Forum Discussion
Getting duplicate values in Power BI
Hello Team,
I am connecting to Azure SQL server from Power BI desktop and in SQL statement option I am using below query to get the data.
I am getting duplicate values for PRODUCT_ID in Power BI for some of the products.
SQL Query - SELECT DISTINCT PRODUCT_ID FROM [XXX-XX-XXXX].[PRODUCTS];
Expected Output -
PRODUCT_ID
2024-P-12345
The output which I am getting -
PRODUCT_ID
2024-P-12345
2024-P-12345
Though I am using distinct but it is returning duplicate records. I also tried using TRIM, Remove duplicates options but it is still returning duplicates.
If I apply filter of this particular id at Power query editor, it is giving me correct output but we want to get data for all PRODUCT_IDs.
Hi aditya_ghui
Please Check for Hidden Characters:
Even though you've used TRIM, there might be hidden characters or non-printing characters that are not visible.
Ensure that there are no extra spaces or special characters in your PRODUCT_ID values.
You can use LEN function in SQL to check the length of PRODUCT_ID and see if there are any discrepancies:SELECT distinct PRODUCT_ID, LEN(PRODUCT_ID) AS Length
FROM [XXX-XX-XXXX].[PRODUCTS];
If your requirement is solved, please make THIS ANSWER a SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.
5 Replies
Hi aditya_ghui
Please Check for Hidden Characters:
Even though you've used TRIM, there might be hidden characters or non-printing characters that are not visible.
Ensure that there are no extra spaces or special characters in your PRODUCT_ID values.
You can use LEN function in SQL to check the length of PRODUCT_ID and see if there are any discrepancies:SELECT distinct PRODUCT_ID, LEN(PRODUCT_ID) AS Length
FROM [XXX-XX-XXXX].[PRODUCTS];
If your requirement is solved, please make THIS ANSWER a SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.Hello Ray_Minds,
I executed query and found that it has '\t' as hidden caracter. I removed this caracter using below code and it worked.
REPLACE(PRODUCT_ID, CHAR(9), '')
I am not sure why it is returning multiple values (have you checked the source to make sure there are no hidden characters or anything by checking the length?), however, once it is in Power Query, right click on the PRODUCT_ID field and select remove duplicates. That way, every time you refresh the data Power BI will always run the de-duplication.
Hello audreygerred,
We tried that option but it giving duplicate value only. I even tried by opening new Power BI desktop and fetch the data but same error.
Data source has only one row.
Hi, aditya_ghui
Thanks for the quick reply from [audreygerred]
You can try the following SQL query to ensure that you get unique PRODUCT_IDs by removing any hidden spaces or differences in case:
SELECT DISTINCT
TRIM(UPPER(PRODUCT_ID)) AS PRODUCT_ID
FROM
[XXX-XX-XXXX].[PRODUCTS];You can also create a calculated column in Power BI using DAX to remove duplicates within your report:
DistinctProductIDs = DISTINCT(Products[PRODUCT_ID])
Best Regards,
Muhammad YousafIf this post helps, then please consider "Accept it as the solution" to help the other members find it more quickly.
