Custom rows extraction

[saksham_kumar]

Hi my source table looks like:

Name	Amount
ABC 1	4
ABC 2	5
ABC 3	9
XYZ	3
MNO	1
PQR	4

In my expected outcome, I want to show data such that the 3 ABCs that is ABC1, ABC 2 and ABC 3 are summed up to show table like:

Name	Amount
ABC	18
XYZ	3
MNO	1
PQR	4

Can I do thi using Search() function, filter etc. ?

Please suggest a way.

Thanks!

[v-yuta-msft · Accepted solution]

@saksham_kumar ,

You can do this in power query, click query editor-> Transform, then click split like below:

 

Then remove the useless column and click group by as below:

Finally you will achieve the result:

 

Community Support Team _ Jimmy Tao

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

[saksham_kumar]

Hey this helps. 

Thanks!