Many of us work with data in Power BI that includes date and time information mined from sources in different locales or regions of the world than our own. In this post, I’m using a simple CSV of expenses that came from London, and I’m in the United States.
If you’re not familiar with the differences in short date formatting in the UK versus the US, it’s evident through this example:
- Today’s date is September 21, 2018
- In the UK, that’s formatted as d/m/yyyy, or 21/9/2018
- In the US, it’s formatted as m/d/yyyy or 9/21/2018
The root of the problem is that Power BI is configured for my locale, “en-US”. The data I’m given, however, has the date information formatted in English (United Kingdom), “en-GB” locale. So, if I were to pull in a CSV drop of expenses from London, Power BI will not recognize the “Date Claimed” column of the expense report as a date data type. Instead, after import and the initial automatic conversion by Power BI, the data type is set as text:
This is one of those things that should be simple to handle, and it is in Power BI. The date can be transformed into your locale’s format, as an actual date data type, by telling Power BI what locale the original data is in: