Reply
Regular Visitor
Posts: 22
Registered: ‎06-05-2017
Accepted Solution

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

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!


Accepted Solutions
Super User
Posts: 3,941
Registered: ‎01-14-2017

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

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.

 

Untitled.png

View solution in original post


All Replies
Highlighted
Super User
Posts: 1,627
Registered: ‎07-03-2015

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

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/

* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Super User
Posts: 3,941
Registered: ‎01-14-2017

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

Hi,

 

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

Regular Visitor
Posts: 22
Registered: ‎06-05-2017

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

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.

 

  NewspaperCall Date    Creative
  NP112/31/2016    Ad 1
  NP112/31/2016    Ad 1
  NP21/1/2017    Ad 2
  NP31/2/2017    Ad 3
  NP41/2/2017    Ad 3

 

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

 

NewspaperCreative NameDay Before Run DateDay After End Date
NP1Ad 112/30/20162/10/2017
NP2Ad 112/29/20161/6/2017
NP3Ad 212/20/20161/30/2017
NP4Ad 31/1/20171/15/2017
Super User
Posts: 3,941
Registered: ‎01-14-2017

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

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.

 

Untitled.png

Regular Visitor
Posts: 22
Registered: ‎06-05-2017

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

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?

Super User
Posts: 3,941
Registered: ‎01-14-2017

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

Hi,

 

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

 

Hope this helps.