field notes using Power Automate with Power BI

/

I have been working at a client learning, testing and building Power BI reports, datasets, dataflows, datamart and dashboards for the last 8 months. With a fairly healthy dose of Power Automate thrown in. I wanted to take a short breather and write down some of the learnings so far. I also would really like to hear your feedback regarding what your Power BI best practices are.

I will be presenting my experiences and examples in the Australian Digital Workplace Conference next week in Melbourne. I hope I’ll be seeing you there.

The system components

  • Call the API of a custom business application to retrieve project & product data, the system uses a NoSQL database, so we are pulling out pages of JSON via Power Automate.

  • At the moment, we store these JSON files into a document library in SharePoint.

  • Use Power BI dataflow to process and merge these JSON data into a staging table.

  • Use Power BI dataflow to transform data from the staging dataflow to actually do work.

  • Reports use the staging dataflow, and reference it if additional local transformations are needed.

  • Fancy visuals with Deneb and HTML content visuals.

Calling APIs - we are using HTTP request and calculating nonce and api-key within Power Automate - this wasn’t something Power BI can call directly without some middleware. In particular, we are interested in a subset of projects from our dataset - so every evening, Power Automate calls Power BI Dataset Query, fetch a list of project codes and make API calls. It also checks if there’s been a change from the version stored in SharePoint and skips writing if the JSON has not been modified. (Unfortunately, we don’t have a last modified metadata from source).

We put JSON data into a SharePoint library and store them by months - this is because the business has a monthly reporting cycle, and we wanted data captured against last month vs this month. An alternative would be to use Azure Blob Storage for this staging area, because once we reached several hundred JSON files, SharePoint often throws too-busy error to Power BI during data refresh.

On re-use, we tried a myriad of methods, and we found using two dataflows to work the best. The first dataflow provides raw json content per file from SharePoint. This dataflow is configured for incremental refresh, so if the JSON isn’t updated, this doesn’t need to refresh in the dataflow (this solves our file too busy problem). Datamart can’t easily be used within the ETL of another dataflow (it’s more suitable for direct query).

Our second dataflow is where we do the transformation of the json data navigate the JSON structure and pull out records, lists accordingly.

We try to have very light local transformation or modification within the Power BI report. Instead, as much as possible, we want to run the transform within the dataflow.

This is a high-level overview blog post and there’s quite a bit more (smaller, bite sized) notes I want to write down in time, but this will do for now, and we’ll leave the rest for a future discussion.

Other design decisions, notes and thoughts

  • What Power Automate brings to the table.

  • What Power BI Dataflows brings to the table.

  • Notes on using DAX and Power Query M

  • Using Dataverse

  • Considerations with using SharePoint or Azure Blob Storage

  • Licensing costs

“Soon” Azure Data Factory and Microsoft Fabric offerings

Turning a new chapter - the MVP Alumni post

/

A few months ago I wrote that I’m turning a page, I’ve found motivation to move forward again, and have begin to actively change my destiny (instead of being passive and waiting for it to happen to me). I wanted to give an update and write as I feel I’m moving onwards onto a new chapter.

  • Some things changed a lot

  • Some things didn’t change much

  • Some things I must give my full attention to

  • Some things I must let go

Some things changed a lot

At the end of last year, I met someone through church meetings that was a friend, who became a close friend, then a fiancé and now my wife. Sometimes, the right person appears suddenly in your life when you are at your lowest, and she didn’t ask questions and just picks you up. I’m grateful to recognize that God is giving me a second chance and I wanted to take that path to move forward with purpose.

  • Moved out from previous family home with a big renovation project completed.

  • Resumed part time work that quickly became full time.

    • Exercising a lot of my Power Automate and Power BI muscles

  • Lost weight, became more healthy

    • and working on gaining more energy via fixing my sleep

  • Married, with two more children, so now a total of four.

    • also add two cats.

  • Moved into a bigger house and the various organization and fixing challenges

  • I have not been very active in the communities (covid, and later as I got swamped with full time work), so alas, I can’t maintain my Microsoft MVP status. After 7 years from 2017 to 2023, I must pass on the spot to someone else.

  • I realized I can do lots of DIY projects and I love doing them. It’s refreshing to realize that I can build physical things and not just digital things.

  • I also realized I really quite like the tranquility of going camping.

