Automate Building a Power BI Report from Survey Results

So here’s the scenario… You sent out a Microsoft Form to collect responses from people across your organization. Responses are coming in and now you want to visualize this data in Power BI Report.

Today we will learn how to use Power Automate to automatically update an Excel table every time your form receives a new response. We will then build a Power BI Report with this Excel table as the data source. Now you can have a beautiful Power BI report the refreshes as your form receives new responses.

For this example, I am sending out a Microsoft Form to our global book club to collect data on what types of books and how many books people read over 2020. I want to display this data in Power BI from where I can draw insights about the trends and preferences of our book club and share the responses around. Let get into in!

Requirements

• Office 365 for Business for access to: Microsoft Forms, Excel, OneDrive for Business/SharePoint, and Power Automate
• Power BI Desktop (Free Download) for report authoring
• Power BI: Free for personal viewing (no sharing) or Pro/Premium for sharing with others

Steps

Create your Microsoft Form

1. Head over to Microsoft Forms and login with your Office 365 account. Create your form here.
2. I have created an example form to collect information about the reading habits/preferences of our book club over 2020. Feel free to create any form you like, but for ease of creating a Power BI Report, try to keep you questions to ones their require either numerical or choice inputs.

   

3. A helpful feature I just discovered in Microsoft Forms is branching. This allows you to conditionally display a question, depending on a user’s response to a preceding question.

   

Prepare your Excel Table

1. With your form complete, open Excel to create a table where your responses will be collected. Create column headings corresponding to the fields you created in your form. In addition to these fields, your form will output a Response Id, Submission Time, and Responders’ email. Be sure to add columns for these items if you wish to track them.

   

2. Now let’s turn these columns into a table. A table is required in order to use the Power Automate action we will require later. With your column headings selected, switch to the Insert ribbon and select Table (or use the shortcut Ctrl + T). Be sure to check the box My table has headers.

   

3. The look of your columns should now change. You can rename your table from within the Table Design ribbon.

   

4. Save your Excel file to OneDrive for Business or a SharePoint site. Saving it to your local system will not work with Power Automate.

   

Create a Power Automate Flow

1. Now head over to Power Automate and sign in with your Office 365 account.
2. From the left menu click Create and then select Automated cloud flow. Name your flow, and then search for “Microsoft Forms”. Select When a new response if submitted and the click Create.

   

   

3. Select your name from the Form Id dropdown.

   

4. Add a new step and search for “get response details”, selecting the Get response details action. Select your form name from the Form Id dropdown and then select Response Id from Dynamic content for the Response Id field.

   

5. Add a new step to your flow. Search for “Excel add row” and select Add row into a table. Select either OneDrive for Business or your SharePoint site from the Location dropdown. Next select the appropriate Document Library, File, and Table. You should now see fields appear corresponding to your column headers in your Excel table.

   

6. Fill these fields with the dynamic content generated from your Microsoft Form.

   

7. Click Save to save your flow. If any errors appear, follow the instructions to fix them.
8. Now, as responses to your form are submitted, they will be autofilled in you Excel table. If you have your Excel file open in Excel desktop, make sure to close it before responses starting coming in (Excel Online is fine to leave open).

   

9. OPTIONAL: If your form has a field that can accept multiple values (i.e. genres in my book club form), it may be helpful to create another table to keep track the running total of each value. On another sheet in the same Excel file, create a table with a column listing all possible values for this field. Add another column that uses the formula below to count the number of occurrences of each value. Highlight this table and hit Ctrl + T to turn it into a table.

   

   =COUNTIF(<column_containing_raw_multivalue_data>,"*"&<cell_to_match>&"*")

Create your Power BI Report

1. Time to create a Power BI Report from your data you now have collected in Excel. Open your Excel document. Click on File and then select the Info tab. Click on Copy path.

   

2. Open up your text editor of choice (I’m using trusty old Notepad) and paste the path. Delete ?web=1 from the end of the path and then re-copy the result.

   

3. Open up Power BI Desktop (free download here).
4. Click on the Get Data dropdown and select Web from the list. Do not pick Excel as tempting as it may be.

   

5. Enter the URL you copied in your text editor and click OK.

   

6. Login with your organizational (Office 365) account and click Connect.

   

7. Select your table by name (and your second table if you created one to track multi-value data) and click Load.

   

8. With the data collected from your form loaded into Power BI, you can start creating your report. You can download my report from here if you are looking for a template. You will just need to switch out the data source.

   

9. One you have finished creating your Power BI Report, hit Publish to publish it to the Power BI Service. You can publish this to any workspace you like, including My workspace.

   

10. Open https://app.powerbi.com/ and navigate to the workspace where you saved your report. Select the associated dataset and click on the schedule refresh icon.

    

11. Select Edit Credentials and enter your Office 365 credentials.

    

12. Within the Schedule refresh section, select a time for your data to refresh. Click Apply. Now Power BI will check to see if your data source (Excel table with your Microsoft Form responses) has any new entries and will update your report at the scheduled time.

    

You’re done! Let’s quickly review what we accomplished today: we created a Microsoft Form to collect responses, we created an Excel table to save these responses, we use Power Automate to automatically update our Excel table every time a new response is received, and we created a Power BI Report that uses the Excel table as its data source.

As always, please leave any feedback or questions in the comment section and I will get back to you.