Combining value of rows keeping all columns

[Anonymous]

Hi all,

I have some data where I have some of the rows repeating many times but with a different numerical value (quantity), where I would simply like to combine it into one unique row per unique combination and summarizing the values. In other words, I simply want to do exactly what I would do in a PivotTable in Excel, where I can see total monthly sales (Quantity) per factor (Country) (City). 

I need to highlight that

(1) I have several columns that I need to keep, so using the basic functionatlity of group-by does not work for me.

(2) I want to do this in PowerQuery because I want to use these summarized rows for doing a LOOKUPVALUE to another table later. 

(3) I have been looking at several different solutions in the forum but somehow none that seem to be suitable for me (apologize if I overlooked something).

So my data looks something like this (in reality I have 11 columns, whereas 1 is the quantity (Sales)):

Country	City	N/S	W/E	Sales
US	New York	South	East	100
US	New York	South	East	50
US	Boston	North	West	200
Canada	Vancouver	North	West	300
Canada	Vancouver	North	West	40
Canada	Vancouver	South	East	120
Canada	Montreal	South	West	140

How I want it to look in PowerQuery is as following:

Country	City	N/S	W/E	Sales
US	New York	South	East	150
US	Boston	North	West	200
Canada	Vancouver	North	West	340
Canada	Vancouver	South	East	120
Canada	Montreal	South	West	140

So a very basic thing in principle, but I cannot figure out how to get PowerBI to summarize/aggregate the values (Sales) into one row if the other columns are matching. 

Thanks in advance for your time. 

[HotChilli · Accepted solution]

A 'Group By' solves this. Select multiple columns before clicking 'Group By'

Am I missing something?

[mussaenda · Accepted solution]

Hi @Anonymous ,

@HotChilli  is right.

On power query there is a Group By function where you can group this.

Make  sure you go to advanced option so you can add more columns while grouping

Then select the sum on the lower part and select the column you want to sum.

Hope this helps!

[v-xuding-msft · Accepted solution]

Hi @Anonymous ,

They both are right. I just add an image and relative code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCg1W0lHySy1XiMwvygYyg/NLSzKAtGticQmQMjQwUIrVIajMFEmVU35xSX4eSHl+EVhNeCpYjRHUKOfEvMSURKBAWGJecn5pWWoRplpjEtSa4FGK7hsjNLW++XklRamJOUhKoaYagoyNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Country = _t, City = _t, #"N/S" = _t, #"W/E" = _t, Sales = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Country", type text}, {"City", type text}, {"N/S", type text}, {"W/E", type text}, {"Sales", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Country", "City", "N/S", "W/E"}, {{"Sum", each List.Sum([Sales]), type nullable number}})
in
    #"Grouped Rows"

[Anonymous]

Hi all,

I have stumbled upon another problem related to this, I hope somebody has a solution. I did the assumption below based on that all the fields that are the same should be grouped to one row, but I have some rare exceptions where I have one specific column that could be different. It could look like this (after grouping) 

Order number	Date	Purchase number	Quantity
11111	01-01-2020	010101	100
11111	01-01-2020	010101	50
22222	02-02-2020	020202	200
22222	15-02-2020	020202	300
33333	03-03-2020	030303	40
33333	03-02-2020	030303	60

When I do my grouping the following happens; one order (22222) does not get the summarized quantity to one row because one column (date) is different.

Order number	Date	Purchase number	Quantity
11111	01-01-2020	010101	150
22222	02-02-2020	020202	200
22222	15-02-2020	020202	300
33333	03-03-2020	030303	100

I understand that PowerBI is behaving correctly, but in this case I would simply like PowerBI to summarize the two rows and use the latest date value to look like this:

Order number	Date	Purchase number	Quantity
11111	01-01-2020	010101	150
22222	15-02-2020	020202	500
33333	03-03-2020	030303	100

If anybody also have any idea for how I can label this row for later analysis (a separate column saying "Merged date" for example) that would be very beneficial as well, so I later know which rows have been merged even though the date has been different.

I would be very grateful if somebody has any idea for this. Thank you very much in advance. 

[HotChilli · Accepted solution]

Group By Order Number and Purchase Number

and use aggregations on: Sum of Quantity     and Max of Date

[Anonymous]

This works great, thank you! 

[Anonymous]

Thank you all three of you, it works! I didn't see at first that you can group several columns, just aggregate them, so that solved the issue. Happy that it was such a simple solution.

[v-xuding-msft · Accepted solution]

Hi @Anonymous ,

They both are right. I just add an image and relative code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCg1W0lHySy1XiMwvygYyg/NLSzKAtGticQmQMjQwUIrVIajMFEmVU35xSX4eSHl+EVhNeCpYjRHUKOfEvMSURKBAWGJecn5pWWoRplpjEtSa4FGK7hsjNLW++XklRamJOUhKoaYagoyNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Country = _t, City = _t, #"N/S" = _t, #"W/E" = _t, Sales = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Country", type text}, {"City", type text}, {"N/S", type text}, {"W/E", type text}, {"Sales", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Country", "City", "N/S", "W/E"}, {{"Sum", each List.Sum([Sales]), type nullable number}})
in
    #"Grouped Rows"

[mussaenda · Accepted solution]

Hi @Anonymous ,

@HotChilli  is right.

On power query there is a Group By function where you can group this.

Make  sure you go to advanced option so you can add more columns while grouping

Then select the sum on the lower part and select the column you want to sum.

Hope this helps!

[HotChilli · Accepted solution]

A 'Group By' solves this. Select multiple columns before clicking 'Group By'

Am I missing something?