Search code examples
date-rangepowerquerynested-ifm

Date Ranged IF statement in PowerQuery

I want to compare workforce details between Academic Years. I have a Start Date and an End Date.

I want to create custom columns which indicate if they were working in 2014/15, 2015/16 etc, then unpivot so I have a row for each year of employment.

so for 2015/16

*=if (([startdate] > 01/09/2015) and if([enddate] > 31/08/2016),"Yes","No")*

struggling a bit with M language, can you help with the syntax?

Solution

  • I would use something like this:

    = if [startdate] > #date(2015,9,1) and [enddate] > #date(2016,8,31) then "Yes" else "No"

    Note PQL / M is case sensitive.

    Don't beat yourself up: after 3 years, the doco on both the if statement and date constants still doesn't exist AFAIK.