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

[AiolosZhao]

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 :

Key	Type	Amount1	Amount2
1	A	60	10
1	B	30	20
2	A	40	20
2	B	70	50
3	A	100	90

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:

Key	Type	Amount1	Amount2	New Amount1	New Amount2
1	A	60	10	90	30
1	B	30	20	90	30
2	A	40	20	110	70
2	B	70	50	110	70
3	A	100	90	100	90

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

[Anonymous]

Hi 

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

[AiolosZhao]

Hi @Anonymous ,

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 

[Anonymous · Accepted solution]

Hi,

I hope this helps you https://www.myonlinetraininghub.com/excel-forum/power-query/power-query-conditional-sum-column

[AiolosZhao]

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

[AiolosZhao]

Anyone can help to solve this question?