Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Bulk update data type DateTime to DateTimeZone with LocalNow Applied

Hi all,

I've found a blog historically that allows you to bulk update all datetime columns to date only using the below code:

 

#”Convert Date” = Table.TransformColumnTypes(#”Changed Type”, List.Transform(Table.ColumnsOfType( #”Changed Type”, {type nullable datetime}),each {_, type date}))

 

 

I'm wanting to know if there's a way to perform tghe following in bulk with Power Query:

1) Identify all columns of a specific data type (date, datetime, datetimezone)

2) transform all the identified columns into a different data type (specifically datetimezone)

3) apply bulk update of DateTime.LocalNow (or another formula if there is as better option) to apply local timezone to the selected columns.

 

Context: I'm connecting to a data source that is storing in UTC but we're working in +930 so I need to move all columns to Australian Central time. This is happening on all tables, so I need code that can dynamically identify any number of columns in a table and apply the steps (whether this is a step-by-step using power query, or a function is not a concern).

Need Help

1 Reply

  • slorin's avatar
    slorin
    Super User
    3 years ago

    Hi

    Try

    = Table.ReplaceValue(
       PreviousStep,
       null,
       null,
       (x,y,z)=>
           if x is date or x is datetime or x is datetimezone 
           then DateTimeZone.ToLocal(DateTimeZone.From(x)) 
           else x,
       Table.ColumnNames(PreviousStep)
    )

     

    Stéphane