Forum Discussion
Merge not Finding Matches Against Custom Column
If you convert to real numbers you will lose the leading zeros. I would keep it as text. Use trim, then clean, then transform to lowercase (even for the numbers) then try it again. I actually just ran into this issue and by cleaning the column first, it worked.
I'll give it a go using PwrBI to convert the columns to numbers. However the data in the Location table was manually entered by me fairly short number of rows and the other data, since its an extraction from a string, I don't see how it could have any misc. characters in it....
Maybe the data in your DB has. With field in "text"-format everything is possible.
Otherwise you can clean and trim both columns instead of converting them to numbers.
If you convert to real numbers you will lose the leading zeros. I would keep it as text. Use trim, then clean, then transform to lowercase (even for the numbers) then try it again. I actually just ran into this issue and by cleaning the column first, it worked.
Thanks!
Clean/Trim/Lowercase helped. I didn't pick through each to determine which one did the trick on which column.
re: leading zeros - exactly why I did it as text, however for the purposes of merging and diagrams it would be a bit inconsequential.
re: the transform and cleaning. I would have to perform that function on the custom column and the column being imported with the Location table, correct?