Transform a matrix into a table from Power Query Editor

Hi, heba_q ;

You could use unpivot it. here is the full M statement.

 

let
    Source = Excel.Workbook(File.Contents("C:\Users\Administrator\Downloads\Matrix to table.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Removed Columns" = Table.RemoveColumns(#"Promoted Headers",{"Cases_12", "TONS_24"}),
    #"Promoted Headers1" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers1",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type any}, {"Column10", type any}, {"Act", type any}, {"Act_1", type any}, {"Act_2", type any}, {"Act_3", type any}, {"Act_4", type any}, {"Act_5", type any}, {"FCST", type any}, {"FCST_6", type any}, {"FCST_7", type any}, {"FCST_8", type any}, {"FCST_9", type any}, {"FCST_10", type any}, {"Column23", type any}, {"Column24", type any}, {"Column25", type any}, {"Column26", type any}, {"Column27", type any}, {"Column28", type any}, {"Column29", type any}, {"Column30", type any}, {"Column31", type any}, {"Column32", type any}, {"Column33", type any}, {"Column34", type any}}),
    #"Promoted Headers2" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Promoted Headers2", {"Category", "Type", "New Codes", "Description", "Brand", "Pricepoint", "Flavor", "NOTES", "Rat", "Weight", "1/1/2022_1", "2/2/2022_2", "3/6/2022_3", "4/7/2022_4", "5/9/2022_5", "6/10/2022_6", "7/12/2022_7", "8/13/2022_8", "9/14/2022_9", "10/16/2022_10", "11/17/2022_11", "12/19/2022_12"}, "Attribute", "Value"),
    #"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Unpivoted Columns", {"Category", "Type", "New Codes", "Description", "Brand", "Pricepoint", "Flavor", "NOTES", "Rat", "Weight", "Attribute", "Value"}, "Attribute.1", "Value.1"),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Unpivoted Columns1", {{"Attribute.1", each Text.BeforeDelimiter(_, "_"), type text}}),
    #"Added Conditional Column" = Table.AddColumn(#"Extracted Text Before Delimiter", "Custom", each if [Attribute.1] = [Attribute] then 1 else null),
    #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each ([Custom] = 1)),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Attribute.1", "Custom"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Attribute", "Month"}, {"Value", "Cases"}, {"Value.1", "TONS"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Month", type date}})
in
    #"Changed Type1"

 

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    Custom1 = let a=Table.ToRows(Source)
              in #table(
                        List.FirstN(a{2},10)&{"Month","Cases","TONS"},
                        List.TransformMany(
                                           List.Skip(a,3),
                                           each List.RemoveLastN(List.Zip({List.Range(a{2},10,13)}&List.Split(List.Skip(_,10),13))),
                                           (x,y)=>List.FirstN(x,10)&y
                                          )
                       )
in
    Custom1

source is imported from raw data without headers