Log In
Register
Home
New Member
June 18, 2020
Offline
Hi,
I have the following set of data.
Material Plant Qty
1001 9000 542
1001 9000 200
1002 9000 599
1001 9001 202
1002 9000 212
1002 9000 300
1001 9003 454
Below is the end result i hope to achieve: Distinct Material, showing sum distinct of plant.
I tried Group By + Power Pivot to achieve it. But was hoping if I can do everything in Power Query.
Material Plant Qty
1001 27,004( 9000+9001+9003) 542+202+454
1002 9000 599+212+300
Any help is greatly appreciated. Thanks in advance!
August 21, 2019
Offline
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"
October 5, 2010
Offline
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
August 21, 2019
Offline
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"
1 Guest(s)
