Remove sum of balance=0 in power query

[Joris_NL]

Hi,

Is it possible - and if so, how - to remove payments with a sum of balance =0 with a power query?

It's a heavy data set and it needs to be cleaned up BEFORE working with the data for graphs etc. I thought it would be best to filter as much as possible in the power query to reduce calculation time in the dashboard.

So this is a simplification of the data set:

Timm   aug     300

Lenard  aug    300

Lenard  aug   -300

James   aug    300

James   sep    500

James   aug   -300

This creates major problems for me: the number of people paid this year is 3, though in reality 2 (not Lenard), and in august it's only 1 instead of 3. The average cost per person or per month becomes a mess now. Keep in mind this is a simplification, and we are going to need a lot of visuals and calculations depending on age, company, starting date, ending date, region, etc, etc, and all those mixed as well.

Any ideas?

I was thinking about a duplicate table, then pivot to total sum per person/per month, then generate some kind of key column to link the original table with the pivot table, and then remove the sum=0 data.

Is there a better way? And is this even possible?

Thanks in advance, I hope it makes some sense.

[amitchandak · Accepted solution]

@Joris_NL ,

New Measure =

var _sum=  sum(Table[Amount]), Allexcept(Table, Table[Name], Table[Month]) )

return

calculate(Sum(Table[Amount]), filter(Table, _sum<> 0))

[Joris_NL]

Thanks for your help! Finally got time to look at it, but don't get it to work.
There is a closing bracket too many in the first row.

Also, the first comma gives an 'Unexpected expression' error message. Tried semicolons instead of comma's, but the result was the same. Just to be clear, this is my dax:

NewMeasure =

var _sum= sum(FEITBOEKINGDETAIL[mBedrag]), allexcept(FEITBOEKINGDETAIL, FEITBOEKINGDETAIL[xFinspecnr], FEITBOEKINGDETAIL[rBetrekkingopjaar])

return

calculate(Sum(FEITBOEKINGDETAIL[mBedrag], filter(FEITBOEKINGDETAIL, _sum<>0))