Replicating an INDEX/MATCH formula using multiple variables

Anonymous

You can try to do that in Power Query.

Check 

let  
    Call=  Table.FromRows({{1,	1,	"3/1/2017",	"4/1/2017"},{2,	2,	"3/1/2017",	"4/1/2017"},{3,	3,	"3/1/2017",	"4/1/2017"},{4,4,	"3/1/2017",	"4/1/2017"}}, {"CALL ID", "AGENT","CALL START TIME","CALL END TIME"}),

    Interaction=Table.FromRows({{1,1,"3/2/2017"},{1,2,"3/3/2017"},{2,3,"3/3/2017"},{5,4,"3/3/2017"}},{"AGENT" , "RECORD ID", "CREATION TIME"}),
 
 
    #"Merged Queries" = Table.NestedJoin(Interaction,{"AGENT"},Call,{"AGENT"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"CALL ID", "AGENT", "CALL START TIME", "CALL END TIME"}, {"NewColumn.CALL ID", "NewColumn.AGENT", "NewColumn.CALL START TIME", "NewColumn.CALL END TIME"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded NewColumn", each ([CREATION TIME] >= [NewColumn.CALL START TIME] and [CREATION TIME] <=[NewColumn.CALL END TIME]) or ([NewColumn.CALL ID] = null))
in
    #"Filtered Rows"

As Eric pointed out you can pretty easily do the join in Power Query, I do this a lot for reconcilations.

Here's the no-code version:

Open the Query editor and select one of the tables then use "Merge Queries" in the top right of the Query Editor window.

You are looking for a "full outer join" on AGENT which will give you both matching and unmatched records.

Expand the column and pick the CALLID column to add it to the records, this will give you a bunch of extra records as each matching call will be added as a row..

You should be able to figure it out from there.