Merge queries matching on dates and leave null if no match found

I have got two tables below (examples):

Table SFID

Sales Force ID	Type	Name	Assistant	From	To
123	ABC	Store A	Ben	01/04/2020	30/04/2020
123	ABC	Store A	Jen	01/05/2020	31/05/2020
123	ABC	Store A	Ben	01/06/2020	21/06/2020
126	DEF	Store B	Tim	01/04/2020	30/04/2020
126	DEF	Store B	Tim	01/04/2020	null

and

Table Activity

Transaction ID	Date	Sales Force ID
1	03/05/2020	123
2	03/06/2020	200
3	01/01/2021	123
4	02/01/2021	126

I want my end result to be

Transaction ID	Date	Sales Force ID	Type	Name	Assistant
1	03/05/2020	123	ABC	Store A	Jen
2	03/06/2020	200	null	null	null
3	01/01/2021	123	null	null	null
4	02/01/2021	126	DEF	Store B	Tim

To do this, the best solution was the one posted in here with some modifications (allow both To and From to be null). However, only the row on transaction ID 2 disappears because that Sales Force had already had assistant entries (they get wiped out on the filtered row step). I also tried the solution presented in here but it takes ages to load.

I would like to know if there was a way to guarantee all transactions appear without having to introduce lines to table SFID for periods they don't have assistants and without making the query a really slow one.

This is my code:

    let
    Source = Source,
    #"Merged Queries" = Table.NestedJoin(Source,{"Sales Force ID"},SFID,{"SFID"},"SFID",JoinKind.LeftOuter),
    #"Expanded SFID" = Table.ExpandTableColumn(#"Merged Queries", "SFID", {"Type", "Name", "Assistant", "From", "To"}, {"Type", "Name", "Assistant", "From", "To"}),
    #"Changed Type" = Table.TransformColumnTypes(#"SFID",{{"Date", type date}, {"From", type date}, {"To", type date}}),
    FilteredRows = Table.SelectRows(#"Changed Type", each ([Date] >= [From] and [Date] <= [To]) or ([Date] >= [From] and [To] = null)or ([From] = null and [To] = null)),
    #"Removed Columns" = Table.RemoveColumns(FilteredRows,{"From", "To"})
in
    #"Removed Columns"

Solution

So after almost 2 weeks with no response and an unjustified downgrade, I found a solution!

I created a query that would basically produce this table with the above mentioned code.

Transaction ID	Date	Assistant
1	01/01/2021	Jen
4	02/01/2021	Tim

let
    Source = Source,
    #"Merged Queries" = Table.NestedJoin(Source,{"Sales Force ID"},SFID,{"SFID"},"SFID",JoinKind.LeftOuter),
    #"Expanded SFID" = Table.ExpandTableColumn(#"Merged Queries", "SFID", {"Type", "Name", "Assistant", "From", "To"}, {"Type", "Name", "Assistant", "From", "To"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded SFID",{{"Date", type date}, {"From", type date}, {"To", type date}}),
    FilteredRows = Table.SelectRows(#"Changed Type", each ([Date] >= [From] and [Date] <= [To]) or ([Date] >= [From] and [To] = null)),
    #"Removed Columns" = Table.RemoveColumns(FilteredRows,{"From", "To"})
in
    #"Removed Columns"

And left-joined it on my initial version of Table Activity

I kept the information of type Type and Name in a separate Query (as they don't change) and then again left-joined it on the Table Activity.

Final query looks like this:

let
    Source = Source,
    #"Merged Queries1" = Table.NestedJoin(Source,{"Sales Force Code"},Info,{"SFID"},"Info",JoinKind.LeftOuter),
    #"Expanded Info" = Table.ExpandTableColumn(#"Merged Queries1", "Info", {"Type", "Name"}, {"Type", "Name"}),
    #"Merged Queries" = Table.NestedJoin(#"Expanded Info",{"ID"},IDvsAssistant,{"ID"},"IDvsAssistant",JoinKind.LeftOuter),
    #"Expanded IDvsAssistant" = Table.ExpandTableColumn(#"Merged Queries", "IDvsAssistant", {"Assistant"}, {"Assistant"})
in
    #"Expanded IDvsAssistant"