Unique values in xls see in Power BI with duplicate

[ewakol]

Hello, my source is xls file where have 78545 unique values in column PQ_OC.Item (i checked it few times, even using remove duplicates in xls, there is is only unque value). But when i load this data to Power BI, goes to table and mark column i see information:

i cant create retation in model because have information that realtion will be many to many, vlookup also doesnt work.

Someone have similar problem than Power BI see unique values as values with duplitates, and know how to solve it?

[AnalyticsWizard · Accepted solution]

@ewakol 

This issue might be due to the case sensitivity of Power Query in Power BI. Power Query is case sensitive, meaning it treats “abc” and “ABC” as two different values, while Powe...1.

Here are a few steps you can take to resolve this:

1. Check for Case Sensitivity: Ensure that the values in your column do not have variations in case. For example, “abc” and “ABC” would be considered as two different values in Power Query but the same...1.

2. Trim and Clean: Use the TRIM and CLEAN functions in Power Query to remove leading/trailing spaces an...1.

3. Create a Custom Column: If you still face issues, consider creating a custom column that combines the problematic column with another unique column. This can help create a truly unique column2.

Remember, these are just a few potential solutions. The exact solution might vary depending on the specifics of your data and the cause of the issue.

[ewakol]

You are great! All of You!! Thank You ❤️
@AnalyticsWizard , @MattAllington  case sensitive was the reason! in xls file i have that walue with small and capital letter, Power Query didnt see it as duplicate, Power BI indeed. Used in xls file formula =UPPER() and it solve problem, but then i have another trouble in other data source also with duplicates but the problem was different and, i found it thanks to your solution @mahenkj2 . So you solve my next problem before i reconize it! 😉 

[MattAllington · Accepted solution]

Great. Instead of using UPPER in Excel, you're better off using transform, uppercase in Power Query. The general rule is to leave the source untouched if possible. 

[AnalyticsWizard · Accepted solution]

@ewakol 

This issue might be due to the case sensitivity of Power Query in Power BI. Power Query is case sensitive, meaning it treats “abc” and “ABC” as two different values, while Powe...1.

Here are a few steps you can take to resolve this:

1. Check for Case Sensitivity: Ensure that the values in your column do not have variations in case. For example, “abc” and “ABC” would be considered as two different values in Power Query but the same...1.

2. Trim and Clean: Use the TRIM and CLEAN functions in Power Query to remove leading/trailing spaces an...1.

3. Create a Custom Column: If you still face issues, consider creating a custom column that combines the problematic column with another unique column. This can help create a truly unique column2.

Remember, these are just a few potential solutions. The exact solution might vary depending on the specifics of your data and the cause of the issue.

[MattAllington]

It is likely one of 2 issues

1. Power Query is case sensitive, Power BI is not. First set the capitalisation to be the same before removing duplicates

2. If that's not it, use the technique I covered in this article. https://exceleratorbi.com.au/remove-duplicates-keep-last-record-power-query/

[mahenkj2]

Hi @ewakol ,

In power query, selete the column and in Home tab, use "keep duplicates", just to confirm what are those duplicates. Accordingly you apply ways to handle those duplicates, such as make them capital first, or some other issues as might be the case.

Hope it helps.

[mahenkj2]