I rarely use Power BI Streaming Datasets, but when I do........ I have to say, they are awesome for meetings, workshops and trainings!
In this post I’m going to talk about feeding your Power BI Dashboard with live data from Microsoft Forms (with a little help from Power Automate).
When you have your final solution in place, you will be able to see your Power BI Dashboard refresh automatically every time you submit a Form, real time data streaming folks, exciting!
Key words: Power BI Streaming Datasets, Power Automate, Microsoft Forms, Real Time Data
1) First, we will create our form. To do this open Microsoft Forms and create the form you want to present to the users, and that will lately feed your dashboard (choose your questions well 😉)
Some help on how to create forms:
I have added 4 questions to my form, you can add more or less depending on your needs:
2) The second step is to create a streaming dataset. To do that open the Power BI Service webpage, select the Workspace you want to create your streaming dataset on and click on New > Streaming Dataset:
Next, choose API:
3) We will now pass our form questions to our streaming dataset. Each question will have a value name (we will use the names we defined before, but you can use any names as long as you map them to the correct fields later).
Don’t forget to turn on Historic data analysis!
Once you’re finished click on Create. You should now be able to see your new dataset:
4) To glue everything together we need Power Automate.
We will be building a Flow that will serve as the connection between the Form and Power BI so that the data is refreshed instantly.
Open Power Automate and on the Templates tab search for Power BI Templates. You will find a template called “Add rows to dataset in Power BI after approval on Microsoft Form submission”, select that one!
This is the Flow that will be automatically created for you:
In the first two steps I just selected my recently created form (called “Let’s get to know each other!”):
Next, I will delete all the steps bellow the step Get response details. You will be left with only the two steps listed in the image above.
We will add a step to pass our Form answers to our Power BI Streaming dataset. Click on Add New Step and select Add Rows to a dataset (preview):
After you’ve selected the Workspace, Dataset and the Table, you will see the fields you created before in the dataset. Once you click on any field you will see a popup window with the options you can choose for that field. This is were you do the mapping of your dataset fields to your Form fields:
This is the mapping of my solution:
Test and Save your Flow. (To test your Flow you need to submit a Form):
5) Next, we will be creating our report and dashboard so that we see our real time data streaming into action. On the dataset options click on Create Report:
I have added a few fields to my report:
After finishing my report, I pinned all the visuals into a new Dashboard:
My final dashboard looked like this (not the most beautiful dashboard I developed I have to confess…):
6) Lastly, we are going to embed our Form in our dashboard. To do that Share, select the code snippet symbol and copy the code:
7) Go back to your dashboard in Power BI and click on Add Tile:
In the popup select Web Content:
Past the piece of code from Forms in the Embed code and click Apply:
Now we have our form embedded in our dashboard!! Pretty cool no??
Now, the only thing you need to do is start submitting Forms and see some magic happen!
Cheers!
Mara