How to Create Power BI Streaming Datasets Using Microsoft Forms and Power Automate

By: Mason Prewett

What are Power BI Streaming Datasets?

Power BI Streaming Datasets allow developers to create reports and dashboards that refresh in real time as a Power BI dataset is updated. These datasets can be updated by various methods, such as using Power Automate or PowerShell.

This article focuses on updating the dataset using Power Automate. Power BI Streaming Datasets are only available in the Power BI service and not in Power BI Report Server.

At a current Key2 Consulting client, I used this solution to create a feedback form that allowed users to tell us how to improve our organizational SharePoint site. During a large presentation, I provided the form to the audience and let everyone see the results update in real time in a Power BI dashboard as the attendees answered the questions.

This is a great solution for displaying feedback/voting results in real time as the audience is participating. Waiting on the data to refresh or refreshing a page is not required.

This article will describe how to produce a solution similar to this.
 
 

Streaming Datasets Solution Overview

I will be creating a simple voting form for everyone’s favorite color. I will be using a Microsoft Form that will trigger a Power Automate flow that inserts the responses into a Power BI Streaming Dataset.

I will then be using Power BI Desktop to create a report for this dataset, and finally a dashboard to pin the report visual to. My solution will flow through these 0365 apps, as shown in the diagram below.
 

 
It may seem unnecessary to use so many different technologies to accomplish this, but this is the way 0365 apps are designed to work. Each technology plays an independent role in a solution rather than a single technology controlling everything. It took me a while to get used to this shift in the cloud, but it is much better once experiencing the benefits.
 
 

Creating a Power BI Streaming Dataset

I will be using My Workspace to create and publish all of the datasets, reports, and dashboards for this solution.
 

1. Go to your workspace.

 

2. Click the New button and select Streaming dataset.

 

 

3. Select API as the source of your data.

 

 

4. Enter the name of the dataset and what columns it needs to have. It is important to turn on Historic data analysis or errors will occur when trying to connect to this dataset from Power BI Desktop. Click create. Copy the text provided in the textbox above the Historic data analysis and paste it into Notepad, as this may be needed later. If you forget this, you can always see this info by clicking the ellipses by the dataset and clicking API Info.
    
   
    

5. The next screen will provide the API URL that is used to push data to this dataset. We will be using Power Automate to do this so the URL is not needed for our solution.

 

6. Your Streaming Dataset will now be listed in the workspace. I always have to refresh the page to see this.

Creating the Microsoft Form

1. Open Microsoft Forms. Click the 9 squares icon (it’s called a waffle) in the top left of the Power BI service page. The MS Forms link is there.

 

2. Click New Form.

 

3. Give it a Name.

 

4. Click Add New->Choice.

 

5. Add choices, in my case it was colors.

 

6. This is all that is needed for the form. Here is what my form looks like:

 

 

Create the Power Automate Flow

1. Open Power Automate.

 

2. Click the 9 squares icon in the top left of the Power BI service page. The Power Automate link is there.

 

3. Click My Flows on the left navigation.

 

4. Click New Flow->Automated Cloud Flow.

 

5. Give it a name and type “form” in the trigger text box.

 

6. Select When a new response is submitted.

 

 

7. When the new flow loads, select the Microsoft Form that you just created for the Form ID.

 

8. Click New Step and type “form” in the search box.

 

9. Select Get Response Details.

 

10. Select your form again for the Form ID.

 

11. For Response ID, click in the text box and select the Response ID field that shows up on the right under dynamic content.

 

12. Click New Step and type “power bi” in the search box.

 

13. Choose Add rows to a dataset.

 

14. Select your workspace.

 

15. Select your Streaming Data Set for the Dataset field.

 

16. Select RealTimeData for the Table field.

 

17. My dataset field now shows up for the ColorVote that I created in step #4 of Creating a Power BI Streaming Dataset.

a. If the dataset fields are not showing up here and it only shows a single field for Payload, follow these instructions for completing this step. I have seen a lot of inconsistency here, sometimes it shows the field by name and sometimes it shows just the Payload field. I have yet to figure out why and would love to hear from anyone that can explain this.

 

18. Click in this text box and select the question from your form that shows up on the right under dynamic content.

 

19. The flow is now complete. Here is what my flow looks like:

 

 

Create the Power BI Report and Dashboard

1. Open Power BI Desktop.

a. The visuals can actually be created in the Power BI Service by following these steps, but creating a report and then pinning it to a dashboard provides much more flexibility.

 

2. Get Data->Power BI Datasets.

 

3. Search for the streaming dataset in your workspace. Select it and create.

a. If you did not select Historic data analysis for your dataset, you will get an error loading this.

 

4. Select your field as a Stacked Bar Chart.

a. It may help to fill out the Microsoft Form a few times to have some data to view. Click refresh in PBI Desktop after filling out the form to see the results.

 

5. Do any formatting that you want and publish to your workspace.

 

6. Reports do not support Streaming Data, only dashboards do. Your report will display this data as of the last time it was refreshed.

 

7. Go to your workspace and click the New button and select Dashboard.

 

8. Give it a name and then click Create.

 

9. This is all for the dashboard, so just go back to the workspace.

 

10. View the report that you published from step 5.

 

11. Hover over the visual and click the Pin Icon.

 

12. Choose the dashboard that you created in step 8, and click Pin.

 

 

13. Now go view the dashboard and the report will be visible.

 

14. Fill out the form and watch the dashboard updating results in real time.

 
 

 
 

Questions?

Thanks for reading! We hope you found this blog post useful. Feel free to let us know if you have any questions about this article by simply leaving a comment below. We will reply as quickly as we can.
 
 

Keep Your Business Intelligence Knowledge Sharp by Subscribing to our Email List

Get fresh Key2 content around Business Intelligence, Data Warehousing, Analytics, and more delivered right to your inbox!
 


Grow your analytics knowledge.

Leave this field empty if you're human:
 

---

Key2 Consulting is a data warehousing and business intelligence company located in Atlanta, Georgia. We create and deliver custom data warehouse solutions, business intelligence solutions, and custom applications.