Consolidate Column Data

[Russ At Index]

Good Afternoon,
Wonder if someone could assist in my problem of the moment !
5 Columns :
Type - Aisle - Bay - Level - Order
ABC - 7 - 30 - 10 - 17
ABC - 7 - 30 - 10 - 171
ABC - 14 - 3 - 10 - 2

I would like to add the two order qty's to create one record , whilst keeping the same column layout .
Eg :
ABC - 7 - 30 - 10 - 182
ABC - 14 - 3 - 10 - 2

Any ideas ??

Many thanks , stay safe ,

Russ

 

[alansidman]

With Power Query use the Group By function

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Type", "Aisle", "Bay", "Level"}, {{"Total Order", each List.Sum([Order]), type nullable number}})
in
    #"Grouped Rows"

Book9
	A	B	C	D	E
1	Type	Aisle	Bay	Level	Total Order
2	ABC	7	30	10	188
3	ABC	14	3	10	2
Sheet2

 

[Russ At Index]

Hi Alan ,

Apologises for the delay in replying , i am new to Power Query (BI) ... could you point me in the right
direction of where to place / run your code please ?.

Many thanks , stay safe ,

Russ.
PS , my BI course was this week , cancelled until January .... Covid 19

 

[Norie]

Russ

Select the data, goto Data>Get & Transform Data>From Table/Range.

Once in Power Query select the first 4 columns, right click and select Group By...

In the Group By dialog box enter an appropriate name for the new column, select Sum from the Operation dropdown and Order from the Column dropdown

Click OK, and then File>Close & Load to return the data to Excel.

 

[Russ At Index]

Thank you Norrie , as always appreciate the help.

I will give your kind instructions a try in the morning ,

Stay safe ,

Russ