Merging queries - incorrect value after expand table

[szczepaneq]

Hello,

After merging 2 queries, I see that the value is correct and expected in the table view as below:

But just after expanding tables I see different value (not match and incorrect - 2022-06-20 instead of 2021-11-08) in the same row as below:

How is it possible and what am I doing wrong? Thanks for any reply!

[TomekPieto]

Hello all,

I had the same.

In my case the reason was a step before merging: Sort ascending.

It seams like excel is having a bag whenever you sort the columns. The same is happening with the formulas in a table in a worksheet, if you sort in DESC or ASC order a randm column.

Once I removed the sorting, the merging was working properly.

[Syndicate_Admin]

Same issue here. That make no sense, but what I noticed is that matching by dates may have something with this. I'll transform dates to text and then merge and expand. 

[Syndicate_Admin]

I got it!. Not sure what causing it and I had no luck with it changing dates to text or combining key columns into one and then merge by new column. Finaly as the issue was caused by expanding nested table, I've tried Table.Join function instead of Table.NestedJoin which is used when we merge using Merge tool from the ribbon. Difference is that Table.Join creates table with expanded all columns from both merged tables. It does not create a new column with nested table. Downside is that you need to handle column name duplicates between two tables but that's clearly explained in the function documentation which can be found here.

[v-eqin-msft]

Hi @szczepaneq ,

It's weird.🤔

Could you please share your pbix file after removing sensitive data?

Best Regards,
Eyelyn Qin

[BA_Pete]

Hi @szczepaneq ,

Can you select your query, go to Home tab > Advanced Editor, copy all the code in there and paste it into a code window ( </> icon above) here please?

I need to be able to see the code that has created the join.

Thanks,

Pete

[szczepaneq]

Sure,

let
    Źródło = Table.NestedJoin(#"Calesco PO - ilość szt >0", {"PO & PO line ID"}, #"SO OBR - ilość szt >0", {"PO & PO line ID"}, "SO OBR - ilość szt >0", JoinKind.LeftOuter),
    #"Przefiltrowano wiersze" = Table.SelectRows(Źródło, each ([Purchase order] = "123744")),
    #"Usunięto inne kolumny" = Table.SelectColumns(#"Przefiltrowano wiersze",{"Vendor account", "Purchase order", "PO line ID", "PO & PO line ID", "Delivery date ", "SO OBR - ilość szt >0"})
in
    #"Usunięto inne kolumny"

[BA_Pete]

Hi @szczepaneq ,

Is there a reason why you're doing row filters and column selection before expanding your merge?

I'm not sure whether this would actually cause a problem, but it seems unorthodox to do things this way round.

What happens if you apply this code to a new blank query?

let
    Źródło = Table.NestedJoin(#"Calesco PO - ilość szt >0", {"PO & PO line ID"}, #"SO OBR - ilość szt >0", {"PO & PO line ID"}, "SO OBR - ilość szt >0", JoinKind.LeftOuter),
    expandMerge = Table.ExpandTableColumn(Źródło, "SO OBR - ilość szt >0", {"PO & PO line ID", "Data dostawy"}, {"PO & PO line ID_2", "Data dostawy_2"}),
    #"Przefiltrowano wiersze" = Table.SelectRows(expandMerge, each ([Purchase order] = "123744")),
    #"Usunięto inne kolumny" = Table.SelectColumns(#"Przefiltrowano wiersze",{"Vendor account", "Purchase order", "PO line ID", "PO & PO line ID", "Delivery date ", "SO OBR - ilość szt >0"})
in
    #"Usunięto inne kolumny"

Pete

[szczepaneq]

I did some filters to describe simple example. Without any filters, I have the same result. (correct value before expand, and another one - incorrect after expand).

before expand:

after:

[bsssss]

Hello!

Did you find any solution? I'm having the same error and I'm going crazy 🙂