Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
dvdbroek
Frequent Visitor

Recursion function with an incrementer doenst work properly

Dear M-query kings and queens,

 

I made an recursion function as followed:

 

 

 

(in_boekjaar as text) as table=>

let

in_Table = #table({},{}),
in_loop = 0,

    Companies = GetCompanies("10", "0"),  // in dit blok wordt het totaal aan administraties opgehaald om het maximaal aantal loopts te berekenen, zie ook de variable TotalLoops
    companies = Companies[companies],
    #"Converted to Table" = Table.FromList(companies, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    ListOfCompanies = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"companyId", "accountingSystem", "totalDocumentsToAuthorize", "name", "email", "_links", "externalId", "language", "extraData", "hasAccess"}, {"Column1.companyId", "Column1.accountingSystem", "Column1.totalDocumentsToAuthorize", "Column1.name", "Column1.email", "Column1._links", "Column1.externalId", "Column1.language", "Column1.extraData", "Column1.hasAccess"}),

    TotalLoops = Table.RowCount(ListOfCompanies),
    in_iD = ListOfCompanies[Column1.companyId]{0},

   RecFunction = (Id as text, boekjaar as text, loop as number, optional paraTable) as table=>

                 let

                        Source          =                    GetTransactions(Id, boekjaar),
                        Extract1        =                    Source[transactions],
                        Extract2        =                    Table.FromList(Extract1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
                        CurrentLoop     =                    loop +1,

                        IdOfCompany     =                    if CurrentLoop -1 = 0 
                                                             then ListOfCompanies[Column1.companyId]{0}
                                                             else ListOfCompanies[Column1.companyId]{CurrentLoop},

                        MergedTable     =                   if paraTable = null 
                                                            then Table.Combine({#table({},{}),Extract2})
                                                            else Table.Combine({paraTable,Extract2}),

                        Result          =                   if CurrentLoop >= TotalLoops
                                                            then MergedTable
                                                            else @RecFunction(IdOfCompany,in_boekjaar, CurrentLoop, MergedTable)

                    in

                        
                         Result,
                        output = RecFunction(in_iD, in_boekjaar, in_loop, in_Table) 
                       
                

    in

    output

 

 

 

 

It works as followed: 

 

1. In the first part i call another function that gives me the total amount of companies that i want to retrieve. I put this number in the "TotalLoops" variable.

2. Then the recursion function loops through the companies wich i got earlier by incrementing the "CurrentLoop" value by 1 every company it loops trough and merges the tables of each company to one table.

 

But my incrementor doenst work properly. If i put my max companies list to for example 2 (so i only get 2 iterations) the result gives me the table of only the first company. I checked if there was data on the second company and there was so im doing something wrong.

 

Anyone can help me out?

 

 

 

1 ACCEPTED SOLUTION
Vera_33
Resident Rockstar
Resident Rockstar

Hi @dvdbroek 

 

Your counter is working, but this one, you never get the list{1}, you go {0} then {2}

 

IdOfCompany     =      ListOfCompanies[Column1.companyId]{loop},

 

 

View solution in original post

1 REPLY 1
Vera_33
Resident Rockstar
Resident Rockstar

Hi @dvdbroek 

 

Your counter is working, but this one, you never get the list{1}, you go {0} then {2}

 

IdOfCompany     =      ListOfCompanies[Column1.companyId]{loop},

 

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.