Forum Discussion
Combining value of rows keeping all columns
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.
A 'Group By' solves this. Select multiple columns before clicking 'Group By'
Am I missing something?
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"Group By Order Number and Purchase Number
and use aggregations on: Sum of Quantity and Max of Date
8 Replies
- HotChilli
Community Champion
A 'Group By' solves this. Select multiple columns before clicking 'Group By'
Am I missing something?
Doesn't work.
- mussaenda
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-msftCommunity Support
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"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
Group By Order Number and Purchase Number
and use aggregations on: Sum of Quantity and Max of Date
