Forum Discussion
Creating a Relationship and Merging tables
Hi All
I'm trying to create a unique list of suppliers taken from our distribution partner spend reports and our invoices that we post internally to get a top level overview of all vendors and how much we spend with them via both routes. I think, I need to create a MASTER vendor list. I do this by creating a reference table from the Vendor Name field and appending the two together then remove duplicates. The problem with this is that I get the error message 'You can't create a relationship betweeen these two columns because one of the columns must have unique values'. I thought I just created that!
So I thought, what if I created a key in the same Master Vendor List Table and then merged it to both of the spend reports then put in the relationship after, using the numbers instead...
Now I'm not even able to load the preview for 'Merge Queries'.
Am I on the right track? What do I need to do to get these tables to talk to each other?
I am guessing that you are likely running into the issue that what M and DAX consider unique is different. This is generally related to capitalization issues. So, you might add a text transform to your query where you capitalize the entire string and then remove duplicates. You could then transform it back to proper case.
Another common issue is having trailing spaces or something at the end of some of the entries so you might want to add a "trim" to your query.
3 Replies
I am guessing that you are likely running into the issue that what M and DAX consider unique is different. This is generally related to capitalization issues. So, you might add a text transform to your query where you capitalize the entire string and then remove duplicates. You could then transform it back to proper case.
Another common issue is having trailing spaces or something at the end of some of the entries so you might want to add a "trim" to your query.
I found a work around before your repsonse and don't want to ruin the report now I've got it to do what I need it too. But I've tried it on a copy dashboard on a much smaller amount of data and seems to work when I capitalise it all!...Just wondering, can I change it all back to what it should be which is first letter capital...For example: John Smith Ltd, change to JOHN SMITH LTD...then back again. If I make it all lower case again it would be john smith ltd. Any thoughts?
Yes, you should be able to transform to UPPERCASE, remove duplicates and then transform back to "Capitalize each word". Just right-click the column in the Query Editor and expand Transform section.