HI All:
I have a calendar table for which I am adding a custom column to notate the Fiscal Operational Period.
For example, if dates are between 2/3/2020-3/1/220 then P1, if dates are between 3/2/20-3/29/2020 then P2, if dates are between 3/30/20-4/3/20 then P3 etc.
Below is my initial PQ M code, how do I add for P2, P3 up to P12? As you can see Operational Periods don't line up with months exactly.
= Table.AddColumn(#"Inserted Week of Month", "Custom", each if
[Dates] >= #date(2020, 2, 13) and [Dates] <= #date(2020, 3, 1)
then 1 else 0)
Thanks
Robb
Hi Robb,
You can add extra clauses to an if like so:
= Table.AddColumn(#"Inserted Week of Month", "Custom", each if [Dates] >= #date(2020, 2, 13) and [Dates] <= #date(2020, 3, 1) then 1
else if .........
else if .........
else .......
)
The final else is a catch all if there's a condition that hasn't already been met by the preceding clauses.
But, you can simplify this entire step like so
= Table.AddColumn(#"Sorted Rows", "Month", each if [Dates] <= #date(2020, 1, 31) then 1
else if [Dates] <= #date(2020, 2, 29) then 2
else if [Dates] <= #date(2020, 3, 31) then 3
else null
)
The if will stop evaluation when it meets a condition that is true, so as long as you check conditions in the correct order - in this case check for the earliest period first, the 2nd period next etc.
Please see attached example where I've created a table of dates, brought them into PQ and then created a conditional column based on what month that date is in.
I've only done up to March but this should be enough for you to see how it works and apply to your own situation.
Regards
Phil
P.S. Are you using the GUI to create the if/then/else ? It's easier to use than coding M by hand.
Hi Phil:
Awesome, thank you. I see now how M and PQ UI work in this situation!
I would have thanked earlier, but can't find the standard reply button on my phone.
Regards
Robb
No worries Robb.
