Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi there,
I was hoping someone could help me with looping in powerbi.
Logic is as follows -
I have columns with supervisors as shown -
The custom column collates all the supervisors.
Currently it is done with simple if statements -
= Table.AddColumn(#"Reordered Columns", "Supervisors", each if([Supervisor 3]<>null) then [Supervisor 1] & " , " & [Supervisor 2] & " , " & [Supervisor 3]
else if([Supervisor 2]<>null) then [Supervisor 1] & " , " & [Supervisor 2]
else [Supervisor 1])
The thing is, this table is based off a column supervisor no. that was pivoted. If the database adds in an extra supervisor - i.e. Supervisor 4, when pivoted there will be an extra column and will throw an error with this calculation.
I would like a loop to dynamically calculate this concatenation so that I do not need to go in and manually change the code if there exceeds the amount of existing supervisors.
Thinking something along the times where there is another custom column specifying number of supervisors then looping through and appending.
Any ideas would be largely appreciated.
Many Thanks,
Ashling
Solved! Go to Solution.
= Table.AddColumn(#"Reordered Columns", "Supervisors", each Text.Combine(Record.ToList(Record.SelectFields(_,List.Select(Record.FieldNames(_),each Text.StartsWith(_,"Supervisor")))),", "))
this will combine all the columns that starts with Supervisor
= Table.AddColumn(#"Reordered Columns", "Supervisors", each Text.Combine(Record.ToList(_),","))
Thanks - it is more efficient to be -
= Table.AddColumn(#"Reordered Columns", "Supervisors", each Text.Combine({[Supervisor 1],[Supervisor 2],[Supervisor 3]},", "))
although not sure how to pass in those columns dynamically using Record.ToList?
= Table.AddColumn(#"Reordered Columns", "Supervisors", each Text.Combine(Record.ToList(Record.SelectFields(_,List.Select(Record.FieldNames(_),each Text.StartsWith(_,"Supervisor")))),", "))
this will combine all the columns that starts with Supervisor