Hi,

You desired display not very clear (what is 27,004( 9000+9001+9003) ?)

Attached is the power query output,  please take a look,  if it is not what you want,  please attached a sample output of final table

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Material", Int64.Type}, {"Plant", Int64.Type}, {"Qty", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Material", "Plant"}, {{"Total Qty", each List.Sum([Qty]), type number}}),
#"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Material", Order.Ascending}, {"Plant", Order.Ascending}})
in
#"Sorted Rows"

Hi,

The total for Plant where Material is 1001 should be 36004, and where Material is 1002 it should be 27000?

You haven't said where you get this data from so I've had to enter it as a table in Excel.  You can enter this into the Advanced Editor:

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Material", Int64.Type}, {"Plant", Int64.Type}, {"Qty", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Material"}, {{"Plant", each List.Sum([Plant]), type number}, {"Qty", each List.Sum([Qty]), type number}})
in
#"Grouped Rows"

Regards

Phil

Hi,

may be this is what you wanted,   for Plant 1001 9000, two records you only take unique number,  i.e. 9000,  like wise for 1002 9000,  only 9000 was taken.  the M Code as below

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Material", Int64.Type}, {"Plant", Int64.Type}, {"Qty", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Number.ToText([Material])&"-"&Number.ToText([Plant])),
#"Grouped Rows" = Table.Group(#"Added Custom", {"Material", "Custom"}, {{"Count", each List.Sum([Qty]), type number}}),
#"Extracted Text After Delimiter" = Table.TransformColumns(#"Grouped Rows", {{"Custom", each Text.AfterDelimiter(_, "-"), type text}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Extracted Text After Delimiter",{{"Custom", type number}}),
#"Grouped Rows1" = Table.Group(#"Changed Type1", {"Material"}, {{"Count", each _, type table [Material=number, Custom=number, Count=number]}}),
#"Aggregated Count" = Table.AggregateTableColumn(#"Grouped Rows1", "Count", {{"Custom", List.Sum, "Sum of Custom"}, {"Count", List.Sum, "Sum of Count"}}),
#"Renamed Columns" = Table.RenameColumns(#"Aggregated Count",{{"Sum of Custom", "Plant_Qty"}, {"Sum of Count", "Qty"}})
in
#"Renamed Columns"