PowerBI Merge queries not matching correctly on mixed format IDs

Question

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.&nbsp;&nbsp;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?&nbsp;&nbsp;Thanks!&nbsp;&nbsp;

ronrsnfld · Accepted Answer

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.
&nbsp;
&nbsp;
List.Transform(Text.ToList([Column1]), each Character.ToNumber(_)) = List.Transform(Text.ToList([Column2]), each Character.ToNumber(_))
&nbsp;
&nbsp;

Forum Discussion

Related content

Embeded PowerBI in PowerPoint not updating correctly

PowerBI Date Fields not Sorting Correctly

Total % not showing correctly

Narrative Measure Not Matching Correctly

POWERBI Subscription