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