cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lmmccrae Frequent Visitor
Frequent Visitor

Replicating an INDEX/MATCH formula using multiple variables

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?

2 ACCEPTED SOLUTIONS

Accepted Solutions
Moderator Eric_Zhang
Moderator

Re: Replicating an INDEX/MATCH formula using multiple variables

@lmmccrae

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"

View solution in original post

dearwatson Member
Member

Re: Replicating an INDEX/MATCH formula using multiple variables

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.

 

Capture.PNG

 

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.

 

 

 

"The commonality between science and art is in trying to see profoundly - to develop strategies of seeing and showing." - Edward Tufte

View solution in original post

2 REPLIES 2
Moderator Eric_Zhang
Moderator

Re: Replicating an INDEX/MATCH formula using multiple variables

@lmmccrae

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"

View solution in original post

dearwatson Member
Member

Re: Replicating an INDEX/MATCH formula using multiple variables

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.

 

Capture.PNG

 

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.

 

 

 

"The commonality between science and art is in trying to see profoundly - to develop strategies of seeing and showing." - Edward Tufte

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 6 members 1,455 guests
Please welcome our newest community members: