
culture
converting from text
. TransformColumnTypes
using the optional parameter: Culture
If they were all the same culture, like en-GB
, you can choose column type Using Locale...
which will create a step like this:

= Table.TransformColumnTypes(
Source,
{{"DateString", type datetime}},
"en-GB"
)
Import Using Dynamic cultures
Using a Lookup Column
Record.Field( record, key)
lets you lookup a record
field without hard-coding the name. That value is used by DateTime.FromText( string, culture)
let
// These datetimes are all the same time, using a different 'culture'
Source = #table(
type table[DateString = text, User = text],
{
{ "2/13/2021 7:01:46 PM", "Bob" },
{ "13/02/2021 19:01:46", "Jen" },
{ "13.02.2021 19:01:46", "Kate" }
}
),
Culture = [
Bob = "en-US",
Jen = "de-DE",
Kate = "en-GB"
],
// [User] column is mapped to a 'culture'
// then [DateString] is converted
DateTime_FromDynamicCulture = (row as record) as datetime =>
let
author = row[User],
culture = Record.Field( Culture, author),
result = DateTime.FromText( row[DateString], culture)
in
result,
convertDatetimes = Table.AddColumn(
Source, "DateTime_DynamicCulture",
each DateTime_FromDynamicCulture(_),
type datetime
)
in
convertDatetimes
Testing your culture with Culture.Current

Get-Culture
in PowerShell
using Get-Culture

Searching for partial matches of english

See More
- The report.pbix and query are at: ninmonkey/PowerQuery-ExampleReports
- dotnet [Sytem.Globalization] Define formats of dates, currency, decimals, calendars and more.
- Many of the Standard DateTime format strings are used by
DateTime.ToText
andDate.ToText
- Custom
DateTime
format strings
