Bulk update data type DateTime to DateTimeZone with LocalNow Applied

[Anonymous]

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).

[slorin]

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