Normalize nested matrix data

[chaz2jerry]

Hi, I have following data in an unusual nested matrix shape.  Can anyone share methods to flatten this data to a normal tabular shape?

As-is:

Product	CY2020-Q1		CY2020-Q2		CY2020-Q3		CY2020-Q4		CY2021-Q1
Bikes	Sales	1000	Sales	1000	Sales	1000	Sales	1000	Sales	1000
Bikes	Book	3000	Book	3000	Book	3000	Book	3000	Book	3000
Bikes	Units	2000	Units	2000	Units	2000	Units	2000	Units	2000
Cars	Sales	1000	Sales	1000	Sales	1000	Sales	1000	Sales	1000
Cars	Book	3000	Book	3000	Book	3000	Book	3000	Book	3000
Cars	Units	2000	Units	2000	Units	2000	Units	2000	Units	2000
Boats	Sales	1000	Sales	1000	Sales	1000	Sales	1000	Sales	1000
Boats	Book	3000	Book	3000	Book	3000	Book	3000	Book	3000
Boats	Units	2000	Units	2000	Units	2000	Units	2000	Units	2000

To-be:

Product	Time period	Sales	Book	Units
Bikes	CY2020-Q1	1000	3000	2000
Bikes	CY2020-Q2	1000	3000	2000
Bikes	CY2020-Q3	1000	3000	2000
Bikes	CY2020-Q4	1000	3000	2000
Bikes	CY2021-Q1	1000	3000	2000
Cars	CY2020-Q1	1000	3000	2000
Cars	CY2020-Q2	1000	3000	2000
Cars	CY2020-Q3	1000	3000	2000
Cars	CY2020-Q4	1000	3000	2000
Cars	CY2021-Q1	1000	3000	2000
Boats	CY2020-Q1	1000	3000	2000
Boats	CY2020-Q2	1000	3000	2000
Boats	CY2020-Q3	1000	3000	2000
Boats	CY2020-Q4	1000	3000	2000
Boats	CY2021-Q1	1000	3000	2000

[chaz2jerry]

Thank you both @mahoneypat  @wdx223_Daniel  for your input.  I am able to use Pat's solution now.  While Daniel your solution can probably work too, it's much easier for me to follow Pat's workflow and troubleshoot/maintain.  By the way Pat, I had to change the step #"Lowercased Text" to make it work, and removed the earlier Type Setting steps to avoid static column names.  Pls see my new code below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKTylNLlHSUXKONDIwMtANNASyFZD4Rmh8YzS+CTLfEKY/VidaySkzO7UYyAtOzAHThgYGBhRxkQ11ys/PBlLGEGXk8ZDNC83LLAHRRhBl5HNBhjonFlHd41AzqeVvqHFU9bZTfmIJ9SMcaijVIhxqHvV8HgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Added Index" = Table.AddIndexColumn(#"Promoted Headers", "Index", 0, 1, Int64.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index", "Product"}, "Attribute", "Value"),
    #"Lowercased Text" = Table.TransformColumns(#"Unpivoted Other Columns",{{"Attribute", each if Text.Start(_,1) = " " then null else _, type text}}),
    #"Filled Down" = Table.FillDown(#"Lowercased Text",{"Attribute"}),
    #"Added Index1" = Table.AddIndexColumn(#"Filled Down", "Index2", 1, 1, Int64.Type),
    #"Divided Column" = Table.TransformColumns(#"Added Index1", {{"Index2", each Number.Mod(_,2), type number}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Divided Column", {{"Index2", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Divided Column", {{"Index2", type text}}, "en-US")[Index2]), "Index2", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"}),
    #"Pivoted Column1" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[#"1"]), "1", "0"),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column1",{{"Attribute", "Time Period"}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"Sales", Int64.Type}, {"Book", Int64.Type}, {"Units", Int64.Type}})
in
    #"Changed Type2"

[mahoneypat · Accepted solution]

Here's another way to do it in the query editor.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKTylNLlHSUXKONDIwMtANNASyFZD4Rmh8YzS+CTLfEKY/VidaySkzO7UYyAtOzAHThgYGBhRxkQ11ys/PBlLGEGXk8ZDNC83LLAHRRhBl5HNBhjonFlHd41AzqeVvqHFU9bZTfmIJ9SMcaijVIhxqHvV8HgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", Int64.Type}, {"Column4", type text}, {"Column5", Int64.Type}, {"Column6", type text}, {"Column7", Int64.Type}, {"Column8", type text}, {"Column9", Int64.Type}, {"Column10", type text}, {"Column11", Int64.Type}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Product", type text}, {"CY2020-Q1", type text}, {"Column3", Int64.Type}, {"CY2020-Q2", type text}, {"Column5", Int64.Type}, {"CY2020-Q3", type text}, {"Column7", Int64.Type}, {"CY2020-Q4", type text}, {"Column9", Int64.Type}, {"CY2021-Q1", type text}, {"Column11", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index", 0, 1, Int64.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index", "Product"}, "Attribute", "Value"),
    #"Lowercased Text" = Table.TransformColumns(#"Unpivoted Other Columns",{{"Attribute", each if Text.Start(_,6) = "Column" then null else _, type text}}),
    #"Filled Down" = Table.FillDown(#"Lowercased Text",{"Attribute"}),
    #"Added Index1" = Table.AddIndexColumn(#"Filled Down", "Index2", 1, 1, Int64.Type),
    #"Divided Column" = Table.TransformColumns(#"Added Index1", {{"Index2", each Number.Mod(_,2), type number}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Divided Column", {{"Index2", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Divided Column", {{"Index2", type text}}, "en-US")[Index2]), "Index2", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"}),
    #"Pivoted Column1" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[#"1"]), "1", "0"),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column1",{{"Attribute", "Time Period"}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"Sales", Int64.Type}, {"Book", Int64.Type}, {"Units", Int64.Type}})
in
    #"Changed Type2"

Regards,

Pat

[wdx223_Daniel]

@chaz2jerry when we generate Power Query code, Excel will change data area to Super Table automatically. So, when you add new data, just keep those data into this super table, and ensure each time adding 2, 4, 6 ....columns.

then refresh the query.

[wdx223_Daniel]

@chaz2jerry 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Custom1 = let a=Table.ToColumns(Table.DemoteHeaders(Source)),
                  b=Table.FromPartitions(
                                         "Time Period",
                                         List.Transform(
                                                        List.Split(List.Skip(a),2),
                                                        each let aa=List.Zip({a{0}}&_)
                                                             in {aa{0}{1},Table.FromRows(List.Skip(aa),{"Product","x","y"})}
                                                       )
                                        )
              in Table.Pivot(b,List.Distinct(b[x]),"x","y")
in
    Custom1

[chaz2jerry]

Thanks Daniel, checking the code, seems this is a static/hard-coded approach to create the new table, in terms of the "time periods".  Unfortunately the incoming data will change over time (adding new time periods), is it possible to modify the code to handle this data change?