cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jjmauras Advocate I
Advocate I

Remove or Hide Duplicate Values

Is there a way to Remove or Ignore duplicate values when they occur in a column?

 

I'm pulling data from our SQL Server. I have 2 related tables. Every once in a while a duplicate value will occur in my One to Many relationship which causes failure. I'm looking for a way to remove/ignore a duplicate value so I can avoid this. Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

Re: Remove or Hide Duplicate Values

There is a Remove Duplicates step when editing your query, it is in the Reduce Rows section of the ribbon, the M code is:

#"Removed Duplicates" = Table.Distinct(#"[Previous Step]", {"[Column Name]"})


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

12 REPLIES 12
Fabiola_K Advocate IV
Advocate IV

Re: Remove or Hide Duplicate Values

I'm not an expert, but i've done in these cases is to create a third table with the
distinct values, and use it as a bridge between both tables.

Super User III
Super User III

Re: Remove or Hide Duplicate Values

@jjmauras You can handle this in SQL with a CTE or subquery using the row_number() function. This will handle everything automatically.

 


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG
Super User IV
Super User IV

Re: Remove or Hide Duplicate Values

There is a Remove Duplicates step when editing your query, it is in the Reduce Rows section of the ribbon, the M code is:

#"Removed Duplicates" = Table.Distinct(#"[Previous Step]", {"[Column Name]"})


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

jjmauras Advocate I
Advocate I

Re: Remove or Hide Duplicate Values

Thanks everyone. @Greg_Deckler I think I'm going to go with this solution for the moment as it worked right away. My question is will it stick? For example is it a one time thing or will it remove duplicates every time the SQL data is refreshed?

PowerBIGuy Responsive Resident
Responsive Resident

Re: Remove or Hide Duplicate Values

@jjmauras Best practice would be to handle this at the source. Per @Seth_C_Bauer solution.

Business Intelligence Architect / Consultant
Super User IV
Super User IV

Re: Remove or Hide Duplicate Values

Yes, query steps stick. When you run a query, it is really just executing a series of steps. You can see the steps recorded on the righthand side. of the query editing window.

 

Handling this in the source is great, but not always possible. As an end-user focused tool, it is not reasonable to expect that users will have access to the backend to make changes, write stored procedures, expert knowledge to write SQL, etc.


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

zcholla Advocate I
Advocate I

Re: Remove or Hide Duplicate Values

I am having the same issue where I am pulling information from a SQL table but can not force the query to remove duplicates based on newest or most recent. I am attempting to remove duplicates out of an account number column, and wanting to keep only the most recent record for each account number. 

 

I connect to the SQL table. 

Edit Queires,

Sort the Table by Entry Date

Then remove duplicates from Account Number

 

When i check the output, I am still seeing records that are not the most recent for specific account numbers. 

 

I was told it is because itr was not sorted at the source. I do not have access to the source to change the sort order. 

 

What can I add in the language to force the sort in the imported data?

 

let
Source = Sql.Databases("123esco.cloudapp.net"),
123_Esco = Source{[Name="123_Esco"]}[Data],
dbo_Meters = CLOUDDATA{[Schema="dbo",Item="Meters"]}[Data],
#"Sorted Rows" = Table.Sort(dbo_Meters,{{"EntryDate", Order.Descending}}),
#"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"UtilityAccountNumber"})
in
#"Removed Duplicates"

 

Super User III
Super User III

Re: Remove or Hide Duplicate Values

@zcholla If you have access to query the table, you could write a query to only retrieve the latest records. Here are a number of solutions that can be used as a guide for you if you aren't very familiar with SQL. You can write the SQL Query in the Advanced Options when you are connecting to the database.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG
bourquejeff
Frequent Visitor

Re: Remove or Hide Duplicate Values

@zcholla, I ran into this issue in Power Query as well. I followed the problem in this article by Ken Puls, and it fixed the problem on the first shot.

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors