Table.Group taking too much time

[grggmrtn]

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

I HAVE tried Table.Group but the actual data has 9 columns to group and not 3 like the dummy data, and a couple million rows, so it's taking hours. Is there another way to do this??

[v-jayw-msft · Accepted solution]

Hi @grggmrtn ,

Check this formula.

Measure = 
var countrow = CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[PersonID],'Table'[Service],'Table'[Date]))
return
IF(countrow<2,1,IF(SELECTEDVALUE('Table'[Received])=1,1,0))

Add it to visual filter should work.

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[amitchandak]

@grggmrtn , You can try like. In data transformation remove received column and post that try delete duplicate rows

https://www.youtube.com/watch?v=QfFCPLOEyRU

[grggmrtn]

Thanks for the reply @amitchandak 

But if I duplicate the query, remove [Received], delete duplicates and merge it back, how am I sure that it will keep the MAX value of [Received] in the original query?