PowerBI Merge queries not matching correctly on mixed format IDs

[ckampen]

I am trying to merge two tables in PowerBI, the IDs have two formats: 7 numbers or 2 numbers-2 letters-2 numbers, I have attached a preview below. When merging the queries (left outer) it matches the number format well but it does not seem to match the combined format. I have done a manual check using Excel, and there I am able to match the combined formats. 

For both tables I have cleaned, trimmed, and uppercased the columns, both columns are set to text. Does anyone have an idea why it might still not match properly? 

Thanks!

[Anonymous]

Hi @ckampen ,
According to your description, the merge operation in power bi is not able to match appropriately, if there is a null value it may be due to the data being followed by spaces. You can check if there are spaces present. If you still can't solve the problem, you can provide sample data, so that we can help you faster.

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[ronrsnfld · Accepted solution]

In Power Query on the data samples that do not match, do a character by character comparison looking at the ASCII codes to see if they match. Or post something here that can be used to reproduce your problem.

List.Transform(Text.ToList([Column1]), each Character.ToNumber(_)) = List.Transform(Text.ToList([Column2]), each Character.ToNumber(_))