Forum Discussion
Merged queries with deleted duplicates and filtered rows
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!
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 2let 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 HeIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly
5 Replies
- PwerQueryKees
Super User
You don't refer to the final query results.
Show your m code...
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
- 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
#"Merge Table1" = Table.NestedJoin(#"Previous step", {"ID"}, #"Name of the final result", {"ID"}, "Table 1", JoinKind.LeftOuter)
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 2let 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 HeIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly
