Issue : Duplicate primary key value detected by error because values are trimmed by Power BI Desktop
[EDIT] - I found the root cause of the issue : it seems that Power BI Desktop is trimming values and will consider (for instance) : 'AAA ' = 'AAA' -- which triggers such discrepant behavior where it considers a well-designed Primary Key field as holding duplicates. Power Query is not making this mistake :
Back End DB (Snowflake) : 2 entries with one having a space in the end
Power Query : 2 distinct rows, normal...
Power BI Desktop, even with this filtering applied :
Deleting the relation already put in place, the data imported from a source of 2 rows end up in a table of 1 row :
-----
[ORIGINAL POST]
Power Bi Desktop detects a duplicate key while refreshing datamodel whereas there is no such duplicate in my dataset - power query itelself does not detect such anomaly (see snapshots attached). The issue did not occur when modeling in Direct Query mode, but appeared when attempting to switch ot either Import or Dual mode.
Refresh : it says "duplicate Key with value " INC."
Full dataset profiling : no duplicates
Column filtering : one single value.
2 Comments
As we all know that DAX does not recoginze space and capitalization . When your values are "AAA" and "AAA ", you always get "AAA" in Desktop view .
As for the "The issue did not occur when modeling in Direct Query mode, but appeared when attempting to switch ot either Import or Dual mode." you mentioned , my understanding is when you import your data from data source , Power Query will perform a transformation on the data , it is normally in Power Query Editor , but back to Desktop view , it will say "duplicate Key with value " INC." .
Best Regards,
Community Support Team _ Ailsa TaoOK - well it's obvious that not everybody knows all about PBI, and specifically this trailing spaces trimming default feature (which could be troublesome in some justified situations), but at least your answer brought me the piece of info that I used to lack. Thanks !
