cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
rpiboy Frequent Visitor
Frequent Visitor

Merge not Finding Matches Against Custom Column

Hi all,

 

I've searched up and down and not found any pertient results for my issue.

 

I've created a custom column that extracts three characters out of a text string. I'm think attempting to do a Merge Query using the Custom Column against a column in another table with the same strings. On the Merge PowerBI says the selection has failed to find any matches and if I do hit Ok I just get null values (which makes sense, since it can't find any matches). However, since I can look at the data I know for a fact I have matches.

 

I should also mention I did recast my custom column as text, as the column in the table I'm attempting to merge in is a Text column.

 

My data source is an MS-SQL DB.

 

Thanks for reading!

-Robert

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: 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.



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




9 REPLIES 9
Super User
Super User

Re: Merge not Finding Matches Against Custom Column

The query editor is case sensitive. Are you sure the text matches in case as well as characters?

rpiboy Frequent Visitor
Frequent Visitor

Re: Merge not Finding Matches Against Custom Column

@KHorseman - the character strings I'm looking to match are all numerals, so unless I'm missing something, it doesn't seem likely that is the issue...

 

customcolumntable.PNGloctable.PNG

Super User
Super User

Re: Merge not Finding Matches Against Custom Column

There might be spaces, blanks or unprintable characters in them. I'd recommend to convert them to "real" number formats and then you should be fine.

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




rpiboy Frequent Visitor
Frequent Visitor

Re: Merge not Finding Matches Against Custom Column

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....

Super User
Super User

Re: Merge not Finding Matches Against Custom Column

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.

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Super User
Super User

Re: 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.



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




rpiboy Frequent Visitor
Frequent Visitor

Re: Merge not Finding Matches Against Custom Column

@kcantor

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?

Super User
Super User

Re: Merge not Finding Matches Against Custom Column

@rpiboy

You are correct. Always duplicate the clean, trim, and transform on any columns you are trying to match. I went from matching 1 out of 8257 to all of them by cleaning up the columns.



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




rpiboy Frequent Visitor
Frequent Visitor

Re: Merge not Finding Matches Against Custom Column

Thanks!

 

Clean/Trim/Lowercase helped. I didn't pick through each to determine which one did the trick on which column.