Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hey all,
Relatively new to PBID, and unfamiliar with backend DAX queries. I'm probably overlooking something, as it seems this should be simple.
I have a table structured as below:
Row Owner Oppty UniqueID etc.
1 Person A MSFT 1
2 Person B AAPL 2
3 Person C GOOG 3
4 Person D AMZN 1
etc.
All I want to do is display all rows with duplicate UniqueIDs, e.g. rows 1 and 4, and filter out everything else. As far as I can tell, there's no built-in "Show items when the value: is duplicate" filter (though it'd be nice if there was). All the solved questions I've found have dealt with either filtering out the duplicates, or highlighting duplicates between two different columns.
Should be simple. Thanks for the help.
Solved! Go to Solution.
Hi There,
You can use powerquery for such tasks. To identify all the rows with duplicate unique IDs,
You should perform these steps in order,
1. In PowerQuery Mode, Perform Group by operatio. This is under the Transform Tab.
In Group By, Follow the steps shown in screen shot.
Thanks & Regards,
Bhavesh
BV displays doing it in 4 steps of Query Editor. Another approach is 4 steps of New filtered tables - in the example below each table is named: UniqueID1 thru UniqueID4
UniqueID1 is your starting table
UniqueID2 = GROUPBY(UniqueID1,
UniqueID1[ID],
"DupeCount",COUNTX(CURRENTGROUP(),UniqueID1[ID]))
UniqueID3 = FILTER(UniqueID2,UniqueID2[DupeCount]>1)
at this point you then must create a join line between UniqueID1.ID and UniqueID3.ID fields
UniqueID4 = FILTER(UniqueID1,UniqueID1[ID]=RELATED(UniqueID3[UniqueID1_ID]))
UniqueID4 is your final table with the results you seek
Should be simple!! but is not........
hi,
the subject is old:)
I want to delete duplicate rows but keep one rows each time .t
for example i have the left table and i want the right table. is it possible?
unic id | time | unic id | time | |||
12 | 20 | 12 | 20 | |||
13 | 15 | 13 | 15 | |||
14 | 14 | 14 | 10 | |||
13 | 15 | |||||
Hi There,
You can use powerquery for such tasks. To identify all the rows with duplicate unique IDs,
You should perform these steps in order,
1. In PowerQuery Mode, Perform Group by operatio. This is under the Transform Tab.
In Group By, Follow the steps shown in screen shot.
Thanks & Regards,
Bhavesh
Brilliant! Straight forward, easy solution to walk through and it worked like a champ. Now I've just got to figure out why I'm getting duplicate records from a customer master table that was previously not giving me duplicates. Until I figure it out I'll just remove the duplicates and move forward.
Many thanks!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |