Forum Discussion
Merge tables with closest dates (and missing IDs)
Hello all. I'm really stuck with this. Please help.
Table 1:
Patient# Visit# Visit Date Weight
1 134 1/1/2022 154
1 235 2/1/2022 164
Table 2:
Patient# Visit# Visit Date Result Date Result Value
1 134 1/1/2022 1/3/2022 8.7
1 1/25/2022 9.4
1 235 2/1/2022 2/5/2022 7.4
My first problem is that not all Results have a matching Visit# or Visit Date, but they do have a Patient#.
Second problem is that Result Date does not match Visit Date.
My desired result is a table that shows all results for the pt, including the ones that don't have a Visit#:
Patient# Visit# Visit Date Weight Closest Result Date Result Value
1 134 1/1/2022 154 1/3/2022 8.7
1 1/25/2022 9.4
1 235 2/1/2022 164 2/5/2022 7.4
I thank you all for the help with this.
Hi Syndicate_Admin ,
You only need to do the merge feature for Table2 to get the result, like:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0NgGSRgZGRvqG+oYIpjGQaaFnrhSrA1EHQVBJI1Mg21LPBC5rZGwKkzVCmGKkDxI1B6mLBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Patient#" = _t, #"Visit#" = _t, #"Visit Date" = _t, #"Result Date" = _t, #"Result Value" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Patient#", Int64.Type}, {"Visit#", Int64.Type}, {"Visit Date", type date}, {"Result Date", type date}, {"Result Value", type number}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Patient#", "Visit Date"}, #"Table 1", {"Patient#", "Visit Date"}, "Table 1", JoinKind.LeftOuter), #"Expanded Table 1" = Table.ExpandTableColumn(#"Merged Queries", "Table 1", {"Weight"}, {"Weight"}), #"Reordered Columns" = Table.ReorderColumns(#"Expanded Table 1",{"Patient#", "Visit#", "Visit Date", "Weight", "Result Date", "Result Value"}), #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Result Date", "Closest Result Date"}}) in #"Renamed Columns"Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Your result table is same as Table 2. So what is the difference?
Good catch. I changed Table 1.
Hi Syndicate_Admin ,
You only need to do the merge feature for Table2 to get the result, like:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0NgGSRgZGRvqG+oYIpjGQaaFnrhSrA1EHQVBJI1Mg21LPBC5rZGwKkzVCmGKkDxI1B6mLBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Patient#" = _t, #"Visit#" = _t, #"Visit Date" = _t, #"Result Date" = _t, #"Result Value" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Patient#", Int64.Type}, {"Visit#", Int64.Type}, {"Visit Date", type date}, {"Result Date", type date}, {"Result Value", type number}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Patient#", "Visit Date"}, #"Table 1", {"Patient#", "Visit Date"}, "Table 1", JoinKind.LeftOuter), #"Expanded Table 1" = Table.ExpandTableColumn(#"Merged Queries", "Table 1", {"Weight"}, {"Weight"}), #"Reordered Columns" = Table.ReorderColumns(#"Expanded Table 1",{"Patient#", "Visit#", "Visit Date", "Weight", "Result Date", "Result Value"}), #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Result Date", "Closest Result Date"}}) in #"Renamed Columns"Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
