cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kalimotxo Frequent Visitor
Frequent Visitor

SELECT DISTINCT Returning Duplicate Strings

Hello All,

 

I have a query that I am running which is SELECT DISTINCT. It is a simple query with only one string field in the results. There are multiple WHERE conditions, and then a ORDER BY.

 

Once I get the result, I add a step for Rows: Remove Duplicates.

 

The table is connected to others through relationships. When I try to apply changes, I get an error stating "Column 'Location' in Table 'Location' contains a duplicate value' [Name Redacted]' and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table".

 

I manually checked in the preview list, and there are indeed what appear to be duplicate values. If I manually exclude it, a different location then also shows up in the error message, so there are multiple violations.

 

I have no idea how the above steps can result in duplicates. I specifically picked DISTINCT and added the step to remove duplicates. Any help appreciated!

 

Query Text:

SELECT DISTINCT LTRIM(RTRIM(OfficeName)) AS [Location]

 

FROM ADR

 

WHERE
   Market NOT LIKE '%Test%' AND Market NOT LIKE '%Inactive%'
   AND Name NOT LIKE '%MS%'
   AND PDN = 'P'
   AND CONVERT(date, Date) BETWEEN
      DATEFROMPARTS(YEAR(GETDATE())-2, 1, 1) AND
      DATEADD(dd, -1, DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1))
   AND ID IS NOT NULL
   AND WCID IS NULL
   AND (Type = 'S' OR Type = 'C' OR Type = 'E')
   AND Code NOT LIKE '%INACT%'
   AND OfficeName IS NOT NULL

 

ORDER BY LTRIM(RTRIM(OfficeName))

1 ACCEPTED SOLUTION

Accepted Solutions
Rfranca Member
Member

Re: SELECT DISTINCT Returning Duplicate Strings

hi, @kalimotxo

 

Have you checked that the records in the LOCALIZATION column contain values with white spaces or special characters?

This is often the case, so there are duplications.
Use POWER EDITOR QUERY functions to perform this cleaning.

 

Please try and test using it. And please mark the right reply as answer if your issue has been resolved, otherwise, please feel free to ask if you have any other issue.

Best Regards,
Rfranca

View solution in original post

2 REPLIES 2
Rfranca Member
Member

Re: SELECT DISTINCT Returning Duplicate Strings

hi, @kalimotxo

 

Have you checked that the records in the LOCALIZATION column contain values with white spaces or special characters?

This is often the case, so there are duplications.
Use POWER EDITOR QUERY functions to perform this cleaning.

 

Please try and test using it. And please mark the right reply as answer if your issue has been resolved, otherwise, please feel free to ask if you have any other issue.

Best Regards,
Rfranca

View solution in original post

kalimotxo Frequent Visitor
Frequent Visitor

Re: SELECT DISTINCT Returning Duplicate Strings

Selecting text as the format, despite it already being the default, seems to have worked. I don't know why, but it did!

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (2,185)