Transform a matrix into a table from Power Query Editor

[heba_q]

Hello everyone,

Below are tables 1 and 2. Table 1 is the original format that I currently have in my data source Excel file, while table 2 is how I want the table to look like in the Power Query editor. So basically I want to remove row #2 (which includes Act and FCST) and Total FY columns from table 1. Then, I want to transpose the Cases, Tons, and months' names and values columns, while ungrouping the other columns (Category, Type, ..., Weight). Can you please help me? This is a OneDrive link for the excel file (table 1 is in sheet1 and table 2 is in sheet2) --> https://1drv.ms/x/s!AvoXXWI9anTxhC4M_yqKFY-XC8Ty?e=RaRiqV

Table 1:

Cases

Cases

Cases

Cases

Cases

Cases

Cases

Cases

Cases

Cases

Cases

Cases

Cases

TONS

TONS

TONS

TONS

TONS

TONS

TONS

TONS

TONS

TONS

TONS

TONS

TONS

Act

Act

Act

Act

Act

Act

FCST

FCST

FCST

FCST

FCST

FCST

Category

Type

New Codes

Description

Brand

Pricepoint

Flavor

NOTES

Rat

Weight

Jan-22

Feb-22

Mar-22

Apr-22

May-22

Jun-22

Jul-22

Aug-22

Sep-22

Oct-22

Nov-22

Dec-22

Total FY

Jan-22

Feb-22

Mar-22

Apr-22

May-22

Jun-22

Jul-22

Aug-22

Sep-22

Oct-22

Nov-22

Dec-22

Total FY

A

B

ABC123

ABCDEFG

O

E 5

ABCD

3

3.1

80

47

94

47

41

13

72

42

51

46

80

13

78.84814

37.1798

33.40666

11.73148

15.47697

10.68127

30.67287

27.69149

4.110705

29.00463

55.82344

3.355371

Table 2 (the desired result):

Category

Type

New Codes

Description

Brand

Pricepoint

Flavor

NOTES

Rat

Weight

Month

Cases

TONS

A

1

ABC123

ABCDEFG

O

E 5

ABCD

3

3.1

Jan-22

80

78.84814

A

1

ABC123

ABCDEFG

O

E 5

ABCD

3

3.1

Feb-22

47

37.1798

A

1

ABC123

ABCDEFG

O

E 5

ABCD

3

3.1

Mar-22

94

33.40666

A

1

ABC123

ABCDEFG

O

E 5

ABCD

3

3.1

Apr-22

47

11.73148

A

1

ABC123

ABCDEFG

O

E 5

ABCD

3

3.1

May-22

41

15.47697

A

1

ABC123

ABCDEFG

O

E 5

ABCD

3

3.1

Jun-22

13

10.68127

A

1

ABC123

ABCDEFG

O

E 5

ABCD

3

3.1

Jul-22

72

30.67287

A

1

ABC123

ABCDEFG

O

E 5

ABCD

3

3.1

Aug-22

42

27.69149

A

1

ABC123

ABCDEFG

O

E 5

ABCD

3

3.1

Sep-22

51

4.110705

A

1

ABC123

ABCDEFG

O

E 5

ABCD

3

3.1

Oct-22

46

29.00463

A

1

ABC123

ABCDEFG

O

E 5

ABCD

3

3.1

Nov-22

80

55.82344

A

1

ABC123

ABCDEFG

O

E 5

ABCD

3

3.1

Dec-22

13

3.355371

Thank you!

[v-yalanwu-msft · Accepted solution]

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.

[wdx223_Daniel · Accepted solution]

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

[v-yalanwu-msft · Accepted solution]

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.