Power Query at a glance
Date and Time in Power Query
Working efficiently with date and time — An overview
German version of this article here.
Automated, formula-based processing of dates and times is often a tedious and thankless task. With clever functions, however, Power Query makes it a breeze. Manual as manual entered formulas are often no longer required — time for an overview.
1. Import of international dates
a) Thousand and one date format
Problems with dates often already arise when importing a simple date. For some reason, there are thousands of different notations and formats for dates in this world. For example, April 5, 2001 here in Germany can be written as:
- 5.4.2021 (D.MM.YYY),
- 05.04.2021 (DD.MM.YYYY),
- 4/5/21 (dd.mm.yyy) or
- 05.04.21 (DD.MM.YY).
In other countries and language areas, the same date is also represented as:
- 04/05/2021 (MM.DD.YYY),
- 04/05/2021 (MM/DD/YYY) or also
- 05/04/2021 (DD/MM/YYYY).
ISO-8601 then comes with the idea to write it as:
- 2021–04–05 (YYYY-MM-DD).
When importing dates, it is therefore important that all these formats are recognized as correct dates. The following sample file will serve us as an example.
Each column contains the date in a different format/representation as a string:
- Column 1: DD.MM.YYY
- Column 2: YYYY-MM-DD
- Column 3: D.M.YY
- Column 4: MM/DD/YYY.
b) Recognizing dates as dates — a matter of interpretation
A date as a character string is usually of little use to us, at least not if we want to perform any calculations or evaluations with the date. Even a simple date filter requires a real date, not a string that only looks like a date.
In the next step we change the data type of all columns to “Date”.
The result then looks like this.
If it looks different on your computer, please read on, an explanation will follow shortly.
The problem in row 1 is obvious. In each cell we tried to import December 4, 2021 (German representation: 04.12.2021) but we got April 12, 2021 (German representation: 12.04.2021) in column 1 and 3. Why, because Power Query expected the string in the form MM.DD.YYYY but got it in the form DD.MM.YYYY and thus misinterpreted the date.
Solche Fehler können schwerwiegende Folgen haben und dennoch lange unerkannt bleiben oder zu scheinbar merkwürdigen Ergebnissen und Fehlern führen.
Auffälliger wird das Problem in der vierten Zeile. Hier konnte Power Query mit einem 22. Monat nichts anfangen und hat uns einen Fehler ausgeworfen.
Such errors can have serious consequences and yet remain undetected for a long time or lead to seemingly strange results and errors.
So we see, Power Query tries its best to convert the date from the original string into an applicable date. However, this attempt fails as soon as the string doesn’t match the format that Power Query expects.
But what does Power Query actually expect? And what is the solution to this confusion?
c) Setting of the global locale
What Power Query expects or how Power Query interprets a date from a string depends on your regional settings. The result of the previous step could therefore look different for you, e.g. like this:
Namely, if you set a different locale in the options in the regional settings.
So, as a result, we can partially avoid errors during import by using the right settings.
Note! How the date is displayed on your computer is yet another question that depends on the settings of your operating system. In this article we will only deal with the question of how a date is read in and recognized correctly internally.
d) There’s an easy solution — choose locale individually
Now it is not very practical to constantly change the global locale (or even fiddle around in the operating system). Sometimes it is not even possible — and it is not necessary, because Power Query offers a simple and individual way to deal with different date formats. And this goes like this:
This way we can select the source format (not the target format, this depends on the operating system!) for each column and each query individually.
See the result:
Note for the English date in column 4, the difference between USA and UK here:
DD/MM/YYYY vs. MM/DD/YYYY
Now we know how we can import very different date formats correctly, in a simple and reliable way. All errors a gone in the picture below, all dates are imported correctly.
e) Select locale via formula
If you don’t feel like selecting the locale with the mouse, you can simply enter it into the formula.
Typical ISO codes are:
- en-US: English (USA)
- de-DE: German (Germany)
- en-GB: English (UK)
f) Derive from examples
If you can’t find the right locale or the date is given in a very unusual form, Power Query can also “guess” the correct date using examples. This works as follows.
Now just enter the correct result for one or two examples.
With a little luck, Power Query guesses the result for the remaining rows (light gray text in the image above) and also suggests a formula.
In this example, we get a new column with our dates as a string in German format. We now can easily convert this to a real date using one of the ways described above.
2. Working with dates
Importing dates is mostly not an end in itself. Often we want to work with the date. And who ever had to deal with date functions ala YEAR(), DATE(), DATEVALUE() or DATEDIF() in Excel, knows what horrible formula orgies are sometimes necessary to make the simplest calculations. It is much more elegant with Power Query.
Especially for derived information, Power Query offers many useful (essentially the same) functionalities for this in two places at once. Why the same functionalities in two places? Well, once under “Transform”.
In this case, the current date column will be replaced.
And once under “Add Column”. In this case, a column with the calculation will be added.
a) Information about the year
Suppose we need only the year in our data model? No problem, one click is enough.
Done!
Power Query has added a column that in the old Excel world would have been calculated with the YEAR() function.
In a similarly simple way, we can get the beginning of the year and the end of the year. This can be a useful feature when we need to make deadline calculations that relate to the end or the beginning of the year.
b) Calculate difference
Wouldn’t it be interesting to know how many days are left between the date and the end of the year. No problem: we simply select both columns (CTRL + click).
With another click we can get the difference as days.
And without even writing a formula, we get a column with the difference as days.
c) Analyze months
What works with years, also works with months. We can extract information about the month from a date with a few clicks.
So we can get the month as a number, the beginning and the end of the month, the number of days of the month and the name of the month.
As you can see from the last two lines, Power Query also takes leap years into account: February 29, 1984 vs. February 28, 1986.
d) Quarters
A pretty cool feature are the functionalities for quarters. Especially for financial reporting this information and classifications are needed all the time.
The result speaks for itself.
e) Weeks
What goes for quarters also goes for information on the week.
And delivers the following result.
f) Days
Under the menu item “Day” you will find further self-explanatory but nevertheless interesting items, which can be helpful especially when analyzing financial data.
The items “Start of Day” and “End of Day” are uninteresting for pure dates, but for dates of date/time data type they can be quite helpful, as shown in the image below.
g) Date only
Now that we’ve been messing around with Date/Time, let’s stick with it for a moment. Occasionally we get Date/Time columns from databases, e.g. because the database automatically stores timestamps. If we are only interested in the date, we do not need to perform any wild string operations here, but simply tell Power Query our wish by clicking on it.
Date/Time becomes Date.
Alternatively, we could have simply changed the data type to date.
h) Get age
For the sake of completeness, the determination of the age with Power Query is also presented here. Let’s assume we have a column with birth dates (here in the format DD.MM.YYYY).
Suppose for some purpose we need the age of the person in our data model. Nothing easier than that.
And immediately we have the result.
In the column “Age” we get the age as days. We can convert this into years without doing any calculations as follows.
The result will look like this.
In practice, we rather rarely use decimal years or a decimal ages. Instead, we usually round down. And of course, there is no need for a formula for this either, all it takes is a click.
Whether the determination of the age in years from the days is correct in every case, I do not know. In respect of leap years and persons born on 29 February, I have doubts if the determination is made around this date. Before implementation in important models I strongly recommend to make own tests.
3. Working with time
a) General aspects
From a “Date/Time” date we can — similar to the date button above — extract various components by using the “Time” button. See figure below.
b) Pure time data
In Power Query, pure time data can also be processed, detached from a date. It should be noted that these are times, not time spans in the sense of a duration (e.g. operating hours, working hours, etc.). This means that aggregation in particular is not possible here (without further ado).
c) Time zone
Power Query also supports a date and time data type with time zone information.
This then looks as follows.
From such dates we can easily also determine a local time.
4. Working with time spans (duration)
a) General aspects
With the data type “Duration” we can process time spans in Power Query.
The time span is then represented as follows.
Day.Hours:Minutes:Seconds
For time spans, there are again lots of little helpers under the “Duration” button.
b) Calculate
With the data type “Duration” we can make calculations quite easily. In the example below we sum up three columns.
As a result, we get a column with the calculation results.
In simple cases, by the way, it works without any formulas. To do so, let’s select the columns with which we want to perform our calculations. This is done by pressing the CTRL or SHIFT key. Then, we select the desired calculation type by pressing the “Duration” button.
In this way we also get a sum column.
More on the conversion of these results later.
c) Aggregations
Unlike time, we can use the “duration” type in aggregations. Let’s take the following example.
Suppose we want to aggregate the operating hours for each robot.
Using the usual aggregation menu, we get the following result.
d) Converting Duration
Let’s stay briefly with the previous examples. Occasionally it can happen that we do not want to have e.g. operating hours displayed in days. Here again the button “Duration” helps us.
In that way we can convert the operating time e.g. into decimal hours, decimal minutes or decimal seconds.
There is a lot more to write in connection with date/time in Power Query. Especially for more complex cases. However, the points shown should suffice as an intro.
