Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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)):

 

CountryCityN/SW/ESales
USNew YorkSouthEast100
USNew YorkSouthEast50
USBostonNorthWest200
CanadaVancouverNorthWest300
CanadaVancouverNorthWest40
CanadaVancouverSouthEast120
CanadaMontrealSouthWest140

 

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

 

CountryCityN/SW/ESales
USNew YorkSouthEast150
USBostonNorthWest200
CanadaVancouverNorthWest340
CanadaVancouverSouthEast120
CanadaMontrealSouthWest140

 

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. 

4 ACCEPTED SOLUTIONS
HotChilli
Super User
Super User

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

 

Am I missing something?

View solution in original post

mussaenda
Super User
Super User

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!

View solution in original post

v-xuding-msft
Community Support
Community 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"

v-xuding-msft_0-1598851414074.png

 

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Group By Order Number and Purchase Number

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

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

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  DatePurchase number     Quantity 
11111  01-01-2020        010101100
11111  01-01-202001010150
22222  02-02-2020020202200
2222215-02-2020020202300
3333303-03-202003030340
3333303-02-202003030360

 

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     DatePurchase number      Quantity
1111101-01-2020          010101150
2222202-02-2020020202200
2222215-02-2020020202300
3333303-03-2020030303100


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       DatePurchase number       Quantity
1111101-01-2020       010101150
2222215-02-2020020202500
3333303-03-2020030303100

 

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. 

Group By Order Number and Purchase Number

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

Anonymous
Not applicable

This works great, thank you! 

Anonymous
Not applicable

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
Community Support
Community 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"

v-xuding-msft_0-1598851414074.png

 

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
mussaenda
Super User
Super User

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
Super User
Super User

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

 

Am I missing something?

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors