Dealing with Dates, Times, and Durations

Given the importance of dates in reporting, understanding how to handle dates, times and durations in Power Query is a critical skill to master. Indeed, nearly every Power BI data model has, or should have, a date table dimension. This is because while knowing the value of a KPI or metric is important, it is much more important to know the value of that KPI or metric at specific dates or at specific times and even more valuable to understand the trend of that KPI or metric over the span of days, months, or even years.

Luckily, the M language includes well over 100 standard functions for dealing with dates, times, durations, date/times, and date/time/time zone calculations and transformations. With these functions, it is relatively simple to perform most standard temporal calculations. In this chapter, we will explore many of these functions as well as practical applications that will increase your depth of knowledge and understanding...