Categories
Power BI Power Query Quick Tips

Importing DateTimes from Multiple Cultures in Power Query

Using DateTime.FromText with Culture to import dates in text from other formats

Dates and Datetimes use your current 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

Using ConvertTo-Culture and Compare-FormatCulture

Leave a Reply