CODEWITHSUNDEEP
excelpowerbipowerquery
Wait
Wait

Reputation: 41

Is there a way use a loop to reiterate a table in power query?

I need a way to reiterate a table N amount of times. I have 1 main table (MainTable) which needs to be processed by each item in a List (ListofItems). It is processed in a function (Funct1) where it takes in 1 item in the ListofItems and transforms the MainTable into a new table (NewTable). But I want to iterate the NewTable through Funct1 again for the next item in the ListofItems.

For Example, the flow would be like this:

total N items in ListofItems,

MainTable ->Funct1(Item1) -> NewTable1

NewTable1 ->Funct1(Item2) -> NewTable2

...

NewTableN ->Funct1(ItemN) -> FinalTable

I have tried using List.Generate but I don't think this can produce a table, so far I could only manage lists.

The furthest I have gotten is with List.Accumulate, it is able to pass each list of items into the function. But I don't know how to make it pass the NewTable back into itself.

let
    MainTable = #"MainTable",
    Source2 = #"TableofList",
    TotalRow = Table.RowCount(Source2),
    ListofItem = Source2[Column1],
    
    output = 
            List.Accumulate(
                {0..TotalRow},
                [Item=0,NewTable=MainTable],
                (result, count) =>
                    (NewTable as table)=>
                    let
                        Item=ListofItem{count},
                        NewTable = Funct1(Item,NewTable)
                    in
                        NewTable             
            )
in
    output

Please help, I feel like I'm so close to getting it right but I'm lost on what else I can do.

Upvotes: 2

Views: 2016

Answers (1)

Wait
Wait

Reputation: 41

I have managed to solve my problem. I was already really close, I should have used NewTable as the result, and the MainTable as the seed. I'll leave it here in case anyone else needs something similar.

let
    MainTable = #"MainTable",
    Source2 = #"TableofList",
    TotalRow = Table.RowCount(Source2),
    ListofItem = Source2[Column1],
    
    output = 
            List.Accumulate(
                {0..(TotalRow-1)},
                MainTable,
                (NewTable, count) =>
                    let
                        Item=ListofItem{count},
                        NewTable = Funct1(Item,NewTable)
                    in
                        NewTable             
            )
in
    output

For further information, Funct1 is actually a function to inner join a column on the main table to a definition table. As I have a few columns to join, I wanted a loop function. I wanted it to be dynamic as the columns to join change from project to project. But the structure is all the same. Each Item in the ListofItem would be the name of the column to join to a separate definition table of the same name.

I'm not sure if my approach is a good way to do it, but it's the way I could think of. I'm open to suggestions if there is a better way to do this.

Upvotes: 2

Related Questions