Records not correctly matching on inner merge

[colin501commons]

I'm using Power Query for Salesforce. I was finding that an inner merge on Salesforce record Id was inexplicably excluding certain rows. So, as a test, I tried the following:

• Do a Left Anti merge of Query 1 with Query 2, matching on Salesforce record id, not fuzzy.

Now there should be no rows in Query 1 with a Salesforce Id that matches the Salesforce Id of a row in Query 2, right?

• Next, do an Inner merge of Query 1 with Query 2 matching on Salesforce Id, and almost all rows match.
• If I reverse it, doing an Inner merge followed by a Left Anti Merge, it works as expected. Almost all rows match on the first merge, as expected, and then all are excluded on by the Left Anti merge.

Am I missing something? Is my file corrupt?

[Jai-Rathinavel · Accepted solution]

Hi @colin501commons  if your are checking Count of Records at the Power Query level it might not show the total number of records. Can you load the data and check the IDs in the reporting view after doing a merge ?

[colin501commons]

Thanks for your quick response, @Jai-Rathinavel. As noted in my reply above, I eventually figured out the problem with a little more digging. Thanks for your time!

[v-venuppu]

Hi @colin501commons ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @Jai-Rathinavel for the prompt response.

The behavior you're seeing is very likely due to invisible inconsistencies in your Salesforce Id fields - most commonly leading/trailing spaces, case mismatches, or data type mismatches.

To resolve this, I recommend applying the following transformations to the Salesforce Id column in both queries before the merge:

Table.TransformColumns(Source, {{"Salesforce Id", each Text.Upper(Text.Trim(_)), type text}})

This will:

• Convert all IDs to uppercase (since Power Query is case-sensitive),
• Remove any leading/trailing spaces, and
• Explicitly cast them as text.

Once that’s done, rerun your Left Anti and Inner joins. The results should now be consistent and logical.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you.

[colin501commons · Accepted solution]

Thanks for your quick response. It didn’t solve the problem, but as I was preparing a response, I discovered what was happening. My Query 1 was a combination of appended tables. I didn’t notice that in two of the contributing queries, I labeled the Id match field differently, so it was blank for those queries. I should have noticed the extra column following the appending. Once I dealt with that, the problem went away. Thanks for your time!

[Jai-Rathinavel · Accepted solution]

Hi @colin501commons  if your are checking Count of Records at the Power Query level it might not show the total number of records. Can you load the data and check the IDs in the reporting view after doing a merge ?