Forum Discussion

DavidNash's avatar
DavidNash
Regular Visitor
8 years ago
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 the best way to acheive this in the query editor?

 

Need Help
Tips and Tricks
  • Greg_Deckler's avatar
    Greg_Deckler
    8 years ago

    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"

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago

    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's avatar
      DavidNash
      Regular Visitor
      8 years ago

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