Forum Discussion
Solved
Remove Duplicates Except 0
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/20...
- 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 
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:
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: