Convert column from etc to local timezone in Get Data Process

[Tango2310]

Hi,

I am performing the get data process from Dataverse and wondering how to convert to UTC+10?

I was thinking it should be done at the ingestion level without having to create a measue.

Ive tried change type then Local or Timezone but neither of these seem to work.

My current query is

= Table.SelectRows(#"Removed Other Columns", each ([crbb0_quotestage] = 252750008))

Column is

Thanks in advance

[Anonymous]

Hi @Tango2310 

You may not be able to do this in DirectQuery mode.

In DirectQuery mode, the data remains in the source system, and Power BI sends queries to the source system to retrieve the data.

This means that, ideally, complex transformations should be handled at the source.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @Tango2310 

To convert the datetime column to UTC+10 during data ingestion, you can add a step in the Power Query to adjust the time. You can try:

Add a custom column. You can create a new column and add 10 hours to the existing datetime column.

Use the DateTime.AddZone function. This function helps you convert the date time to a specific time zone.

let
    Source = ...,
    RemovedOtherColumns = Table.SelectRows(Source, each ([crbb0_quotestage] = 252750008)),
    AddedCustom = Table.AddColumn(RemovedOtherColumns, "UTC+10", each DateTime.AddZone([utit_quotefinaliseddate], 10))
in
    AddedCustom

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Tango2310]

Thanks @Anonymous Will this work in DirectQuery mode?

[Tango2310]

This is my latest attempt 

= Table.TransformColumnTypes(#"Filtered Rows",{{"utit_quotefinaliseddate", type DateTime.AddZone(["utit_quotefinaliseddate"],10)}}))