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.
I am sure this is an easy solution for someone here.
I want to convert data in the format below to three columns OTD Code, Period (currently the month period across the top), Date.
What is the best way to acheive this in the query editor?
Solved! Go to Solution.
Select the three Period columns and Unpivot them. See this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8g9xsVTSUTI01Dcy1zcyMDTH4BjBObE6YPWGBhBhQ1MkNSgcI0OohlgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"OTD Code" = _t, #"Oct-17" = _t, #"Nov-17" = _t, #"Dec-17" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"OTD Code", type text}, {"Oct-17", type text}, {"Nov-17", type text}, {"Dec-17", type text}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"OTD Code"}, "Attribute", "Value") in #"Unpivoted Columns"
Select the three Period columns and Unpivot them. See this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8g9xsVTSUTI01Dcy1zcyMDTH4BjBObE6YPWGBhBhQ1MkNSgcI0OohlgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"OTD Code" = _t, #"Oct-17" = _t, #"Nov-17" = _t, #"Dec-17" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"OTD Code", type text}, {"Oct-17", type text}, {"Nov-17", type text}, {"Dec-17", type text}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"OTD Code"}, "Attribute", "Value") in #"Unpivoted Columns"
Thanks a lot! I was trying that by selecting all the columns not just the date ones!
Happy to help! 🙂
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 |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |