REMOVE rows that have the same text in column one and balance to zero in column 2

[Anonymous]

I have a report Im attempting to create for several of our internal accounts.

I only need the information that does not have a balancing entry also entered.

One of these accounts is an invoiced not received account, and the other is a work in progress.

 The first 2 lines need to bo deleted in this example.

The first column is the voucher text, if it matches AND the next column has a positive and negative matching amount both lines need to be gone.

We only want to see the ones with out an outbound entry.

I am extramly new to Power Bi, I do not know dax or m (neither does my co-worker) So we are both stuck.

Any help would be greatly appreciated (especially if it takes into account this is the first report I am writing and assumes im like a toddler 😅).

[jgeddes · Accepted solution]

Here is one way to do this.

In Power Query (M) we will group by Voucher Text, sum the Amount for each group and then filter out groups that have a zero sum.

As an example table...

Select the 'Voucher Text' column and select 'Group By' from the Home ribbon.

Select Advanced grouping and create the columns as below...

Clicking 'OK' you will get a table like...

Filter the 'Group Sum' column to exclude zero values.

Now expand the table in the 'All Rows' column.

Deselect the 'Voucher Text' column as you already have it and deselect the 'Use original column name...' as you do not need it.

You should now have a table without offsetting entries.

You can delete the 'Group Sum' column as it is no longer needed.

Hope this points you in the right direction.

[Anonymous]

I have come back to this time and time again. This is well explained and easy to follow. My most recent questions have been hard to get step by step answers, this one just makes me smile.

I have used this on at least 7 different reports, as many of the reports in our system are incorrect, and none come in anything other than PDF.

[Anonymous]

Thank you so much!