Convert a Matrix into 3 columns

[DavidNash]

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?

[Greg_Deckler · Accepted 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"

[DavidNash]

Thanks a lot! I was trying that by selecting all the columns not just the date ones!

[Greg_Deckler]

Happy to help! 🙂