Forum Discussion

Tango2310's avatar
Tango2310
Helper I
2 years ago

Convert column from etc to local timezone in Get Data Process

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

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      Tango2310
      Helper I
      2 years ago

      Thanks Anonymous Will this work in DirectQuery mode?

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.

  • Tango2310's avatar
    Tango2310
    Helper I
    2 years ago

    This is my latest attempt 

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