If you have read my post, How to Automate Power BI Work, Power BI REST API, you already know how to use REST APIs, and there are new Fabric REST APIs. But you probably wonder if you can automate the code, too. For example, if you want somebody else to create workspaces who may not even have the rights to do that.
There are solutions for that, and I want to share one of them here. The answer is Power Automate Flows.
The Use Case
Before going into technical details, I would like to present a use case. Imagine you have colleagues that sometimes need a workspace, and sometimes it should be a Premium/Fabric workspace. The issue is that you have strict tenant governance, and not everyone can create one. How could you do that? Of course, you can use your PowerShell scripts, and every time someone requests a workspace, you run them. But maybe there is a better way? Yes, you can create a Microsoft Form that a colleague will fill out with needed information. After that, Power Automate Flow will run the scripts automatically and even write a confirmation email.
The Form
Form’s creation is intuitive, so I will show you how colleagues will see it:
So, from this form, I’ll get:
- workspace name
- if capacity is needed
- respondent’s name and email – this is very important because I need to know who needs access to the workspace and who to contact if I need more information or to send a notification that everything is done. This is the form’s setting that needs to be enabled.
The Flow
What is Power Automate Flow?
Microsoft says it is a comprehensive, end-to-end cloud automation platform powered by low code and AI. You can automate many things, but in this case, I’ll use answers from the form, call some Power BI REST APIs, and send the email when it’s done.
When you sign in, you can look at flow examples:
Each of these flows consists of steps, for example:
Here, you have the first step – the flow is triggered when you get an email in your inbox, and the second step is forwarding it to a Teams channel or chat.
You can read about pricing here.
Current Flow Setup
Before you start, you must choose the Environment where you will create flows. If you don’t have company guidelines, you can use the default environment, but if you do, you need to ask what you must use. For simplicity, we use the default here.
To create a Power Automate flow, you must sign in and decide how it will be triggered. In this case, we need it triggered when the form is filled out. So, you need to choose Automated cloud flow:
Then you need to write a Flow name and choose the trigger, in this case, When a new response is submitted – Microsoft Forms.
As you can see, you need to choose a trigger, depending on the tools you use. Here, I’m using MS Forms.
After pressing the Create button, you will choose the actions for your solution. So, firstly, you need to pick the form as your Form id; in this case, it is Workspace Creation Request.
Now, you need to add new steps called actions in Power Automate.
What’s next? You need:
- get responses from the form
- create a workspace – you should think about naming conventions,
- if capacity is requested, you need to assign it; if not – not, if the answer is maybe – you need to write an email and ask what the solution is about and see if the capacity is needed,
- after everything is done, send an email stating that the request has been fulfilled.
Get Form's Responses
You need to find an action that gets the responses from the form. The easiest way to do that is to find all Power Automate’s actions for MS Form. To do that, we need to press New step and search for “form”:
After choosing the action Get response details, you need to select the form name
When you press on the blank field where the Response Id is, a new window will appear, suggesting that you choose content from the previous actions or triggers.
So, you can select the Response Id from the trigger. And this is how you get the responses from the form.
Create Workspace
For workspace creation, you can use the Create Group API. Since I already wrote about how to use REST APIs in my post, I’ll focus on the flow here.
To use APIs in Power Automate, you can use HTTP action:
In this action, you need to fill out the necessary details:
Everything can be manually filled out except for the workspace name you get from the form’s responses. So, when you provide a body, between double quotes, you must select Workspace Name from the Get response details action. Then, the body part will look like this:
I’m using a service principal (in short, SPN) for authentication with the scopes required for running the API. The only thing you need to provide additionally is the Audience: https://analysis.windows.net/powerbi/api (highlighted in blue).
Of course, there is a way to make it better. For example:
- Use variables to store tenant ID, client ID, and other text values, especially if you use them in other actions,
- Keep the SPN’s secret in the Azure Key Vault (maybe now it’s called Entra Key Vault) and use the additional step, Get secret. This way, you don’t need to hardcode this sensitive info.
I won’t show you how to do this in this post since there is already quite a lot of new info. for you here.
You could think that you’re finished with creating a workspace, but… You created a workspace with the SPN, meaning only it can access it. So, you need to add a requester as an admin to this workspace. Only then he/she/they can access it.
Thus, you need additional HTTP action using the Add Group User API.
Add User as a Workspace Admin
First, you can copy-paste the previous HTTP action not to fill out all the details again. You can do that by pressing 3 dots and selecting Copy to my clipboard:
Then, for the new step, you can choose My clipboard and select the action:
Now, you need to change the URI and Body like this:
You’ll encounter a problem: you don’t have a groupId from the previous HTTP request. You see only the Body, Headers and Status code. To fix this, you’ll need an additional step:
- Parse JSON will get the details from the HTTP action’s Body where the groupId (i.e., workspace ID) is.
First, how do you add a step between the other two? By pressing the plus sign and Add an action:
Then, search for the Parse JSON and fill out the details:
In the Content part, you’ll add Body from the previous HTTP action, but what about Schema? This is where you need to run the flow and see what output the first HTTP action gives you. So, you need to delete the Parse JSON action, save the flow and run it. Since this is the first time, you must do it manually by filling out the form.
After you fill out the form, you need to go to the flow overview and press on the date and time in the run history:
If you don’t see anything, you can refresh it.
After pressing the run in the history, you need to press on the HTTP action and scroll to the Body part. This is the place where you copy the JSON part:
Here, you can notice that workspace ID is there as id.
Now, you need to exit this page, press the Edit button at the top left, and again create a Parse JSON action and paste the code you copied in the window that appears after pressing Generate from the sample:
Assign Capacity to a Workspace
The next step is to assign a capacity if required. So, the plan is:
- to figure out if you need to assign a capacity,
- if yes, assign it using Groups AssignToCapacity.
First, you need to use the 2nd answer from the form “Do you need a Premium/Fabric workspace?”. Since there are 3 possible answers, you need to use Condition action.
So, you must define what happens when the answer is Yes, No or Maybe. It can look like this:
Where you check if a value from Do you need a Premium/Fabric workspace? is Yes, you need another HTTP action for assigning capacity; if it is different, you need another Condition action.
For assigning a capacity, you will need the workspace and capacity IDs:
That’s it! This is an excellent place to save your flow and try it out. Again, you can go to the form, write a workspace name and answer that you need a capacity. After the flow finishes running, you should see that it failed.
To troubleshoot what happened, you need to press on the date in the running history. When you’ll see that the HTTP 3 action failed because of “PowerBINotAuthorizedException”.
As always, it’s not the best error message. This means that you can’t assign a capacity when using SPN (at least, this is true at the moment I’m writing this post).
To fix this you must use another action where you’ll use your account for authentication. The account you use must be the capacity admin or contributor.
Again, you must edit the flow, delete the HTTP 3 action (press 3 dots on the action and select Delete) and use Invoke an HTTP request – HTTP with MS Entra ID (preauthorized):
If you press the 3 dots on this action, you’ll see that it uses your account.
Send Email
You should email the colleague who asked for the workspace, stating everything was done. For this, you can use Send an email (V2) – Office 365 Outlook:
You need to set up 3 emails:
- when a workspace is created and capacity is assigned,
here, Responders’ Email is used in the To field. - when a workspace is created (capacity isn’t required),
- when a workspace is created, and the responder doesn’t know if he/she/they needs a capacity or not.
This is what the end of the flow looks like:
Finishing Notes
The whole flow looks like this:
I filled out the form where I asked to create a test workspace and assign a capacity. Then the flow ran, and I saw the workspace in my Power BI Service:
Moreover, I got an email:
This is how you can automate Power BI & Fabric REST APIs. Of course, there are different ways to do it, and you could make some customisations, e.g.,
- change flow’s action names to better understand what the step (trigger or action) does,
- customise emails that it won’t be sent from your account but, e.g., from the team’s account,
- try other actions for the APIs, etc.
Moreover, remember that when you test the flow, you’ll need to delete all the workspaces afterwards. And remember that not all of them can be seen in the Power BI Service since only the SPN can access them. You can delete them by using the Delete Group API.