Remove Duplicates Except 0

[gpl]

Thanks in advance to anyone who helps!

I am trying to remove duplicates except 0's and cant seem to figure it out.. help please!

6/2/2019	20870
6/2/2019	20870
6/2/2019	0
6/2/2019	0
6/2/2019	0
6/2/2019	20947
6/2/2019	0
6/2/2019	0
6/2/2019	20947
6/2/2019	20949
6/2/2019	20949
6/2/2019	0
6/2/2019	0
6/2/2019	20955
6/2/2019	20955
6/2/2019	20956
6/2/2019	20956
6/2/2019	20958
6/2/2019	20958

[TomMartens · Accepted solution]

Hey,

i did not test this but maybe this would work, the idea create two tables, filter for all rows that are zero and the 2nd table <> 0, make the 2nd table distinct like so (DAX pseudocode 🙂 😞

NEWTABLE =
UNION(
CALCULATETABLE(<tablename>, value = 0)
,DISTINCT(CALCULATETABLE(<tablename>, value <> 0))
)

Hopefully this provides some ideas.

Regards,
Tom

[jdbuchanan71]

Hello @gpl 

We can do what you are looking for like this:

Add an index column to the data
Modify the index so it wont match any values on accident
Add a column that checks if the value is 0, if so bring the index, if not, bring the date and value. (this assumes if you have the same value on different dates you want to see each date/value pair.
Remove duplicates on the last column we just added.
Remove all the columns we added.

Example excel file with the PowerQuery, will also work in PowerBI. 

https://www.dropbox.com/s/oqcdopy9sca5z0g/KeepZeroDupes.xlsx?dl=0

[Anonymous · Accepted solution]

In Power Query:

• Groupby Date and Value. Aggregating for all Rows and Count

Then add an index column to each sub-table. 

Table.AddIndexColumn([AllData],"Index",1,1)

Remove the other columns, only need the subtable with the index column and the count

Expand the table out

Add a new column with the folowing:

then filter out that column for "keep" and remove other columns and set data types

Final Table:

File:

https://1drv.ms/u/s!Amqd8ArUSwDS3AtSNswgChcZdaUF?e=qWpb0l

[TomMartens · Accepted solution]

Hey,

i did not test this but maybe this would work, the idea create two tables, filter for all rows that are zero and the 2nd table <> 0, make the 2nd table distinct like so (DAX pseudocode 🙂 😞

NEWTABLE =
UNION(
CALCULATETABLE(<tablename>, value = 0)
,DISTINCT(CALCULATETABLE(<tablename>, value <> 0))
)

Hopefully this provides some ideas.

Regards,
Tom

[DouweMeer]

Hello @gpl 

Perhaps create an initial validation step before the distinct? I believe they recently introduced the operator == to filter out the blanks. 

You could also think of a union of the the filtered table with just but 0's and the distinct values.