Merging tables by multiple fields and date ranges

[Arcstud]

Hey Champions

How would you have bring the Group value from table A for each donation row in table B,

based on similar country and the relevant date range?

Thanks all for your help

[latimeria · Accepted solution]

Hi @Arcstud ,

You can try this:

table A

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi5IzMxT0lEyMNQ3MNU3MjAyBHEs9Q0NYBz3ovzSAgVHpVgdhGpDC30DC5gCOMcIrtoJrNq9KDWxRMGpKLMEYYchTCGIY4SqyxmrLkJ2uYB1uRUl5iWnglSY6hsYw5WbYih3RVWO6m9zqF6Ecjew8tBgRyLCyB1JLSFXeyCrRXaysSGGWk+l2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [country = _t, #"start date" = _t, #"end date" = _t, Group = _t]),
    #"Trimmed Text" = Table.TransformColumns(Source,{{"country", Text.Trim, type text}, {"start date", Text.Trim, type text}, {"end date", Text.Trim, type text}, {"Group", Text.Trim, type text}}),
    #"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"country", Text.Clean, type text}, {"start date", Text.Clean, type text}, {"end date", Text.Clean, type text}, {"Group", Text.Clean, type text}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Cleaned Text",{{"start date", type date}, {"end date", type date}})
in
    #"Changed Type"

Table B

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc69CoMwFAXgVwmZhfuT5JqMdWgfQDqJQygOLlKC70+TkipapzN893DuMOh7istr0o12lg3lJAdoFCOTHpuDe2tzIgE6uHDhsPllX3xx9oBS+vz1/h3npbAJLlRu1cbP/lbR/xDOKBLa+rjf8ZGmuKouzWsdECYsZ5L3/waERE4D4wc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [country = _t, #"Donation ID" = _t, Date = _t]),
    #"Trimmed Text" = Table.TransformColumns(Source,{{"country", Text.Trim, type text}, {"Donation ID", Text.Trim, type text}, {"Date", Text.Trim, type text}}),
    #"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"country", Text.Clean, type text}, {"Donation ID", Text.Clean, type text}, {"Date", Text.Clean, type text}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Cleaned Text",{{"Date", type date}}),
    BufferTableA = Table.Buffer(#"Table A"),
    #"Added Custom" = Table.AddColumn(
        #"Changed Type", 
        "Group", 
        (s)=> Table.SelectRows(
            BufferTableA,
            (c)=> s[country] = c[country] and (s[Date] >= c[start date] and s[Date] <= c[end date])
        )
    ),
    #"Expanded Group" = Table.ExpandTableColumn(#"Added Custom", "Group", {"Group"}, {"Group.1"})
in
    #"Expanded Group"

Each is sugar syntax for (_)=>.
You need to define the right row context by using variables.

And the result

You get more lines but there are overlaps in the sample.

Hope this help.