Unexpected comparison results

[PCarson00]

I have a power query table which is a merge of two tables. It performs fine but I want to compare two columns with two other columns.

Spec and SPEC (one is PUDPipeData.SPEC)

I have added a conditional column with this code:

if Text.StartsWith([PID SPEC], [PUDPipeData.Spec]) then "Match" else "Not Matching"

What happens now is the rows double with one "Matching" and one "Not Matching"

After applying conditional column:

Now slicing by "Not Matching"

As you can see after slicing, the not matching is being applied to everything where it should only work on the S52 one that needs to be S53.

I have tried changing the type to text before applying this column, so they are all text columns.

I have also double checked the lower and upper case of the columns and they appear to be correct. Here they are below:

Here are my steps: 

[Anonymous · Accepted solution]

Hi @PCarson00 ,

Based on the information you have provided, It seems that the result appears to be a Cartesian product. There are several possibilities that could cause this:
First, check that the relationship between the two tables is not a many-to-many relationship before you merge them. The Cartesian product result often occurs in the case of many-to-many relationships.
If it's not a many-to-many relationship, check what mode you're using for merge.

For each different mode, the final number of rows returned will vary.
Merge queries overview - Power Query | Microsoft Learn

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

[ronrsnfld]

You are leaving something out with regard to your process. That formula only returns a single result. Perhaps if you posted your actual code (from the advanced editor) the problem may be clarified.