Power BI – Power Query Editor – Converting Dates From Another Locale – Expense Report Example

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:

PowerBI date 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:

That’s it.

 

Advertisements


Categories: Business Management, Office 365 and O365, Power BI

Tags: , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: