Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anthony007
Helper I
Helper I

Transpose/Transform Multiple Rows into Single Row

It is necessary to convert the table as follows:

Screenshot_1.png 

 

at:

Screenshot_3.png

 

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

3 ACCEPTED SOLUTIONS
Stachu
Community Champion
Community Champion

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 🙂



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

View solution in original post

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



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

View solution in original post

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"


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

View solution in original post

12 REPLIES 12
Stachu
Community Champion
Community Champion

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 🙂



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

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 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:

Screenshot_1.pngto table: Screenshot_2.png

@Stachu

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

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"


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

@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

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.



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Thank you very much

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



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

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

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



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

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?

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.