Power Query Wrong Merging (fuzzymerge with 1 result)

[Imbizile]

Hi all

I have a lot of tables that I merge back and forth in Power Query and in general, I need to merge LeftOuter, but as the table I merge with often has duplicates, I use fuzzy matching, with only 1 result (as I always only need the first occurrence).

But I have just noticed a quite troubling issue with Power Query merging, when I do as above, as it seems to merge completely incorrect sometimes.

I have set up a small test sheet, with 2 tables with numbers (but treated as text) - table2 has a single duplicate (the last 2 records). As seen below, when I merge table2 into table 1, and then check if the merged result corresponds with the source result, a bunch of rows are incorrectly merged:
WrongMerging.png

This is my merging code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Numbers1", type text}}),
    #"Merged Queries" = Table.FuzzyNestedJoin(#"Changed Type", {"Numbers1"}, Numbers2Table, {"Numbers2"}, "Numbers2Merging", JoinKind.LeftOuter, [IgnoreCase=false, IgnoreSpace=false, NumberOfMatches=1]),
    #"Expanded Numbers2" = Table.ExpandTableColumn(#"Merged Queries", "Numbers2Merging", {"Numbers2", "Occurrence"}, {"Numbers2Merged", "Occurrence"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded Numbers2", "WrongMerge", each if [Numbers1] <> [Numbers2Merged] and [Numbers2Merged] <> null then "WrongMerge" else null)
in
    #"Added Conditional Column"

Is this a bug or am I doing something wrong - and what is the correct way of doing this kind of merge in Power Query?

Best regards
Imbizile

[AliGW]

Why don't you simply merge and then remove duplicates? Fuzzy merging is not the right approach as far as I can see, based on your description.

[Shareez Saleem]

Have you tried 'Full outer -All Rows from both' option in Merge > Join Kind

[Imbizile]

I need the table I am merging into to stay intact, meaning keeping the same number of rows and not altering any of the existing data in that table. And that table might contain duplicates as well (I did not address that in the first post, but that is often the case). So I only need to add extra columns to that first table with additional info from the second table, without otherwise changing the first table.

As I see it, both suggestions will change data in the first table.

[AliGW]

OK - please mock up manually the results you want to see in the sample workbook. At the moment, we are stabbing in the dark.

NOTHING is ever changed in an underlying source table.

[Imbizile]

Ok, I now added a duplicate row in numbers1 table to illustrate that the scenario is 2 tables both with duplicates, and what I need is to get additional info (columns) from second table into first table , without otherwise changing data in the first table.

So now the 2 tables look like this:
WrongMerging1.png

I then made a new "Expected result" table, like this:
WrongMerging2.png

Basically, I just made them from these formulas and dragged to bottom:

Numbers2Merged
=IFERROR(VLOOKUP(K3;Table3[#All];1;FALSE);"")

Occurrence
=IFERROR(VLOOKUP(K3;Table3[#All];2;FALSE);"")

WrongMerge
=IF(OR(L3="";K3=L3);"";"WrongMerge")

And that is what I would expect the Power Query to return as well, but it returns what is listed under the "result" table.

[Bo_Ry]

Please try

let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    Merged = Table.NestedJoin(Source, {"Numbers1"}, Numbers2Table, {"Numbers2"}, "Numbers2Table", JoinKind.LeftOuter),
    Expanded = Table.ExpandTableColumn(Merged, "Numbers2Table", {"Numbers2", "Occurrence"}, {"Numbers2", "Occurrence"}),
    RemovedDuplicates = Table.Distinct(Expanded, {"Numbers1"})
in
    RemovedDuplicates

or

let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    Merged = Table.NestedJoin(Source, {"Numbers1"}, Table.Distinct(Numbers2Table, {"Numbers2"}), {"Numbers2"}, "Numbers2Table", JoinKind.LeftOuter),
    Expanded = Table.ExpandTableColumn(Merged, "Numbers2Table", {"Numbers2", "Occurrence"}, {"Numbers2", "Occurrence"})
in
    Expanded

[Imbizile]

Thanks so much Bo_Ry - your first suggestion deletes the last duplicate row in the first table, so that is a no go, but your second suggestion gives the expected result. So I see the trick was adding the Table.Distinct() to the second table when merging.

I will now change all my fuzzymerges to the Table.Distinct() approach instead.

Thanks again and also thanks to Shareez Saleem and AliGW for their contributions

[Imbizile]

A bonus question: in this demo sheet, both tables were sorted by the numbers column, but if the tables have different sorting, it will change the sorting order whenever I merge a table into another table. Is there a way to keep the same sorting order in the table I am merging into?

[AliGW]

Add an index column before merging, and sort on it after merging, then remove it.

[Imbizile]

Ahh right, I will do that!

Thanks

[AliGW]

You're welcome.