Using Power Automate to provide Timezone and Local Time to Power BI Dataset and Data Flow

/

Problem:

In Power BI data transformation, we have useful methods such as DateTime.UtsNow() and DateTime.LocalNow() they work great when running from a desktop. But when running as a dataset or dataflow within the Power BI service, these fall apart because The Power BI service does not have the concept of a local timezone. Everything is UTC. The 'regional locale settings' is only useful in controlling the formatting of dates and currency, but not in setting timezone.

Some ideas on how to fix this:

  • There are useful methods such as: DateTimeZone.RemoveZone DateTimeZone.ZoneHours but again they don't work within the Power BI service.

  • Manually add a time duration offset everywhere - but you can't handle day light savings

  • Call an external website Display Last Refreshed Date in Power BI - Excelguru to calculate and get correct local date time. Except the suggested website timeapi has gone offline.

  • This Blog: write a Power Automate flow to help us. Because Power Automate has a fairly robust set of Date Time connectors that handles timezone based on the .NET timezone library.

Make our flow

  1. Create a HTTP Request trigger - change the method to GET

  2. Add Current Time

  3. Save the flow once - this will create the GET URL in the HTTP Request

  4. Add two Convert time zone actions - take the current time (in UTC) and convert to the timezone you want.

5. Add a Compose action and create a JSON with three attributes: UTC, Local, SeattleLocal. Since I'm based in Sydney, my "Local" is Australian East timezone.

6. Add a HTTP Response action and put the Compose output in the Body


If you copy and paste the URL and put that into a browser window, you'll see this:

Wonderful the flow is done. Now we go to Data Flow or Data Transformation in Dataset.

Data Set / Data Flow

Conclusion

Lovely, pretty, local time - even when refreshing dataset or data flow from Power BI

Power Apps API changes - no longer accept Azure Management token

/

There has been recently a major change to the API and authorization that Flow Studio uses to access the Power Platform. As we have just pushed the update to production, we want to take a bit of time to write this blog post - why we are doing this, and how it might affect you.


Flow Studio uses a mixture of APIs available through Azure, Microsoft Graph, and Power Platform to provide a tool that lets us focus on our flows, and make sure they continue running successfully. Since the very beginning in 2018, Flow Studio does this by requesting an Azure authorization token when the user logs in. Since the Power Platform APIs sit on top of the Azure platform, this token was sufficient to access across multiple APIs.

In 2021 there was a big update with Power Automate APIs and now in 2023 there's a second update with Power Apps APIs. As a result, we made the decision to switch to granular, Power Platform specific permissions.

Scope, Permissions

"read flows", "manage flows" are required permissions to access Power Automate - see and update your flows, shared flows or solution flows.

"read activities" allows Flow Studio to read recent Power Automate events - we use this to figure out which flows have been running recently.

"read approvals" and "manage approvals" are related to Power Automate's approval feature. Flow Approvals is not an area we currently have major feature development in, but it is a tab that we have available, so we ask for this permission to maintain the feature.  Since Power Automate approvals v2, some of the data is also available via the default solution DataVerse entities. So if you are keen to do some approvals reporting, you can access them that way.

"Power Apps Service API" is for reading Power Apps and Power Platform connections.

"basic profile" is what most apps request to read the user's email address and display name.

"maintain access" is the "offline" access - since Flow Studio is a single web application we store your token in the browser's web storage temporarily (this is standard MSAL functionality).



Trust and Verified Publisher


Flow Studio is a trusted, verified publisher. This tick is provided by Microsoft, and tells our customers that we aren’t some random new app that’s popped up now asking for your permissions. We have been around since 2018, and have operated under Microsoft publisher guidelines in our use of the APIs and the care we have for our customers. If we do a bad thing, Microsoft knows how to find us.

We are a legal company registered in Australia, our office operates out of Sydney.



Path forward for Flow Studio

So from Flow Studio v1.1.41 or later - users will need to re-login and grant the new set of permissions to continue using Flow Studio.

Flow Studio for Teams and Enterprise is updated as of v0.1.070


Other users might be affected by this

  • If you use older versions of Power Platform Power Shell

  • If you have very old Power Apps connectors created from a long time ago

  • If you are using Office CLI to perform some actions on the Power Platform

You may see specific errors referring to "The received access token has been obtained from the wrong audience or resource".