Forum Discussion

Aurora-BI's avatar
Aurora-BI
Icon for Helper I rankHelper I
2 years ago
Solved

How to change UST to EST in Direct Query

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.

Need Help
  • marcelsmaglhaes's avatar
    marcelsmaglhaes
    2 years ago

    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

2 Replies

  • marcelsmaglhaes's avatar
    marcelsmaglhaes
    Icon for Super User rankSuper User
    2 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.