Forum Discussion

CHARLEA1's avatar
CHARLEA1
Frequent Visitor
4 years ago
Solved

Power Query Looping Calculation

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.  ...
Need Help
  • wdx223_Daniel's avatar
    wdx223_Daniel
    4 years ago

    = 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

CHARLEA1's avatar
CHARLEA1
Frequent Visitor
4 years ago

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's avatar
wdx223_Daniel
Icon for Community Champion rankCommunity Champion
4 years ago

= 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