How to Import Dates Correctly Across Culture or Locales

Have you written a report where dates import right. But they break when ran on another machine? If you import right, you don’t have to set cultures in your report settings.

This Is Caused by Default Cultures

When you import columns with Table.TransformColumnTypes, the UI doesn’t include the culture parameter.

If you pick Date and “Using Locale…” it will replace this

// replace this
= Table.TransformColumnTypes( Source,{  {"Date", type text}} )
// with this
= Table.TransformColumnTypes( Source,{  {"Date", type text}}, "en-GB" )

Now I can import a CSV that uses the culture “English UK” or “German” or others
And it still works when it’s ran on an “English-US” environment

When is Culture / Locale used?

Every time you convert dates and numbers to text, culture is involved. When you convert from text to numeric values, culture is involved.
If you don’t set the culture parameter, it falls back on the value Culture.Current . Mine uses “en-us”

Sample data to break things

Here’s a variety of dates to compare which breaks. They came from cultures using the named ShortDate “d” format string

Related Links

• With Nin.PqLib for powershell it’s easy to export named format strings for all cultures
• Listing of Named Date Format Strings – Microsoft docs