Replicating an INDEX/MATCH formula using multiple variables

[Anonymous]

I have two data tables.

1. Call Data - the important fields here are "CALL ID", "AGENT", "CALL START TIME", "CALL END TIME"

2. Interaction Records - the important fields here are "AGENT", "RECORD ID", "CREATION TIME"

I match the interaction records to the calls based on it being the same AGENT and the CREATION TIME falling between the CALL START / CALL END times and then I append the CALL ID to the Interaction Record. There can be multiple Interaction Records matching to a single call. Also, it is possible that a call has no matching interaction records or that an interaction record has no matching calls.

I'd like to move the matching up into POWERBI rather than doing it in Excel before I load because I'm dealing with millions of records. Right now the Excel array that I use is:

{=IFERROR(INDEX('Call Data'!$A:$A,MATCH(1,IF(IR-AGENT='Call Data'!$R:$R,IF(CREATION TIME>='Call Data'!$C:$C,IF(CREATIOIN TIME<='Call Data'!$E:$E,1))),0)),0)}

How do I replicate this in Power BI?

[Eric_Zhang · Accepted solution]

@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"

[dearwatson · Accepted solution]

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.

[Eric_Zhang · Accepted solution]

@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"