Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hey everybody !
I have a table like this :
id | theme 1 | theme 2 | theme 3 |
1 | car | ||
2 | car | bike | |
3 | car | ||
4 | car | moto | |
5 | moto | ||
6 | moto | bike | |
7 | moto | ||
8 | moto | ||
9 | bike | moto | bike |
10 | bike |
And I want transform this table in this format :
id | theme 1 |
1 | car |
2 | car |
3 | car |
4 | car |
5 | moto |
6 | moto |
7 | moto |
8 | moto |
9 | bike |
10 | bike |
2 | bike |
6 | bike |
9 | moto |
4 | moto |
9 | bike |
If someone have a simple function usable in unic query, that could help me so much ! I have already duplicate first table in 2 other tables and use append fonction to agregate values but I don't like this solution.
Thanks for advance,
Cyril
Solved! Go to Solution.
Have you tried unpivot transformation? Copy and paste this into your Query Editor for reference:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUpOLAKSIBSrE61kBBdJysxOVYAJG2MoNEEWyc0vyQeLmsI4CIVmCCEUI80xlVpgCllCtaEaApYyNEDIgZXHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [id = _t, #"theme 1" = _t, #"theme 2" = _t, #"theme 3" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"theme 1", type text}, {"theme 2", type text}, {"theme 3", type text}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"id"}, "Attribute", "Value"), #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}), #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Value] <> "")), #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Value", "Theme"}}) in #"Renamed Columns"
Have you tried unpivot transformation? Copy and paste this into your Query Editor for reference:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUpOLAKSIBSrE61kBBdJysxOVYAJG2MoNEEWyc0vyQeLmsI4CIVmCCEUI80xlVpgCllCtaEaApYyNEDIgZXHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [id = _t, #"theme 1" = _t, #"theme 2" = _t, #"theme 3" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"theme 1", type text}, {"theme 2", type text}, {"theme 3", type text}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"id"}, "Attribute", "Value"), #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}), #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Value] <> "")), #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Value", "Theme"}}) in #"Renamed Columns"
Indeed that works
Thank you very much Nick !
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |