cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
AiolosZhao Established Member
Established Member

In Power Query Editor, how to add columns which need sum and group by?

Hi all,

 

I'm new to Power BI, so I really hope that you can tell me the solution step by step.

 

I have data like below :

KeyTypeAmount1Amount2
1A6010
1B3020
2A4020
2B7050
3A10090

 

Acutally I have a lot of "amount", "amount1,2,3,4,...", and in this example, I want to add 2 columns in Power Query Editor, like below:

KeyTypeAmount1Amount2New Amount1New Amount2
1A60109030
1B30209030
2A402011070
2B705011070
3A1009010090

 

Logic is, sum(amount1) group by key and type

 

I'm not familiar with the M Query, so I don't know how to add these 2 columns, please help!

 

Thanks in advanced.

 

Aiolos Zhao

1 ACCEPTED SOLUTION

Accepted Solutions
husnabanu Regular Visitor
Regular Visitor

Re: In Power Query Editor, how to add columns which need sum and group by?

5 REPLIES 5
AiolosZhao Established Member
Established Member

Re: In Power Query Editor, how to add columns which need sum and group by?

Anyone can help to solve this question?

husnabanu Regular Visitor
Regular Visitor

Re: In Power Query Editor, how to add columns which need sum and group by?

Hi 

 

You can use group by function to group with KEY and TYPE

 

image.png

 

 

image.png

AiolosZhao Established Member
Established Member

Re: In Power Query Editor, how to add columns which need sum and group by?

Hi @husnabanu ,

 

thanks for reply, but after I use the group by, I won't have amount1 and amount2.

what I want is keep all column in table2 and add 2 columns.

 

Thanks.

Aiolos 

husnabanu Regular Visitor
Regular Visitor

Re: In Power Query Editor, how to add columns which need sum and group by?

AiolosZhao Established Member
Established Member

Re: In Power Query Editor, how to add columns which need sum and group by?

thanks, that's what I want.

 

but unforunately, my data is too large, and when I use that method, it takes too long to apply the data.

 

do you have any other idea to solve that?

 

Thanks.

Aiolos

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 245 members 2,471 guests
Please welcome our newest community members: