Log In
Register
Home
September 15, 2020
Offline
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
See Answer
October 5, 2010
Offline
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.
Answers Post
1 Guest(s)
