Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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?
Solved! Go to Solution.
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},
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},