Tag: 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 and Date.ToText
• Custom DateTime format strings