Unable to match data in a 1-to-1 relationship within a Table visual.

[LFalcon]

I am currently working on a dashboard where I have one data column being imported from a SharePoint Excel file (Contract Master) while the second data column is imported from an Excel file (OEDR) within my file browser.

I have created a 1-to-1 relationship within the Data Model:

However, when I tried to verify that the values were being properly matched, I came across this problem.

Two of the primary key values have matched, but 4 of them have not despite existing within both tables. When importing the data from both sources, I made sure to set each column type to Text using the Advanced Query Editor. As the OEDR data is from an Excel file, I have tried to clean the data column within Excel using the TRIM, CLEAN, and TEXT functions, but this issue keeps re-occurring. I have also tried to use the TRIM and CLEAN functions within Power BI's Power Query, but to no luck. If anyone has any ideas on what I could do to ensure that the data from both sources is being read as cleanly as possible so I can match within Power BI, that would be greatly appreciated.

[Seanan]

Hi @LFalcon 

Would you be able to send over the PBIX file without any sensitive data?

[LFalcon · Accepted solution]

Thank you for the offer, but I was able to figure out the problem. It turns out there was hidden metadata within the Excel files which caused them to not match with the SharePoint data. By converting the Excel files into CSV files, then extracting the column values using Power Query, I was able to finally match the data within Power BI.