Power Query Looping Calculation

[CHARLEA1]

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

[wdx223_Daniel]

= Table.AddColumn(#"Reordered Columns", "Supervisors", each Text.Combine(Record.ToList(_),","))

[CHARLEA1]

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?

[wdx223_Daniel · Accepted 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