Left Join Between dates

Hi Armand_Theron 

Please open "Transform data", then check my queries below:

in "tableb" merge queries with "tablea" in "right outer" way,

then add custom columns and filter rows in "tableb",

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hc5LCsAgDATQqxTXgvm1Fq8i3v8aNVaRiODCRcLLODk7DBgICK6Y5HZ+zm+f62MAV7y10VpSS7rb2CWXdZZmLUVJ9dqbBY7g+sOPacmlTd8ThZEqR9oKsPKFzrZk2z4dlw8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [b_SD = _t, b_ED = _t, b_Dataset = _t, b_RowsCopied = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"b_SD", type datetime}, {"b_ED", type datetime}, {"b_Dataset", Int64.Type}, {"b_RowsCopied", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"b_Dataset"}, Tablea, {"a_Dataset"}, "Tablea", JoinKind.RightOuter),
    #"Expanded Tablea" = Table.ExpandTableColumn(#"Merged Queries", "Tablea", {"a_eventDate", "a_Schedule", "a_Dataset"}, {"Tablea.a_eventDate", "Tablea.a_Schedule", "Tablea.a_Dataset"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Tablea", "Custom", each if [b_Dataset]<>null and [Tablea.a_eventDate]>=[b_SD] and [Tablea.a_eventDate]<=[b_ED]  then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1))
in
    #"Filtered Rows"

create a new query1 which uses "tablea" data,

then in new query, merge query with "tableb" based on two columns "datatset" and "eventdate"

let
    Source = Tablea,
    #"Merged Queries" = Table.NestedJoin(Source, {"a_Dataset", "a_eventDate"}, Tableb, {"b_Dataset", "Tablea.a_eventDate"}, "Tableb", JoinKind.LeftOuter),
    #"Expanded Tableb" = Table.ExpandTableColumn(#"Merged Queries", "Tableb", {"b_SD", "b_ED", "b_Dataset", "b_RowsCopied"}, {"Tableb.b_SD", "Tableb.b_ED", "Tableb.b_Dataset", "Tableb.b_RowsCopied"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Tableb",{{"a_eventDate", Order.Ascending}, {"a_Dataset", Order.Ascending}})
in
    #"Sorted Rows"

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.