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