Power BI: Creating an IsWeekday function in Power Query M Language

I spend a lot of time doing consulting that involves Power BI. One of the core skills for working with Power BI is getting yourself comfortable with the M language that’s used in Power Query.

I was asked the other day, how to create a function that determines if a date is a weekday or a weekend. That’s actually quite easy. The Date.DayOfWeek function can be adapted to do that.

In Power Query, go to the Edit Queries option and choose to add a new query. (You could also start by duplicating an existing query). Then on the View menu, choose the Advanced Editor and replace any code that’s there with this:

(DateToTest as date)=>
let
    DateIsWeekday = Date.DayOfWeek(DateToTest, Day.Monday) < 5
in
    DateIsWeekday

Save it and call the query IsWeekday.

Date.DayOfWeek returns the day number in the week, but you need to tell it which day your week starts on. In this case, because I wanted to have Saturday and Sunday as weekend days, I started the week on Monday. That means that Saturday is then day 5 and Sunday is day 6 because the first day is day 0. So then all I need to do is check for a value less than 5.

If you need to change the weekend to say Friday and Saturday, you’d just need to replace the Day.Monday constant in the script with Day.Sunday.

Testing the function

Testing the function is also easy. Once you save the code, you’ll see a template for calling the function:

Select a date, click Invoke and see the result:

2019-06-28