I’ve searched through YouTube, particularly around group dating in PQ, which I am familiar with, and through online sites but nothing I can find do exactly what I am needed or I am not that sophisticated yet.
I’ve started by developing the table in PQ called Opportunities Test which aggregates and do date differences on the data. See image #1. But what I am trying to end up with a column called Influenced Campaign, see image #2, where I can filter and do other analysis.
Hi Yuhanna,
I understand that you have various BC# which have various campaigns:
BC#
RFP Received
Campaign
Campaign Start Date
1
18.08.2021
A
02.12.2020
2
01.07.2019
B
17.09.2018
3
06.03.2019
C
03.03.2018
4
25.03.2022
A
29.04.2021
1
18.08.2021
B
07.11.2020
2
01.07.2019
C
19.03.2019
3
06.03.2019
D
23.04.2018
4
25.03.2022
A
17.03.2021
1
18.08.2021
B
14.03.2021
2
01.07.2019
A
12.07.2018
3
06.03.2019
B
30.04.2018
4
25.03.2022
C
21.11.2021
You want to know which campaign started closest to the RFP. All other lines are filtered out:
BC#
RFP Received
Campaign
Campaign Start Date
1
18.08.2021
B
14.03.2021
2
01.07.2019
C
19.03.2019
3
06.03.2019
B
30.04.2018
4
25.03.2022
C
21.11.2021
Yes, you can group, get the Nearest Campaign Start Date (Max) and then expand again. Finally you filter the relevant lines and get rid of the Nearest Campaign Start Date column:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"RFP Received", type date}, {"Campaign Start Date", type date}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"BC#", "RFP Received"}, {{"Data", each _, type table [#"BC#"=number, RFP Received=nullable date, Campaign=text, Campaign Start Date=nullable date]}, {"Nearest Campaign Start", each List.Max([Campaign Start Date]), type nullable date}}),
#"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Campaign", "Campaign Start Date"}, {"Campaign", "Campaign Start Date"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Data", each [Campaign Start Date] = [Nearest Campaign Start]),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Nearest Campaign Start"})
in
#"Removed Columns"
It worked like a charm but I don’t want the nearest date, in my original post, I was looking for the Campaign with the nearest date “prior” to the RFP Received date. That is the trick.
If you see image #1, below take BC # 100228-01, the nearest date “prior” to the RFP received date is campaign SFHISTCUIDA37 with a campaign date of 12/31/2018.
In the PQ image #2, we have EWCUIDA57 selected. This came after the RFP received date, which is incorrect.
From marketing perspective, a proposal comes in but the campaign can’t get credit, or attributed revenue, after the proposal is received. This is the reason why I am looking for "nearest date prior to"
That is the syntax that is giving me headache. Thanks for your help.
Hi Yuhanna,
true my data assumed that all campaigns start before the RFP is received.
=> The trick is simply to filter all campaigns which start before the RFP is received:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"RFP Received", type date}, {"Campaign Start Date", type date}}),
#"Filtered Prior" = Table.SelectRows(#"Changed Type", each [Campaign Start Date] < [RFP Received]),
#"Grouped Rows" = Table.Group(#"Filtered Prior", {"BC#", "RFP Received"}, {{"Data", each _, type table [#"BC#"=number, RFP Received=nullable date, Campaign=text, Campaign Start Date=nullable date]}, {"Nearest Campaign Start", each List.Max([Campaign Start Date]), type nullable date}}),
#"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Campaign", "Campaign Start Date"}, {"Campaign", "Campaign Start Date"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Data", each [Campaign Start Date] = [Nearest Campaign Start]),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Nearest Campaign Start"})
in
#"Removed Columns"
Well, without grouping, you can make it even simpler. Simpler is normally better.
You can adjust the Sorted Rows step according to your needs (e.g. I added the BC# sorting), but please be aware that the Changed Type step is on purpose after the Sorted Rows Step:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Filtered Prior" = Table.SelectRows(Source, each [Campaign Start Date] < [RFP Received]),
#"Sorted Rows" = Table.Sort(#"Filtered Prior",{{"BC#", Order.Ascending}, {"Campaign Start Date", Order.Descending}}),
#"Changed Type" = Table.TransformColumnTypes(#"Sorted Rows",{{"RFP Received", type date}, {"Campaign Start Date", type date}}),
#"Removed Duplicates" = Table.Distinct(#"Changed Type", {"BC#", "RFP Received"})
in
#"Removed Duplicates"
You have bought me close. What I have done is create another column called Nearest Campaign. I will research a formula to change the “# Filter Pior” to look at Nearest Campaign column and filter only for the minimum positive value.
