You load the data in Power BI, visualize it and… it looks wrong. Instead of the European Day-Month-Year format, you get the funny-looking US Month-Day-Year one. Or the other way around.
Even worse, when you try to summarize the January sales, you get 12 months instead.
Intelifly.com took the challenge and looked at this issue. They prepared a demo on how to import and present the dates, no matter what the source format looks like. Our Intelifly partners specialize in finding advanced solutions to your data challenges, mostly by using Microsoft’s Power BI analytics software. If you are curious about what can be achieved, have a look at the free demos available on intelifly.com – the COVID pandemic evolution (tracked daily) and the Wiener Boerse status (Vienna Stock Exchange).
To better explain what is going on, we have prepared several steps:
1. Input tables
There are 3 tables available in Microsoft Excel, showing 3 dates formats:
- an US-formatted dates table (i.e. MM-DD-YYYY style),
- an European-formatted dates table (i.e. DD-MM-YYYY style) and
- an unformatted dates table – where I specifically formatted the dates as Text and forced them to the dubious General format (which is a way of MS Excel saying “I don’t know what you want from me”).
These tables are used to demonstrate Power BI’s dates importing behavior.
They are placed in a Microsoft Excel file and formatted accordingly – this can be seen by right-clicking on the respective cells (or columns) – and checking the format that was selected there:
European dates:
US dates:
Unformatted dates:
2. Import into Power BI
Simply put, Power Business Intelligence is…. intelligent. In other words, if the data was pre-selected within the source AND if Power BI can properly “understand” the source, it will import it correctly. Naturally, Microsoft Excel and the cells formats there can be expected to behave and be properly imported.
All Power BI imported tables look as follows
There is one exception: when the data is not defined (i.e. the dataset has no pre-assigned format), Power BI starts guessing. In our particular case (unformatted dates table), Power BI correctly guesses that the cells are or a date format (using the “/“ character). What Power BI misses in this case is simply the CORRECT date format – which is dependent on the geographical origin. The next step is logical – Power BI assumes that this data comes in the US format – i.e. in the MM/DD/YYYY format. Or, if you want, in the format of having the month first, then the day and the year at the end.
In our case this was simply the wrong assumption. Of course, it is better than nothing.
But what happens when you need to import data that is simply impossible to be changed at the source? Even worse, what happens when you have different date formats in different data sources – do you simply have to guess them permanently? Do you need to correct them at the source?
What if you have multiple data sources from multiple countries, each with a different date format?
3. The solution – change format “Using locale”
One of the simple (and thus brilliant) features in Power BI remains the ability to import using the local format. In other words, when shaping the data during the import phase, any analyst can actually “tell” or “translate” to Power BI’s M language what is the geographical origin of certain data columns.
One can do this by selecting the column, (right) clicking on “Change format” and selecting the very last option from the expanded menu:
In that simple menu, you can select the original data format. By doing so, Power Bi will now be able to properly retrieve and store the date (and not only) column format – and retrieve it correctly as often as possible.
This feature is actually very useful not only for the dates, but for a variety of other purposes. One of the most often met problems in finance is retrieving numbers from various systems.
Sometimes the decimal numbers format use the dot as a thousands separator + the comma as a decimals separator.In other cases the column is, you guessed it by now, in the opposite format: comma as a thousands separator and dot as a decimals separator.
To avoid confusing the users and Power BI, it is always a good practice to check the local format of the dates and of the numbers – and pre-select it with the “Using locale” option. This will not only save you a lot of time, but it will also avoid further corrections or mis-interpretations downstream. Especially when dealing with a multi-country audience, it is a a great idea to pre-check the dates format.
Did you find this Power BI blog interesting? Useful? Please leave us a comment to understand if we did well. Thank you!
Of course, not all data challenges are as simple as this one. If you have a complex analytics problem, intelifly.com can try to address it – using Power BI and complex related technologies to unravel and automate it. For multiple stakeholder’s groups, with advanced programming skills put at (your) good use.
Note:
This blog is the first in a series addressing Power BI best practices. If you find it useful and/or interesting, we would appreciate your comments – also if you have improvement suggestions.