Forum Discussion
Solved
Convert a Matrix into 3 columns
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 th...
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! :)
