Forum Discussion
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/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 |
- 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:
4 Replies
- 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:
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