Some things didn’t change much

  • I still love to chat, tell people how to build things

  • I am speaking at the Digital Workplace Conference in August 2-3. Presenting topics related to Power Automate and Power BI dataflow.

  • I will still find time to blog, make YouTube content

  • I still love working on Flow Studio and helping customers.

Some things I must give my full attention to

  • Flow Studio, my startup, must continue to grow and I’m working on multiple aspects of our offerings relating to Power Platform monitoring and governance.

  • Grow my YouTube channel, I think between my YouTube and my blog is where I’ll continue to reach more of you.

    • Twitter where we met before is becoming such a strange place

    • Reddit is weird too

    • Some of the community events are no longer around after Covid

  • My health, which is improving, so I’ll still be here for a long time to writing things.

  • My family, which requires me to be around for a long time

Some things I must let go

A younger me thought that hey I’ll build cool things and tell people how to build them. You’ll find this person on this very blog, over the span of twenty years.
Over two decades, I had fun with you all. I built a career, which let me build what I wanted and be on the cutting (yes I bleed a lot so you don’t have to bleed as much) edge.
I wrote, spoke, presented and taught on these topics. I received and maintained the MVP award status.

But I must tell you, when everything comes crashing down, none of this mattered. Career, wealth, fame, travels, future.

Consider what is closest and important to you. Do more of that. Spend time with people that you should have - don’t regret it afterwards when it is too late to do anything different.

I am thankful for the MVP program for recognizing my contributions to the community, and for the many beautiful friendships I’ve made along the way. I’m grateful that I have a clear path to move on, and I hope you all are still here to cheer me on.

So it feels so good to put these things to words. I’m fourty-four. I feel like I’ve climbed out of a valley. There’s a new horizon in sight, I have a second chance.

I’m always here, online and physically in person around Australia.
I’m sure we’ll meet again soon.

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".





Turning a new page

/

Wanted to write again, and let everyone know what I’ve been up to. I ended up taking a break through most of 2021 and 2022 simply resting, recovering, and doing light work from home.

2023 resumed with a big bang, I found motivation and drive to dive back into the many projects I’ve temporarily shelved in the last two years. I’ve also became pretty handy with a bunch of home DIY projects. It was a big change to the old me that only knew how to do digital projects but not physical projects. Perhaps more on that in a future post.

Flow Studio

We’ve had several Flow Studio fixes in the last two months

  • there was an API pagination fix since the API no longer accepts 250 records at once and restricts us to only 50. (That means more pages and API call takes longer)

  • API auth fix relating to Power Apps is in-progress.

  • There’s a second API skip/continuation token fix.

  • We’ve also tweaked the way trail is applied when anyone wants to try Flow Studio pro - you can sign up and trial will be available for two weeks - you can cancel the subscription before the trial end date to avoid being charged, if Flow Studio isn’t suitable for you.

Clarity / Flow Studio for Teams and Enterprise

We’ve had renewed interest in Flow Studio for Teams and Enterprise (Clarity) through the last few years.

  • Flow Studio for Teams will be tweaked to focus on monitoring critical flows and alerting users when their business critical flows fail. This will be priced simply and does not offer governance capabilities.

  • Flow Studio for Enterprise will be focused on the turnkey Power Platform Governance story, adding new features to scan more areas of the Power Platform, and integrate with CoE starter kit.

  • So far this year, we’ve added BYO Azure Storage. There’s been a lot of fixes to API breakages in Power Apps area this year.

Contract Work

I started a regular part time contract work in Sydney CBD, so if you are local, hit me up for a coffee.

  • I’m working with a lot of Power BI reports

  • There’s a lot of Power Automate doing the heavy lifting as well.

  • We are also talking about adding some Power Apps visuals to allow executive comments to be collected during a report presentation.

Community

Several of the meetups, conferences and events that I used to participate in are becoming active again. I hope to see more of the community not just virtually, but physically as well. I hope to be able to grab a coffee with you soon.