Custom date formats in Power BI

Until the Power BI team gives us the ability to specify a custom date format on a column, one has to jump through a few hoops to get a custom date format to be recognized as a an actual date format. I finally decided to blog about it as it seems a common enough issue with a solution that is as simple as it is convoluted. Here goes.

 

As you can see, Power BI has failed to recognize this data as a date field and has decided to go for a numerical field. However, if we just try to change the format to a date, we are rewarded with this instead:

Not very helpful, I’m afraid. Clicking on the error message brings up a dialog that says that Power BI could not convert the field to a date – as there are just a few very select date formats that Power BI actually recognizes.

In order to get the original data (20180522 in this specific case) into a format that Power BI *will* recognize, one way is to do the following.

First, let’s split the column into the individual date parts. This can be done using “split column”, located on the Transform tab:

Unfortunately we can’t do the whole thing in one go, so we’ll do it in a few steps. The first step is to split the column by number of characters, like this:

This will give us one column with the four leftmost characters (2018) and one column with the rest. See where this is going? Right, but here is where it gets a tad evil.

You see, Power BI has already decided to automatically done a conversion to a numerical data type. That’s all fine and dandy, but it will wreak havoc with the next step as that’ll be splitting the second column just like we did with the first one. As the month part (05 in this case) has a leading zero, if we let Power BI convert this to numeric, the zero will be gone and the split will produce the wrong output. This is what we want to have at this stage, note the ABC icons for strings instead of the 123 of numbers.

After doing the splitting jig again, we’re left with this:

…and Power BI went ahead and changed the data type to a numeric AGAIN! Remove the “changed type” step and crack on – time to combine the columns into a usable field. Select the three fields we want to combine, and look for the “Merge Columns” button on the “Add Column” tab.

Power BI expects a date to behave in a certain way – one of the acceptable behaviors is having a dash between the date parts. When merging the columns we can select a custom separator and get the friggin’ dash in there, like so:

We are now treated to a new and shiny column called “MeasureDate” and Power BI thinks it’s a string. That’s OK, we’ll set Power BI straight in a second.

So can we NOW kindly have Power BI accept this as a date? Why, yes, thank you for asking! Power BI can easily interpret the above as a date, and we’ll end up with something like this (note the calendar icon of a date):

Now all that is left is housekeeping by selecting the three columns that we no longer need, right-clicking and choosing “Remove Columns”.

A quick column reorder later, we’re finally back where we started – but this time with a date field instead of a numerical one.

When you get tired of this clunky approach to what shouldn’t be a problem from the beginning, check out Lukas Lötter’s elegant PowerQuery function approach to solving the same problem here: https://www.oraylis.de/blog/local-date-formats-in-power-bi

 

Pre-Ignite episode 1

This is the first of several pre-Ignite episodes, and on this one we talk travel and packing.

If you have any additions, questions or comments, tweet Alexander (@arcticdba) or Simon (@Bindertech)!

Knee-deep in Tech on iTunes

Kneep-deep in Tech on Spotify

Pre-Ignite episode 1

Show notes:

ESTA: https://esta.cbp.dhs.gov/esta/
Noise cancelling headphones: https://thewirecutter.com/reviews/best-noise-cancelling-headphones/
Uber: https://www.uber.com/airports/mco/
Lyft: https://www.lyft.com/rider/cities/orlando-fl
Note that both Uber and Lyft are subject to a $5.80 extra charge on pickup from the airport!
Power plugs in the USA: https://www.power-plugs-sockets.com/united-states-of-america/
Weather in Florida: https://www.accuweather.com/en/us/orlando-fl/32801/weather-forecast/328169

Episode 45

The fortyfifth episode is up, a.k.a “wheels on your biceps” where we touch on news from the summer, Power BI updates, failing to clean a Surface laptop and upcoming changes to Microsoft Learning. We also cover what’s happening this fall, and the things we’ll be doing up to and at Ignite! Oh, and one more thing – we couldn’t cram all the info we wanted into 30 minutes, so it became about an hour. We’ll try to not do that again 🙂
We’ll be doing webinars in the not-so-distant future, and we’d love for you to tell us what you want the webcast to be about!

As always we gladly accept tips and criticism, as well as ideas for content for us to cover. Just tweet Alexander (@arcticdba) or Simon (@bindertech)!

Knee-deep in Tech on iTunes

Kneep-deep in Tech on Spotify

Episode 45