Forum Discussion
Post PatronEfficient way to transform a table
Hi All,
I am looking for an efficient way to transform a table.
The way I am working on it is
1.) pivot column 2.) add conditional columns 3.) Groupby
From:
| ID | User | Cost | Rank |
| 001_1000 | A | 0.55 | 1 |
| 001_1000 | B | 1.27 | 2 |
| 001_1000 | C | 1.3 | 3 |
| 001_2001 | K | 0.85 | 1 |
| 001_2001 | L | 0.88 | 2 |
| 001_2001 | M | 0.93 | 3 |
To:
| ID | User 1 | Supplier 1 Cost | User 2 | Supplier 2 Cost | User 3 | Supplier 3 Cost |
| 001_1000 | A | 0.55 | B | 1.27 | C | 1.3 |
| 001_2001 | K | 0.85 | L | 0.88 | M | 0.93 |
Great thanks.
But my way to handle this work is so inefficient, may I seek for some helps here?
Hi, ngct1112
I tried it, and I got the final result, but since PowerQuery is not my area of expertise, I did not find a very effective method. Ideally, I should create a custom function to complete it.
Below is my code. You can see that this is not efficient, maybe you have better insights.let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjAwjDc0MDBQ0lFyBGIDPVNTIGWoFKuDIucEEtQzMgdSRuhyzmA5YyBpDJcyAhJAAW+wkRaoRkLlfCByFihGQuV8wXKWUDNjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, User = _t, Cost = _t, Rank = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"User", type text}, {"Cost", type number}, {"Rank", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Content", each _, type table [ID=nullable text, User=nullable text, Cost=nullable number, Rank=nullable number]}}), //there should be a function #"Added Custom" = Table.AddColumn(#"Grouped Rows", "User 1", each [Content]{0}[User]), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Supplier 1 Cost", each [Content]{0}[Cost]), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "User 2", each [Content]{1}[User]), #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Supplier 2 Cost", each [Content]{1}[Cost]), #"Added Custom4" = Table.AddColumn(#"Added Custom3", "User 3", each [Content]{2}[User]), #"Added Custom5" = Table.AddColumn(#"Added Custom4", "Supplier 3 Cost", each [Content]{2}[Cost]), // function end #"Removed Columns" = Table.RemoveColumns(#"Added Custom5",{"Content"}) in #"Removed Columns"By the way, there is a PowerQuery forum here. Perhaps this question can be answered quickly in that forum, and there are many experts in this area. If you can find a better answer, please share it for others to learn, thank you.
Best Regards,
Community Support Team _ Zeon Zheng
1 Reply
- v-angzheng-msft
Community Support
Hi, ngct1112
I tried it, and I got the final result, but since PowerQuery is not my area of expertise, I did not find a very effective method. Ideally, I should create a custom function to complete it.
Below is my code. You can see that this is not efficient, maybe you have better insights.let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjAwjDc0MDBQ0lFyBGIDPVNTIGWoFKuDIucEEtQzMgdSRuhyzmA5YyBpDJcyAhJAAW+wkRaoRkLlfCByFihGQuV8wXKWUDNjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, User = _t, Cost = _t, Rank = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"User", type text}, {"Cost", type number}, {"Rank", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Content", each _, type table [ID=nullable text, User=nullable text, Cost=nullable number, Rank=nullable number]}}), //there should be a function #"Added Custom" = Table.AddColumn(#"Grouped Rows", "User 1", each [Content]{0}[User]), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Supplier 1 Cost", each [Content]{0}[Cost]), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "User 2", each [Content]{1}[User]), #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Supplier 2 Cost", each [Content]{1}[Cost]), #"Added Custom4" = Table.AddColumn(#"Added Custom3", "User 3", each [Content]{2}[User]), #"Added Custom5" = Table.AddColumn(#"Added Custom4", "Supplier 3 Cost", each [Content]{2}[Cost]), // function end #"Removed Columns" = Table.RemoveColumns(#"Added Custom5",{"Content"}) in #"Removed Columns"By the way, there is a PowerQuery forum here. Perhaps this question can be answered quickly in that forum, and there are many experts in this area. If you can find a better answer, please share it for others to learn, thank you.
Best Regards,
Community Support Team _ Zeon Zheng