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...

Technical requirements

The Power Query editor within Power BI Desktop can be used to complete the examples in this chapter. For a review of using the Power Query editor, see Chapter 2.

Dates

Dates are the backbone of temporal analysis, forming the foundation upon which we generate meaningful insights about data. There are almost 60 M functions specific to working with dates. But dates have a secret – they are really just numbers that represent the number of days prior to or after a particular reference date.

We can demonstrate this with a simple example by creating a blank query and using the advanced editor with the following code:

1. Create the following query in the Power Query editor:

   let
       Source = List.Generate(() => -10, each _ <= 10, each _ + 1)
   in
       Source
   

2. In the List Tools | Transform tab of the ribbon, click the To Table button:

Figure 10.1: Convert list to table

3. On the To Table dialog, simply click the OK button.
4. Right-click the header for Column1 and choose Duplicate Column.
5. Right-click the header for Column1, choose Change Type, and then Date:

Figure...

Time

While perhaps not as frequently used in reporting as much as dates, time can also be an important reporting element, particularly when reporting on metrics such as employee performance or supply chain productivity.

Similar to dates, time is also represented in numeric form as fractions of a day. To observe this, create a new query with:

let
    Source = List.Generate(() => 0, each _ <= 24, each _ + 1),
    Table = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    AddMultiplicationColumn = Table.AddColumn(Table, "Multiplication", each [Column1] / 24, type number),
    DuplicateColumn = Table.DuplicateColumn(AddMultiplicationColumn, "Multiplication", "Multiplication - Copy"),
    ChangeType = Table.TransformColumnTypes(DuplicateColumn,{{"Multiplication - Copy", type time}}),
    RenameColumns = Table.RenameColumns(ChangeType,{{"Multiplication - Copy", "Time"}})
in
    RenameColumns...

Dates and times

As one might anticipate, the representation of both a date and a time is possible within the M language. This data type is a datetime data type and exhibits similar properties to both date data types and time data types. In fact, since dates are simply whole numbers based upon a reference date and time is a decimal number reflecting the fractions of a day, a datetime data type can be represented as a decimal number, where the whole number portion represents the date and the decimal portion represents the time. For example, the following expression returns noon on February 14^th (2/14/2024 12:00:00 PM):

DateTime.From(45336.5)

The individual components of date and time can be extracted from a datetime data type using the DateTime.Date and DateTime.Time functions. Alternatively, the DateTime.ToRecord function returns a record with field/value pairs for Year, Month, Day, Hour, Minute, and Second.

Similar to dates and times, a constructor is available for the...

Time zones

While eminently practical and relatively easy to understand, time zones have caused issues with software systems and programming from the very dawn of computing. Fortunately, the M language includes the datetimezone data type, with an array of supporting functions that ease the burden of dealing with dates and times that include time zones.

Unlike date, time, and datetime data types, datetimezone data types cannot be represented solely as decimal numbers. In fact, creating a datetimezone value using DateTimeZone.From(45336.5) adds the additional time zone information of the local system, returning 2/14/2024 12:00:00 PM -05:00, for example, for a system running in the United States EST time zone.

There are actually two additional elements for a datetimezone data type: ZoneHours and ZoneMinutes. These additional elements can be retrieved using the DateTimeZone.ZoneHours and DateTimeZone.ZoneMinutes functions, respectively. Additionally, the DateTimeZone.ToRecord function...

Duration

Unlike the Analysis Services tabular data model, the M language includes a data type for duration. The unfortunate omission of a duration data type in Analysis Services means that calculations involving duration are perhaps better handled in Power Query rather than with DAX columns or measures. However, it is also important to realize that duration data type columns in Power Query are converted to decimal numbers when loaded into the data model. Consider the following expression:

let
    Source = Duration.From("0.01:00:00"),
    #"Converted to Table" = #table(1, {{Source}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type duration}})
in
    #"Changed Type"

This single row table, containing a duration data type column with a value of 1 hour, when loaded into the data model becomes the value 0.0416666666666667, which is the fractional amount of a day (1 hour/24 hours...

Summary

In this chapter, we covered the topics of dates, times, dates and times, time zones and durations. The majority of the functions dealing with these data types were covered, and we explored some practical applications of using these functions, including Melissa de Korte’s famous Extended Date Table, the creation of functions for converting between Gregorian dates and Julian days, and handling alternate date formats. We also covered examples of calculating the number of working days between two dates and how to calculate a moving average. With respect to time, we demonstrated the creation of a time dimension table at both the minute and second level of granularity and how to classify time into first, second, and third shifts. Dates and times were also covered and included an example for correcting data refresh times. Finally, durations were discussed, with examples of calculating work duration and converting work days to durations.

In the next chapter, we continue...