Very odd merge behavior in power Query

[mike_asplin]

I have a look up table with a row for "Sales Enquiry" as below which is called Enquiry Status Reason Funnel

In my main table I am using this merge and expansion

 #"Merged Queries6" = Table.NestedJoin(#"Filtered Rows3", {"Enquiry Status Reason (CRM)"}, #"Enquiry Status Reason Funnel", {"Enquiry Status Reason (CRM)"}, "Enquiry Status Reason Funnel", JoinKind.LeftOuter),
    #"Expanded Enquiry Status Reason Funnel" = Table.ExpandTableColumn(#"Merged Queries6", "Enquiry Status Reason Funnel", {"Enquiry Type", "Funnel Bucket", "Funnel Bucket Description", "Enquiry Status Reason Group"}, {"Enquiry Type", "Funnel Bucket", "Funnel Bucket Description", "Enquiry Status Reason Group"}),

This seems to work fine for all the other rows in the look up table, but this particular one generates this

 so it has icked up one column correctly, but al lthe other columns are blank???  i did original have this looking up form 2 tables, but have completely deleted the 2nd table. Its like it is remembering the old table and just not reading the code correctly.  i've rebuilt it severla times.

Any clues?

[v-echaithra]

Hi @mike_asplin ,

Thank you for confirming that issue has been resolved. If further assistance is still required. We are available to support you and are committed to helping you reach a resolution.

Best Regards,
Chaithra E.

[Natarajan_M]

Hi @mike_asplin , 

1. If one column is expanding correctly while other columns are not populating as expected, it could be due to duplicates in the Enquiry Status Reason Funnel table. Check for any rows with the same key "Sales Enquiry" that have null values in other columns. In Power Query's preview mode, it will restrict the view to the first 1,000 rows, so make sure to check the source data.

   

2. Additionally, verify if there are any trailing spaces in the key values in both tables. Ideally, the key should be "Sales Enquiry," but it’s worth checking for variations like "Sales Enquiry ".

3. You mentioned that Power BI is referencing the old table. Please review your Power Query, as it might be related to earlier references you made. If you could share the complete Power Query, we would be able to diagnose the issue more effectively.

Thanks .

If this response was helpful in any way, I’d gladly accept a kudo.
Please mark it as the correct solution. It helps other community members find their way faster

[mike_asplin · Accepted solution]

I did check for duplicates and the extra spaces. If it had no columns that would make sense.  As i said doing a complete refresh seemed ot clear its head and it was fine. Thanks for response.  

[Natarajan_M]

@mike_asplin  , whats the fix to this issue ?

[mike_asplin]

close the pbix, reboot reopen and fixed it

[rohit1991]

Hii @mike_asplin 

Since one column expands correctly and others return null, the merge is partially matching but not fully resolving the lookup row.

Fix:

1. In both tables, set join column explicitly to Text.

2. Add a clean column for merge:

Text.Upper(Text.Trim([Enquiry Status Reason (CRM)]))

3. Use this cleaned column in the merge on both sides.

4. Refresh preview (sometimes Power Query cache holds old structure).

[mike_asplin]

I did a refresh and fixed itself before I tried your fix but much appreciated