Tips on fixing Power BI dynamic value

In Power BI we have the ability to use dynamic value within a text block, this allows us to create dynamic paragraphs with data that automatically update.

The problem is, the way we specify the dynamic value is via Power BI Q&A - this can be a giant hit or miss, when it miss - it’s a frustrating experience.

Tip 1 - just use a measure (with a unique name)

The first tip then is to create our own measure with a unique name so that it can’t be confused with anything else. Within our measure, we can use DAX and write specifically what we want the dynamic value to be.

We can add a Card visual to inspect what the value of our measure is. This makes debugging the measure quite simple.

Tip 2 - use the Q&A setup, particularly, to set up the synonyms

I encountered a new problem today, no matter what I tried - Power BI did not recognize the measure (or even the new table that I was looking for). I went into Q&A setup and added simpler, unique names to the measure as well as the table via the Synonyms dialog within Q&A setup, but Power BI was just unable to recognize it.

Tip 3 - too many entities

I tried to export the Linguistic schema (beware this is a YAML file) to see what’s wrong with the model, and that’s when I got this new error message.

So I went back to the Synonyms dialog and unchecked “Include in Q&A” on several entity tables that did not need to be included, and as soon as I did this, dynamic value suddenly can find my measure by name, and link up what I want to render correctly.

I wanted to write this down as it may help others. This ate 2 hours of time for two people, and we created a bunch of rubbish name entities and measure names that I now need to go clean up…

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

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

View fullsize

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.

View fullsize

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:

View fullsize

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

Data Set / Data Flow

View fullsize

View fullsize

Conclusion

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

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.

On using Microsoft Flow as a pre-ETL step for Power BI

Photo by WeRoad on Unsplash

A topic I’ve presented a few times to an Power BI crew is the concept that Microsoft Flow makes a great pre-ETL step for Power BI.

I talked to many attendees at Difinity Power BI Conference Auckland about this. It’s probably a time to write this up to summarize my thoughts.

There’s just something nice about having a product called Flow gathering data into your lakes.

Power BI

Power BI - Power Query and DAX is extremely good at crunching numbers.

Flow has variables, branching and loop logic, but they are slow for number crunching. Loop limitations like 5k rows or total number of action steps are also very limiting.

Don’t use Flow to crunch numbers.

Flow

Power BI connects to about 70 sources - Flow connects to about 250 connections.

Flow’s HTTP action and Custom Connector framework is better at gathering data. Automatic paging, more authentication options, retry policy, async HTTP request.

Flow can gather data on Trigger, copy them all to a simple location (e.g. SharePoint library or Azure Blog Storage) for Power BI to start refresh. So data processing can be done in a secure way.

Flow can call Power BI API to start a refresh. Turning the ETL into a trigger-push system. We get the best of both worlds - Flow to gather data, and Power BI to crunch them.

Power BI Scheduled Refresh with Flow

1. Schedule Refresh requires Flow to call Power BI API with a HTTP Request. But until very recently – this requires a delegate permission (so Flow has to call Power BI as a user) to perform delegate permission call requires a Custom Connector – which is what this blog post from Konstantinos goes into detail about creating.
   
   https://medium.com/@Konstantinos_Ioannou/refresh-powerbi-dataset-with-microsoft-flow-73836c727c33
   
   

2. In February, Power BI has started releasing application permission to call Power BI API – this work is still in preview and requires quite a lot of set up, it also requires the administrator to approve the permission. App-only read/write everything is a pretty high level permission, non-admin can't grant this.
   
   https://powerbi.microsoft.com/en-us/blog/use-power-bi-api-with-service-principal-preview/
   
   pros: easier to call with Service Identity, and HTTP request
   cons: may be impossible to get your admin to approve a new AAD APP permission
   

3. The April roadmap says Refresh is an action coming to Flow’s Power BI Connector – this will use delegate permissions and would be much simpler to use. So my thinking is that wait for this to drop after April
   
   https://docs.microsoft.com/en-us/business-applications-release-notes/April19/microsoft-flow/improved-power-bi-connector