Transpose/Transform Multiple Rows into Single Row

[Anthony007]

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 you.

Pbix with sample data here: https://drive.google.com/file/d/1z5xWjv13wjYAS1pcAVSkz3W1k4viGdfB/view?usp=sharing

[Stachu · Accepted solution]

you should be able to adjust this code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTJUitWJVkqCs5LhrBQ4KxXIMgKz0uCsdCDLGMzKALJMwKxMIMsUzMqCs7LhrBwIKxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [name = _t, id = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"name", type text}, {"id", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"id"}, {{"names", each Table.Transpose(Table.FromList(List.Combine({[name]}))), type table}}),
    #"Expanded names" = Table.ExpandTableColumn(#"Grouped Rows", "names", {"Column1", "Column2", "Column3", "Column4"}, {"Column1", "Column2", "Column3", "Column4"})
in
    #"Expanded names"

you should:

1) replace the red code with your table definition

2) add coma at the end of your table definition

3) replace blue step name with whatever is the the name of the last step in your table definition

hope that helps 🙂

[Stachu · Accepted solution]

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

[Stachu · Accepted solution]

you should be able to adjust this code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTJUitWJVkqCs5LhrBQ4KxXIMgKz0uCsdCDLGMzKALJMwKxMIMsUzMqCs7LhrBwIKxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [name = _t, id = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"name", type text}, {"id", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"id"}, {{"names", each Table.Transpose(Table.FromList(List.Combine({[name]}))), type table}}),
    #"Expanded names" = Table.ExpandTableColumn(#"Grouped Rows", "names", {"Column1", "Column2", "Column3", "Column4"}, {"Column1", "Column2", "Column3", "Column4"})
in
    #"Expanded names"

you should:

1) replace the red code with your table definition

2) add coma at the end of your table definition

3) replace blue step name with whatever is the the name of the last step in your table definition

hope that helps 🙂

[Anthony007]

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?

[Anthony007]

@Stachu thank your for help. I think I need to learn M code.

I have complicated the task a bit, and this solution does not fit. You can help transform the following table:

to table: 

[Anthony007]

@Stachu

misspelled. I mean, Can you help transform the following table?

[Stachu · Accepted solution]

try this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tcw5DsAgEEPRu0xNCrKcBlGQQDbI/dtYM5Khe/qWHIIkceIlOmjy9N411IPKVIFm1Uld0KK6oVX1QJtJH80vlbqGvVKN+kzxBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [name = _t, id = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"name", type text}, {"id", Int64.Type}}),
    ExcludedItems = Table.SelectRows(#"Changed Type", each Text.EndsWith([name], "-1")),
    ExcludedItemsWithKey = Table.AddColumn(ExcludedItems, "Custom", each Text.Start([name], Text.Length([name]) - 2)),
    WithoutExcludedItems = Table.SelectRows(#"Changed Type", each not Text.EndsWith([name], "-1")),
    #"Merged Queries" = Table.NestedJoin(WithoutExcludedItems,{"name", "id"},ExcludedItemsWithKey,{"Custom", "id"},"WithoutExcludedItems",JoinKind.LeftOuter),
    #"Expanded WithoutExcludedItems" = Table.ExpandTableColumn(#"Merged Queries", "WithoutExcludedItems", {"name"}, {"name with -1"})
in
    #"Expanded WithoutExcludedItems"

[Anthony007]

@Stachu

Thanks for the help and time spent on me. All solutions work great.

tell me where it is better to take the literature for learning M code? I saw the list of formulas.
But I see there is a different methods for use, to examples, using "#" and not using

[Stachu]

you could try this
https://msdn.microsoft.com/en-us/query-bi/m/power-query-m-language-specification

but it's quite technical

these videos are quite good as well
https://www.youtube.com/watch?v=ecfRTEoYadI

https://www.youtube.com/watch?v=L0Y1KL7o3aQ

other than language specification I haven't read any books. just lots of blogs, videos etc.

[Anthony007]

Thank you very much

[Stachu · Accepted solution]

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]

= 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! 

[Stachu]

I wrote it manually
If you create a new query and write 

#shared

in the formula bar it gives list of all the formulas used in M, you can then transform it to table and filter

regarding the columns - if you go to #"Grouped Rows" step the columns are not yet expanded. You can expand there with UI, and it will expand based on your data. Will not be flexible though in case there are more columns with next refresh, so it may be good to add some manually

[Anthony007]

Hi @Stachu! 

It works fine! thank a lot of help!

Tell me, did you write this code manually? I cannot reproduce this step in the editor: 

= Table.Group(#"Changed Type", {"id"}, {{"names", each Table.Transpose(Table.FromList(List.Combine({[name]}))), type table}})

How u do that?