Forum Discussion
Remove duplicity on same time
Hi, I'm using Power BI Desktop and I need to remove duplicate values but only on same day. For example:
I need to know only that No. 22011 bought something on 28.12.2016 then on 15.12.2016 something and on 6.11.2016 something. Hope you understand.
Thank you for your help.
Hi gmmb1,
You could achieve this by Query Editor like this (assume we have 2 columns Date and Id)
- data with 2 columns
- duplicated Date column
- get date only
- duplicated Id column
- Merge 2 new columns
- remove duplicates by Merged Column
We have 2 columns in datasetduplicated Date columnI will select only Date and remove part of time cause you want check there are some orders in that date10 first charactersduplicated Id columnChoose 2 new duplicated columns (Id copy & date copy) to merge columndefault behaviorthe rest of things is choose Merged column and select Remove Duplicates
Note: If you want to check based on DateTime, you dont need to remove time part (just merge datetime and id, then remove dup on merged column)
If this works for you please accept it as solution and also like to give KUDOS.
Best regards
Tri Nguyen
4 Replies
You can change the format of date to "YYYY : MM : DD" and then select the column and Click one the "remove duplicate".
use this function:
DATE(<year>, <month>, <day>):<year>= Year (yourdate)
<month>= month(yourdate)
<day>= day (yourdate)
Please let me know if you have any question!
Hi gmmb1,
You could achieve this by Query Editor like this (assume we have 2 columns Date and Id)
- data with 2 columns
- duplicated Date column
- get date only
- duplicated Id column
- Merge 2 new columns
- remove duplicates by Merged Column
We have 2 columns in datasetduplicated Date columnI will select only Date and remove part of time cause you want check there are some orders in that date10 first charactersduplicated Id columnChoose 2 new duplicated columns (Id copy & date copy) to merge columndefault behaviorthe rest of things is choose Merged column and select Remove Duplicates
Note: If you want to check based on DateTime, you dont need to remove time part (just merge datetime and id, then remove dup on merged column)
If this works for you please accept it as solution and also like to give KUDOS.
Best regards
Tri Nguyenhi dude ,
cool we can remove the duplicate in multiple ways in power bi. that is not a problem ,
here my question is u have another one column right from that what u want ?
Max , Min , Sum ?
Hi gmmb1,
You can simply use DISTINCT function to filter the duplicate records.(it also works on table)
Reference:
Regards,
Xiaoxin Sheng
