Forum Discussion

deerzzxdell's avatar
deerzzxdell
Frequent Visitor
2 years ago

Change in dataflow localize data after auto refresh in BI service

Hello, Community

I need your help with Power BI on desktop. I have a BI file with data source from dataflow which contain data from power automate system and Dataverse. 

Here is what my datatable originally look like when I first import dataflow into BI

 

but the date on all columns (requested on, started on and completed on) are incorrect. So I add + 6 hrs to my column and It become like this which is what I prefer

 

After this BI got to workspace (BI service). Until this step everything works fine. But after the BI got automatically refresh from refresh schedule, time format on all columns got change back to image 1 again. I try to re-publish but when it got auto refresh the localize data of time always change back to original. 

 

I would like to know the cause and if possible, a way to solve this problem.

 

Thank you. 

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi deerzzxdell ,

    Don't worry, I'll do my best to come up with an effective solution for you!
    According to your description, I think the problem should be the time inconsistency between the local environment time and the power.
    I think the problem is that the time between the local environment and the cloud server of power BI Service is inconsistent. May I ask the time zone of the datavarse where you get the data and the time zone of the local environment of your computer are the same, if they are not the same, then there will be a situation that the same type of time/date data is not displayed consistently in power BI Desktop (local environment) and power BI Service (cloud) (because of different time zones). If not, the same time/date type of data will be displayed inconsistently in power BI Desktop (local environment) and power BI Service (cloud) (because of different time zones)

    Please check whether the local time (desktop)zone and servcie time zone are the same.

    You can try my action below。
    Create a new calculated column:

    NewTime =
'Table'[your time]+6/24

    The 6/24 here is because the unit is days, so it's divided by 24 hours.


    I added 6 hours to the calculated time.
    In my case, the service and the local timezone are the same,
    If your situation is really due to a six hour difference between the two time zones, then this should work!

    Consistent display on service (no time change occurs)

    Could you please provide detailed data, if so, this will be able to help immensely in solving the problem.
    I will offer my own understanding below.

    I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

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

    • deerzzxdell's avatar
      deerzzxdell
      Frequent Visitor
      2 years ago

      hello! thank you for the reply.

      To answer your question. From my understanding data will be retrieve in form of UTC time, then ideally the localize should be the one to manage the time depends on the client time zone (mine is UTC+7). My environment (dataverse) is set to asia time zone (UTC+7). 

       

      Right now, in BI desktop data from DF after time conversion from localize is UTC+7. But went it got refresh/auto updates it goes back to UTC time without the conversion. So if I plus 6 or 7 to createon column within BI desktop, data will be incorrect.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi,deerzzxdell .According to your subsequent description, you are saying that if you force the use of time operations for time conversion will result in incorrect data due to changing time zones when refreshing on service, indicating that it is not feasible to modify the time data on power BI Desktop. I think it may be caused by the mismatch of the time zone of the data source, because at each refresh, the data is re-fetched from the data source, and the taken time data is modified to the time zone where the data source is located. Dataverse usually stores the data in UTC time, so you may need to add some logic to convert the time zones in Dataflow before importing the data into Power BI.
        Regarding the issue of how to change the timezone of a dataflow uploaded to Dataverse.
        I found a similar solution for you, you can refer to it to see if it can solve your problems.
        URL: Change in dataflow localize data after auto refres... - Microsoft Fabric Community
        Because the time zone is not supported by the power BI service now.
        Or you can also modify the data source from the dataverse by creating a new column in the dataflow, and then adding 6 hours to the new time column, so that even after the refresh, the data you get will be the data after the +6 hour.
        I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
        Best Regards,
        Carson Jian,
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.