- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 you.
Pbix with sample data here: https://drive.google.com/file/d/1z5xWjv13wjYAS1pcAVSkz3W1k4viGdfB/view?usp=sharing
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 🙂
Thank you for the kudos 🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Thank you for the kudos 🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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"
Thank you for the kudos 🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 🙂
Thank you for the kudos 🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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"
Thank you for the kudos 🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
Thank you for the kudos 🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you very much
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Thank you for the kudos 🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
= 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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Thank you for the kudos 🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
Subject | Author | Posted | |
---|---|---|---|
09-18-2024 06:04 PM | |||
08-16-2024 12:16 AM | |||
03-05-2024 11:22 AM | |||
Anonymous
| 03-21-2019 02:21 AM | ||
Anonymous
| 02-09-2023 12:06 PM |
User | Count |
---|---|
113 | |
80 | |
55 | |
54 | |
44 |
User | Count |
---|---|
168 | |
114 | |
74 | |
61 | |
53 |