Outer Left Join Power Query not returning joined matches

[Jose_Perez]

I am doing an outer left join in power query to bring a value from another table. Join is done fine even when i look at the table inside the cell is the value I want take a look a the image.

 The problem is when its expanded it open a different row with different numbers. see image

has anyone encounter this issue before?

Thank you guys! 🙂

[Syndicate_Admin]

I think the nulls are to blame.

Equals Is Not Always Equivalent: Power Query Joins vs. SQL Joins | Ben Gribaudo

[Syndicate_Admin]

That is indeed very bizarre behavior. I have no explanation for it but maybe Ben Gribaudo has.

I used an alternative code 

    #"Added Custom" = Table.AddColumn(#"Filas filtradas3", "Layout", (k)=> Table.SelectRows(Layout,each [Descripción]=k[Ubicacion])),
    #"Expanded Layout" = Table.ExpandTableColumn(#"Added Custom", "Layout", {"Código Id"}, {"Código Id"})
in
    #"Expanded Layout"

and that worked fine.

[Syndicate_Admin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Jose_Perez]

Hi,

Thaks for your answer. You can find the file here https://docs.google.com/spreadsheets/d/1IbweWkfPkAXU3yt-CJMu4vEA19vZXXME/edit?usp=sharing&ouid=10461... 

Please go to PowerQuery Table 'StockReposicion'. You will find the error in the last 2 steps. First the join and then when is expanded. What its seen inside the joined column that contains a table it looks fine. Then when expanded results are different.

Thanks for your help.

[ronrsnfld]

I hope someone comes up with an explanation.

It doesn't seem to matter what kind of Join you specify; they all seem to work OK with regard to the resultant table, but then provide the incorrect results for both tables when you expand them.

Of interest is that if you do a Table.Join (not nested), the result is what it should be.  So the following modification would work:

...
    #"Consultas combinadas1" = Table.Join(#"Filas filtradas3", {"Ubicacion"}, Layout, {"Descripción"},  
        JoinKind.LeftOuter),
    #"Removed Columns" = Table.RemoveColumns(#"Consultas combinadas1",{"Descripción", "Sector", "Nivel"})
in
    #"Removed Columns"

producing: