Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
Hi @LFalcon
Would you be able to send over the PBIX file without any sensitive data?
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
105 | |
78 | |
67 | |
63 |
User | Count |
---|---|
144 | |
107 | |
105 | |
82 | |
69 |