How to Insert Index Match with multiple criteria and IFERROR with nested formulas in Power Query

[MikeO]

I have a few tables I am tryingto combine for marketing performance purposes.

I have an Advertising Table, a Sales Table, a Customers Table, and Calls Table.  Currently, a lot of =Index Match, =Index Match with multiple criteria using a non-array, =COUNTIFS, and =IFERROR in my formulas to come up with ROI and CPC information.  However, I am dealing with hundreds of thousands of rows and it now takes about an hour calculate every time I make an update the tables in my excel spreadsheet.  I would love to stop this calculation in Excel and do it with the Power Query M language if possible.

Below are a few examples of what I want to complete in Power BI:

Assign a creative to a phone call using index match with multiple criteria: 

- Using the Newspaper and Call Date columns in my Calls Table

- Using the Creative Name, Newspaper, Day Before Run Date, and Day After Run Date columns in my Advertising Table

I created a Creative Name column in my Calls Table with the following formula:

=INDEX(NPOrders[Creative Name],MATCH(1,INDEX(([@Newspaper]=NPOrders[Newspaper])*([@[Call Date]]>NPOrders[Day Before Run Date])*([@[Call Date]]<NPOrders[Day After End Date]),0,1),0))

Create a Sales Column by Item Typel using IFERROR:

This one only uses the Sales Table.  In the Sales Table there is a separate row for every type of sale and item.  I created columns for type of sale and type of item sold.  I have four different types of sales in my SalesType column: "Gross", "Cancelled", "Returned", "Replacement", "Exchanged", and "Fee".  I break up the items sold into two types in my Item Type column: a sale for Widget (left and right) and all other items sold.

I created a column of All Widgets Sold in my Sales Table with the following formula:

=IFERROR(IF(AND([@SalesType]="Gross",OR([@[Item Type]]="L_widget",[@[Item Type]]="R_widget")),[@[Net Sales]],""),"")

How would I create these formulas in the Power Query language?

Any help would be much appreciated!

[Ashish_Mathur]

Hi,

This should be possible.  Share some data and show the expected result.

[MikeO]

Below is from the Calls Table, every row is a unique caller.  The Creative column is the result from the index match formula with multiple criteria between the two tables below.

		Newspaper	Call Date					Creative
		NP1	12/31/2016					Ad 1
		NP1	12/31/2016					Ad 1
		NP2	1/1/2017					Ad 2
		NP3	1/2/2017					Ad 3
		NP4	1/2/2017					Ad 3

Below is the Advertising Table.  Each row is unique per run date of a creative in a newspaper.

Newspaper	Creative Name	Day Before Run Date	Day After End Date
NP1	Ad 1	12/30/2016	2/10/2017
NP2	Ad 1	12/29/2016	1/6/2017
NP3	Ad 2	12/20/2016	1/30/2017
NP4	Ad 3	1/1/2017	1/15/2017

[Ashish_Mathur · Accepted solution]

Hi,

The correct answer should be AD1,AD1,AD1,AD2,AD3.  This is the calculated column formula i have written in Call Table of the Query Editor

=CALCULATE(FIRSTNONBLANK(Advertising[Creative Name],1),FILTER(Advertising,Advertising[Day Before Run Date]<=EARLIER(Calls[Call Date])&&Advertising[Day After End Date]>=EARLIER(Calls[Call Date])&&Advertising[Newspaper]=EARLIER(Calls[Newspaper])))

Hope this helps.

[MikeO]

This isn't quite reading between the two tables.  When I try to create this as a new measure or a calculated column every column name referenced in the Calls table is greyed out and I getthe below message:

EARLIER/EARLIEST refers to an earlier row context which doesn't exist.

Do I have to build a relationship between these tables first?

[Ashish_Mathur]

Hi,

It is working fine.  No relatioship is required.  Download the PBI file from here.

Hope this helps.

[MattAllington]

I think you should be looking at Data modelling using the modelling engine (power pivot) instead. This is what Power BI is built to do. 

This article I wrote describes Power pivot, but it is the same in Power bi. https://exceleratorbi.com.au/what-is-power-pivot/