One problem that can really drive you crazy when working with data from text files is the many different formats that dates and numbers can be stored in around the world. For example, take a look at the contents of a tab-delimited file shown in the following screenshot:
There’s a single row of data and two columns, Date and Sales. What number and date are shown though? For me, living in the UK, the date shown is the 2nd of March 2015 (in the UK, like most of the world, we use the DD/MM/YYYY date format) and the number is one hundred thousand and two (in the UK we use the . sign as a decimal separator and the , as a thousands separator). However, if I was from the US and used the MM/DD/YYYY format I’d read the date as the 3rd of February 2015; and if I was from France and used a comma as a decimal separator, I’d read the number as a value just a tiny amount over one hundred. Of course, if you’re working in one country and getting data sent from another, which uses a different date or number format, you need to take all of these variations into account.
The good news is that Power Query has a really easy way of doing this for you, even if it’s not immediately obvious where this functionality is. You don’t need to change your Windows locale or split dates into their constituent parts and rearrange them in the correct order, or anything like that.
When you first load a csv file like the one shown above into Power Query, it will try to guess the data types of each column. Here’s the code generated by Power Query for this file:
[sourcecode language=”text” padlinenumbers=”true”]
let
Source = Csv.Document(
File.Contents(
"C:\Users\Chris\Documents\Power Query demos\PQDateNumberFormats.csv"),
null,"#(tab)",null,1252),
#"First Row as Header" = Table.PromoteHeaders(Source),
#"Changed Type" = Table.TransformColumnTypes(
#"First Row as Header",
{{"Date", type date}, {"Sales", Int64.Type}})
in
#"Changed Type"
[/sourcecode]
As you can see, it has created three steps in the query automatically:
- Source loads the data from the CSV file given and sees that it’s tab delimited
- First Row as Header uses the first row of data as the column headers
- Changed Type sets the Date column to be type date, and Sales to be an integer
To change how the column data types are interpreted though, you first need to delete the Changed Type step by clicking on the cross icon next to it, shown above. Then, right-click on the Date column, choose Change Type then Using Locale…
When you do this, the Change Type With Locale dialog appears:
A locale is simply a name for all of the rules for date and number formats and more associated with a particular language and region. So, for example, setting the Date column so that it is interpreted using the English (United States) locale, means that when the data is loaded into Power Query on my machine I see the date 3/2/2015.
What’s happened is that the csv file contains the date “2/3/2015”, I have told Power Query that the data source uses a US English format date, Power Query has then assumed this date is therefore in MM/DD/YYYY format, loaded the data for me and shown the date in my own locale (which is English (United Kingdom)) in DD/MM/YYYY format as 3/2/2015. The date itself hasn’t changed, just the way it is displayed.
Using the same technique to import the Sales column as a decimal value using the English United States locale like so:
…gives the following final result, where the comma is assumed to be a thousands separator:
Here’s the new code for the query:
[sourcecode language=”text”]
let
Source = Csv.Document(
File.Contents(
"C:\Users\Chris\Documents\Power Query demos\PQDateNumberFormats.csv"),
null,"#(tab)",null,1252),
#"First Row as Header" = Table.PromoteHeaders(Source),
#"Changed Type with Locale" = Table.TransformColumnTypes(
#"First Row as Header",
{{"Date", type date}}, "en-US"),
#"Changed Type with Locale1" = Table.TransformColumnTypes(
#"Changed Type with Locale",
{{"Sales", type number}}, "en-US")
in
#"Changed Type with Locale1"
[/sourcecode]
However, if I change the locale used to set the types of these two columns to French (France), I see the following:
You will see that I have told Power Query to interpret the value “100,002” as a decimal number using a French format, where a comma is used as a decimal separator, and it has therefore imported and displayed it as 100.002 in the English (United Kingdom) format with a decimal point used as the decimal separator.
So what happens if you receive a data dump each day with a mix of GBP and EURO like this
Country Price
GB 38.9
FR 43,99
GB 1, 398.87
FR 1.457,99
Assuming the number format is dictated by the currency, you would load all the values as text (deleting any Changed Type steps that were created automatically), then split into two queries based on currency symbol, change the type using different locales in each query, then use Append to create a single table for output.
Is there a list somewhere which contains all the separators per region? I usually do see what the separators are, but it is very hard to decide which region that combination belongs to. I’d very much prefer to be able to simply set the separators and date order than having to go on a witch-hunt to find the one I happen to need this time.
No, but since you asked (and I’m curious) I’ve just written an M query that does this. I’ll write it up in a blog post later this evening 🙂
Interesting!! Thanks.
Here you go: http://blog.crossjoin.co.uk/2018/12/06/windows-language-code-identifiers-and-date-number-formats-power-bi-power-query/
Awesome!!
This post helped me solve an issue. very helpful.
What if we have different Countries dates coming in same column . Like when using merge query we combine the date field coming from different countries format . How can we overcome this sort of situation.
I tried the above steps.
You would need to have another column that identified which country the date came from. Otherwise you would not know if 1/3/2019 was March 1st or January 3rd.
Article helped a lot, thanks! Would be usefull though to have this regional setting on a higher level. I my case I have a zip file with 30+ CSV’s, many of them have several columns that need to be “Change Type with Locale”.
Thanks for all the great comments. I seem to have a different problem. I’m importing dates from .Csv files (Bankstatements). Then I created a “staging query” to do all the cleaning up, of which one of the steps are to correct the Date Col type. I then run this query on all the monthly imported csv files with a big append of all these cleaned files. This is where my problem comes in. For some reason, PQ will land the date from the csv files in one instance dd/mm/yyyy and the very next file will land as yyyy/mm/dd. Now this is quite tricky as the staging query handles only one way and not the other. Any thoughts on this? I’m Thinking is there a setting in how PQ reads the Csv before landing the initial data perhaps?
Hi Chris, Is there a way to get the format to persist in the Power BI web service, as the format is rendered in Europe format, reversing dots by commas.
Thank you but this assumes that you know the locale to apply.
What if you receive files from several countries ?