Iterate over a table using M

kirukim 21 Reputation points
2021-03-31T11:36:30.103+00:00

83248-1.png

I need your help!!
I've this table and i wanna iterate over it to have each value in the first column with all the values in the second column

For example i should get a table with the following values :
A B
A C
A D
A X
A Y
A Z
A F
A E
B X
B Y
.
.
.
..
and it should stop when we'll no more having values from the second column in the first one

Not Monitored
Not Monitored
Tag not monitored by Microsoft.
23,911 questions
No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Jim Davis 1 Reputation point
    2021-04-01T09:16:42.217+00:00

    I'm pretty sure there is a way to do this with an inline function but this works for me and I think is pretty clear and simple:

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Appelant", type text}, {"Appele", type text}}),
    SecondCol = Table.FromList(List.Distinct(#"Changed Type"[Appele]), null, {"Appele"}),
    FirstCol = Table.FromList(List.Distinct(#"Changed Type"[Appelant]), null, {"Appelant"}),
    Joined = Table.AddColumn(FirstCol, "Appele", each SecondCol),
    #"Expanded Appele" = Table.ExpandTableColumn(Joined, "Appele", {"Appele"}, {"Appele"})
    in
    #"Expanded Appele"

    Hope this helps!