How to change UST to EST in Direct Query

[Aurora-BI]

Hi all, 

I have a dataverse table that has it's created on field in EST. When I conenct to the data directly in Power BI desktop, the dates are in UTC. Does anyone know how I can change this?

Any help is greatly appreciated.

[Anonymous]

Hi @Aurora-BI ,

Based on your problems, here are my answers.

I create a table as you mentioned.

Then I create a new Column to change the UTC to EST. Here is the DAX code.

Column =
FORMAT ( 'Table'[UTC], "MM/dd/YYYY" ) & "  "
    & REPLACE ( FORMAT ( 'Table'[UTC], "HH:mm:ss" ), 1, 2, HOUR ( 'Table'[UTC] ) - 5 )

Finally you will get what you want.

Best Regards

Yilong Zhou

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

[marcelsmaglhaes · Accepted solution]

Hey @Aurora-BI 

To adjust the time zone of the date and time fields in your Dataverse table when connecting directly to Power BI Desktop, you can use the option Advanced Editor on Power Query Editor to edit the M scritp:

• Click on the "Advanced Editor" option in the Power Query toolbar to open the Advanced Editor window.

• After the last step, add a new step to transform the time zone directly using M code. Here's the code to add:

           AdjustedTable = Table.TransformColumns(YourLastStepName, {"Created On", each _ - #duration(0, 5, 0, 0)}) 

• Apply Changes: After adding the new step, click on "Done" to close the Advanced Editor.

• Review and Test: Review your data to ensure that the time zone adjustment has been applied correctly. You can also test your report to verify that the time zone conversion behaves as expected.

M Script Example
let
Source = ..., // Your original data source steps

AdjustedTable = Table.TransformColumns(YourLastStepName, {"Created On", each _ - #duration(0, 5, 0, 0)})
in
AdjustedTable