Forum Discussion

Anthony007's avatar
Anthony007
Icon for Helper I rankHelper I
8 years ago
Solved

Transpose/Transform Multiple Rows into Single Row

It is necessary to convert the table as follows:     at:   The data will be updated automatically, the number of names can change. Help solve the problem with the query editor, thank ...
Need Help
Anthony007's avatar
Anthony007
Icon for Helper I rankHelper I
8 years ago

And Stachu, there is a problem. Data updated. If the id has more than four values, they will not fall into the transposed table. because the last code spreads the table into 4 columns. What if there are 50 such values?

Stachu's avatar
Stachu
Icon for Community Champion rankCommunity Champion
8 years ago

if you replace last row in the sytnax with this

= Table.ExpandTableColumn(#"Grouped Rows", "names", List.Distinct(List.Combine(List.Transform(#"Grouped Rows"[names], each Table.ColumnNames(_)))), List.Distinct(List.Combine(List.Transform(#"Grouped Rows"[names], each Table.ColumnNames(_)))))

then it will always expand all the columns

  • Anthony007's avatar
    Anthony007
    Icon for Helper I rankHelper I
    8 years ago
    = Table.ExpandTableColumn(#"Grouped Rows", "names", List.Distinct(List.Combine(List.Transform(#"Grouped Rows"[names], each Table.ColumnNames(_)))), List.Distinct(List.Combine(List.Transform(#"Grouped Rows"[names], each Table.ColumnNames(_)))))

    this works as well! :smileyhappy: