I realize that this tile might be a bit confusing. That’s why we are going to introduce three most important concepts before we move forward. Let’s start with Dataflows Gen2.
What is Dataflows Gen 2?
You probably are using Power BI Dataflows already, so what is Dataflows Gen2? This is very similar solution, redesigned for the purpose of Microsoft Fabric. There are some minor differences between standard and Gen 2 solutions, but the most important one is that Dataflows Gen 2 allows us to decide, where we would like to load the data. So, instead of having the data exposed through Dataflow Entities, we can decide if we would like to load the data to Azure Data Explorer, Fabric Lakehouse, Azure SQL DB or Fabric Warehouse.
Power BI REST APIs
This definitely deserves a separate article, and we will not go to deep into technicalities. For the need of this article let’s say that API stand for Application Programming Interface, and RESTful API is a set of rules for interacting with web services. We are all using in Power BI Service without even realizing it, with help of User Interface. However, Power BI provides us with REST API documentation, that we can use to interact with Power BI programmatically.
It is important to mention, that you don’t need Tenant Admin access to interact with APIs we are going to cover today.
No-Code solutions
This one is pretty much self-explanatory. The only thing that could be clarified here is why we are talking about No-Code solution, if in the section about REST APIs it is clearly stated, that they allow us to interact with web services programmatically. This is also not trivial process, as we need to be familiar with at least one programming language, take care of requesting access token for authentication purposes, parse the response, handle errors, etc. So where is this “No-Code” part here? Microsoft documentation allows us to “try” specific API and takes care of all the painful points I just mentioned. So, if you were afraid that this post will be too technical – don’t be. I will try to make it as simple as possible.
Problem statement
We covered some of the definitions, but what is exactly the problem we are trying to solve here? Great question 🙂
Dataflows Gen 2, just as Microsoft Fabric itself, is still in Preview Mode. This means that not all the features are already delivered, and we must live with all the inconveniences standing in our way. It may happen, that refresh of Dataflow Gen 2 is stuck, or simply takes too long and we would like to stop it. And here is the problem we are trying to solve – we can’t stop/cancel the refresh today. This option is simply not available yet and, surprisingly, it was not available for standard Dataflows for pretty significant amount of time as well. Not sure when this is going to be fixed, but in the meantime, we can use alternative solution, and we will use this opportunity to slowly enter REST API area.
Solution
There are two APIs that we need to run to complete this task. The final one is Cancel Dataflow Transaction, follow the link to check MS documentation.
Here we can see how to build URL to call the API, and the option to “Try It”, which we are going to use. Before we can do it, we must prepare two parameters to provide in URL. These are groupId and transactionId. First one is workspace ID, second one is the ID of refresh request, that is assigned automatically whenever any type of refresh is started. Workspace ID we can quickly get from address bar of your web browser. Go to your workspace, and copy the string that comes after “/groups/”:
This was easy, transactionId is slightly trickier to get. Technically speaking we could get it from Refresh History panel, but it’s not available during the refresh. Here comes another API we need to run – Get Dataflow Transactions. You can follow the link for more info.
This API also require two parameters, one we already have – groupId. Second one – dataflowId we could normally get from web url as well, but… we can’t enter the dataflow during the refresh 🙂 Luckily, this information is available within Refresh History, even during the refresh:
It’s time to make first API call using available parameters. Click “Try It” and you will be asked to login. Use your organizational account. When you are signed up, you will see following window:
- Provide dataflowId here.
- Provide groupId here.
- Below there is a Request Preview, where we can see Bearer Token needed for authentication during API Call.
After we call API, we get the list of transactions for requested Dataflow. Result is provided in JSON format, where at the very top we see current refresh in “InProgress” status:
We see type of the refresh as well, but of course the most important is the id. We need to copy it and paste in Cancel Dataflow Transaction API. Go back to that API and use “Try It” option again. Paste groupId and transactionId, then click “Run”. You will receive short response:
If you quickly go back to Get Dataflow Transactions API, you will see the latest transaction being canceled by You, but after some time, for whatever reason, status will change to “Success”. So, don’t worry if you see something weird here. The most important thing is that Dataflow Refresh was stopped, and you can continue with adjustments.
Conclusion
With REST API we enter more technical side of Power BI, however, thanks to “Try It” option, we can play with it without writing a single line of code. This was only one example, buf if you go through the list of available APIs, I am pretty sure you will find many of them useful. If you have any questions, let me know in the comments below.
Thank you for reading, and till next time 🙂
