Forum Discussion

Anonymous

Not applicable

4 years ago

Solved

Combine rows (based on ID) into single row with multiple columns

Dear all,

How can I combine multiple rows into single row with multiple columns as below?

Regards,

Urmas

Anonymous
4 years ago
You can first Group By Id, making sure you select "All Rows" as the Aggregation. Name the column "NewRows". Then you can do this:

= Table.TransformColumns(#"Grouped By", {{"NewRows", each Table.FromRows(List.Combine(Table.ToRows(_)))}})

Then delete the other columns and expand the tables.

--Nate

3 Replies

Anonymous
Not applicable
4 years ago
You can first Group By Id, making sure you select "All Rows" as the Aggregation. Name the column "NewRows". Then you can do this:

= Table.TransformColumns(#"Grouped By", {{"NewRows", each Table.FromRows(List.Combine(Table.ToRows(_)))}})

Then delete the other columns and expand the tables.

--Nate
- Anonymous
  Not applicable
  4 years ago
  IT works. perfectly. I only had to add curly brackets in one place in the end:
  = Table.TransformColumns(#"Grouped Rows", {{"NewRows", each Table.FromRows({List.Combine(Table.ToRows(_))})}})
  
  Thank you very much,
  
  Urmas
- PowerBIPuffGirl
  Regular Visitor
  4 years ago
  In the solution, where do you type this:
  = Table.TransformColumns(#"Grouped By", {{"NewRows", each Table.FromRows(List.Combine(Table.ToRows(_)))}})
  I'm very new to Power BI, so appreciate the help!