[Power Query] Removing rows that meet specific criteria (aka WHERE)

[grggmrtn]

Hi everyone,

Our data is giving us redundencies that I need to correct for in PowerBI. It's a glitch that I can't get fixed within the data itself.

Basically what happens is that I get two rows that are *almost* identical - something like this:

PersonID	Service		Date	Received
1		A		1.1.2019	1
1		A		1.1.2019	0
1		A		2.1.2019	1
1		A		3.1.2019	1
1		B		1.2.2019	0
1		B		2.2.2019	0

There SHOULD be one line for each date, but every once in awhile I get two lines (like in the first two lines in the above table) - with the only difference being [Received].

I need some sort of filter that will DELETE the row if [PersonID], [Service], [Date] are identical, but where [Received]=0

Can anyone help?

[Ashish_Mathur · Accepted solution]

Hi,

In the Query Editor, select the first  3 columns, right click and select Group By.  Take the maximum of the Received column.

Hope this helps.

[grggmrtn]

Awesome @Ashish_Mathur , thanks so much!

[Ashish_Mathur]

You are welcome.