Merge tables with closest dates (and missing IDs)

[Syndicate_Admin]

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.

[v-yingjl · Accepted solution]

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.

[Vijay_A_Verma]

Your result table is same as Table 2. So what is the difference?

[Syndicate_Admin]

Good catch. I changed Table 1.