Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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!
Solved! Go to 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.
Hi,
This should be possible. Share some data and show the expected result.
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 |
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.
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?
Hi,
It is working fine. No relatioship is required. Download the PBI file from here.
Hope this helps.
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/
User | Count |
---|---|
83 | |
77 | |
70 | |
69 | |
54 |
User | Count |
---|---|
106 | |
96 | |
90 | |
79 | |
68 |