Excel: combining tables while turning columns into rows-CodePudding

I have these tables (under DATA), each table is in a separate sheet in the same file. I'm trying to figure out a way to merge them a single report similar to the green table (Desired Outcome)

Any advise? My issue is turning Brand A/B/C into unique Rows

• Select some cell in your Data Table,

• Data Tab => Get&Transform => From Table/Range,

• When the PQ Editor opens: Home => Advanced Editor,

• Make note of all the 4 Tables Names,

• Paste the M Code below in place of what you see.

let
    //DataOne
    SourceOne = Excel.CurrentWorkbook(){[Name="DataOne"]}[Content],
    DataTypeOne = Table.TransformColumnTypes(SourceOne,{{"#", type any}, {"Region", type text}, {"Desc.", type text}, {"Brand A", Int64.Type}, {"Brand B", Int64.Type}, {"Brand C", Int64.Type}}),

    //DataTwo
    SourceTwo = Excel.CurrentWorkbook(){[Name="DataTwo"]}[Content],
    DataTypeTwo = Table.TransformColumnTypes(SourceTwo,{{"#", type text}, {"Region", type any}, {"Desc.", type text}, {"Brand A", Int64.Type}, {"Brand B", Int64.Type}, {"Brand C", Int64.Type}}),

    //DataThree
    SourceThree = Excel.CurrentWorkbook(){[Name="DataThree"]}[Content],
    DataTypeThree = Table.TransformColumnTypes(SourceThree,{{"#", type any}, {"Region", type text}, {"Desc.", type text}, {"Brand A", Int64.Type}, {"Brand B", Int64.Type}, {"Brand C", Int64.Type}}),

    //DataFour
    SourceFour = Excel.CurrentWorkbook(){[Name="DataFour"]}[Content],
    DataTypeFour = Table.TransformColumnTypes(SourceFour,{{"#", type any}, {"Region", type text}, {"Desc.", type text}, {"Brand A", Int64.Type}, {"Brand B", Int64.Type}, {"Brand C", Int64.Type}}),

    //AppendingAllFourTables & Unpivoting
    Append = Table.Combine({DataTypeOne, DataTypeTwo, DataTypeThree, DataTypeFour}),
    DataType = Table.TransformColumnTypes(Append,{{"#", Int64.Type}, {"Region", type text}, {"Desc.", type text}, {"Brand A", Int64.Type}, {"Brand B", Int64.Type}, {"Brand C", Int64.Type}}),
    UnpivotData = Table.UnpivotOtherColumns(DataType, {"#", "Region", "Desc."}, "Attribute", "Value")
in
    UnpivotData

• Change the Table name as Desiredtbl before importing it back into Excel.

• When importing, you can either select Existing Sheet with the cell reference you want to place the table or you can simply click on NewSheet