cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RMDNA Senior Member
Senior Member

Displaying rows with a duplicate same-column value

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.

1 ACCEPTED SOLUTION

Accepted Solutions
BhaveshPatel Super Contributor
Super Contributor

Re: Displaying rows with a duplicate same-column value

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.screen1.PNGGroup Byscreen2.PNGFilter out all the records in duplicate records column with Value 1screen3.PNGExpand the Oppty column by clicking on the two sided arrowsscreen4.PNGRemove duplicate records and unique IDs column and rename the headers

 

Thanks & Regards,

Bhavesh

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

View solution in original post

4 REPLIES 4
BhaveshPatel Super Contributor
Super Contributor

Re: Displaying rows with a duplicate same-column value

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.screen1.PNGGroup Byscreen2.PNGFilter out all the records in duplicate records column with Value 1screen3.PNGExpand the Oppty column by clicking on the two sided arrowsscreen4.PNGRemove duplicate records and unique IDs column and rename the headers

 

Thanks & Regards,

Bhavesh

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

View solution in original post

CahabaData New Contributor
New Contributor

Re: Displaying rows with a duplicate same-column value

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........

www.CahabaData.com
MilanAXVII Regular Visitor
Regular Visitor

Re: Displaying rows with a duplicate same-column value

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 idtime unic idtime 
 1220 1220 
 1315 1315 
 1414 1410 
 1315    
       
Highlighted
KenSkinner Frequent Visitor
Frequent Visitor

Re: Displaying rows with a duplicate same-column value

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!

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 351 members 3,186 guests
Please welcome our newest community members: