Pivot Data of the clone of pivot table|Power Pivot|Excel Forum|My Online Training Hub

Mynda's blog post    https://www.myonlinetraininghu.....cenarios    gives a great explanation on how to do this.  I suggest you follow her Scenario #3.

I mocked up a sample of what I think is your excel data based on your Capture2.PNG.   

This is the query I created.  You will need to substitute your file location in place of mine

let
Source = Excel.Workbook(File.Contents("C:\YOUR FILE LOCATION"), null, true),
#"Sheet1 (2)_Sheet" = Source{[Item="Sheet1 (2)",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(#"Sheet1 (2)_Sheet",{{"Column1", type text}, {"Column2", type text}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}}),
#"Transposed Table" = Table.Transpose(#"Changed Type"),
#"Filled Down" = Table.FillDown(#"Transposed Table",{"Column1"}),
#"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Column2"}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Removed Columns", {{"Column1", type text}}, "en-US"),{"Column1", "Column3"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged"),
#"Transposed Table1" = Table.Transpose(#"Merged Columns"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"ITEM;", type text}, {"Categories;", type text}, {"1/31/2021;Qty", Int64.Type}, {"1/31/2021;Amt", type number}, {"2/28/2021;Qty", Int64.Type}, {"2/28/2021;Amt", type number}, {"3/31/2021;Qty", Int64.Type}, {"3/31/2021;Amt", type number}, {"4/30/2021;Qty", Int64.Type}, {"4/30/2021;Amt", type number}, {"5/31/2021;Qty", Int64.Type}, {"5/31/2021;Amt", type number}, {"6/30/2021;Qty", Int64.Type}, {"6/30/2021;Amt", type number}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"ITEM;", "Categories;"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type date}, {"Attribute.2", type text}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type2", List.Distinct(#"Changed Type2"[Attribute.2]), "Attribute.2", "Value", List.Sum),
#"Changed Type3" = Table.TransformColumnTypes(#"Pivoted Column",{{"Amt", Currency.Type}, {"Qty", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type3",{{"Categories;", "Categories"}, {"ITEM;", "ITEM"}})
in
#"Renamed Columns"