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

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(_),","))

  • 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