Merged queries with deleted duplicates and filtered rows

[EtienneT]

Hi everyone, 

I'm having issues with merging two queries together. 

My Table 1 looks like that: 

ID	Data_1	Data_2	Date_Field
ABC	100	150	07/23/2024
ABC	110	160	07/24/2024
ABC	130	N/A	07/26/2024

After a bunch of PowerQuery steps, I keep only the latest value of the data to make it look like that : 

ID	Data_1	Data_2
ABC	130	160

I use some filters, some conditional formulas and also a duplicate removal steps. So at the very last step of my query, I have only one version of each ID in the table, which the latest version of data, which is exactly what I want. 

Now comes my second table, that has the same ID on its records, where I need to perform the merge to get the Data_1 and Data_2 fields. 

For that I use the "Merge Query" Left Outer function and select both IDs in both table to perform the merge. I have exact matching and I would expect the Table 2 to look like that : 

Other Data	ID	Table1.Data_1	Table1.Data_2
XXXXX	ABC	130	160

Turns out it does not at all and it keeps giving me wrong data from the Table1, like that : 

Other Data	ID	Table1.Data_1	Table1.Data_2
XXXXX	ABC	100	150

I do not understand this behaviour as I would expect the merge to be performed on the latest step of the Table1 query, including the removal of old data. It looks like it is returning me some rows that are deleted in my Table1. 

Any hints on how to solve this ? 

Thanks a lot in avance! 

[Anonymous · Accepted solution]

Hi @EtienneT ,
Thanks for @PwerQueryKees reply.
Not sure about the structure of your second table based on your description, here's my test data
Table 2

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJRMjQwAJGmINLAXN/IWN/IwMhEKVYHrsAQrMAMpsAEQ4ExSMpP3xGqwAyqIBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Data_1 = _t, Data_2 = _t, Date_Field = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Data_1", Int64.Type}, {"Data_2", type text}, {"Date_Field", type date}}),
    SortedTable = Table.Sort(Source,{{"ID", Order.Ascending}, {"Date_Field", Order.Descending}}),
    AddIndex = Table.AddIndexColumn(SortedTable, "Index", 1, 1, Int64.Type),
    GroupedTable = Table.Group(AddIndex, {"ID"}, {
        {"LatestData", each 
            let
                LatestData1 = List.First([Data_1]),
                LatestData2 = if List.First([Data_2]) <> "N/A" then List.First([Data_2]) else List.First(List.Skip([Data_2], 1))
            in
                [Data_1 = LatestData1, Data_2 = LatestData2]
        }
    }),
    ExpandedTable = Table.ExpandRecordColumn(GroupedTable, "LatestData", {"Data_1", "Data_2"}),
    #"Merged Queries" = Table.NestedJoin(ExpandedTable, {"ID"}, #"Table (2)", {"ID"}, "Table (2)", JoinKind.LeftOuter),
    #"Expanded Table (2)" = Table.ExpandTableColumn(#"Merged Queries", "Table (2)", {"Other data"}, {"Table (2).Other data"})
in
    #"Expanded Table (2)"

Final output

Best regards,
Albert He

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

[PwerQueryKees]

#"Merge Table1" = Table.NestedJoin(#"Previous step", {"ID"}, #"Name of the final result",  {"ID"}, "Table 1", JoinKind.LeftOuter)

[PwerQueryKees]

Hard to say without seeing your code.

Everything is in one Query? Then you should refer to the name of the step showing the final result you showed in your post.

[PwerQueryKees]

You don't refer to the final query results.

Show your m code...

[EtienneT]

Hey, thanks for the answer 

I cannot post the code here for privacy policy but it looks like that : 

 #"Merge Table1" = Table.NestedJoin(#"Previous step", {"ID"}, #"Table1",  {"ID"}, "Table 1", JoinKind.LeftOuter)

How can I refer exactly to the final query result ? 

Thanks