Duplicate value error but no duplicate values

[LMACAT]

I am not able to apply changes to a table in Power Query and am getting an error message that there is a duplicate value in a column in the table that is an ID number, but there are no duplicate values in tha column. How do I resolve this?

[Idrissshatila]

Hello @LMACAT , 

Power query shows the top 1000 rows only, maybe the duplicate is not visible.

and if the error is saying there's duplicate then there should be some sort of duplicate in a certain place.

or maybe the relationship between two tables is 1 to 1 and there's a many in one of them.

[LMACAT]

Hello @Idrisshatila. Neither is the case. I checked all the records in the table offline and it is not a duplicate, and the relationship to the other table is one-to-many.

[Greg_Deckler]

@LMACAT What do the ID's look like? Remember, A1 and a1 are the same as far as the semantic model are concerned.

[LMACAT]

Hi @Greg_Deckler they are nine digit numbers. I did Remove Rows Duplicates on the ID column and the total number of rows did not change. Yet when I try to create a one-to-many relationship with another table with IDs, the model says it is a many-to-many relationship. I am stumped.

[Anonymous · Accepted solution]

Hi  @LMACAT , 

they are nine digit numbers 

Check the data type of the ID column to confirm if it is 'Text'. If the same ID has a different number of spaces at the end, Power Query will recognize them as different values.

Try use 'Text.Trim' function

[Greg_Deckler]

@LMACAT Do you have any blanks? If you have evan a single blank value in each table, then the relationship will get forced to many-to-many.