post icon

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!



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.
    Book club 2020 form
  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.
    Excel headings
  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.
    Excel Table
  3. The look of your columns should now change. You can rename your table from within the Table Design ribbon.
    Table created
  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.
    Save as

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.
    Automated cloud flow
    Trigger from when form is submitted
  3. Select your name from the Form Id dropdown.
    Trigger action
  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.
    Get response details action
  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.
    Locate Excel file
  6. Fill these fields with the dynamic content generated from your Microsoft Form.
    Fill in dynamic fields
  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).
    Responses in Excel table
  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.
    Count per genre

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.
    Get Excel URL
  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.
    Web data source
  5. Enter the URL you copied in your text editor and click OK.
    Paste in link
  6. Login with your organizational (Office 365) account and click Connect.
    Auth with organizational account
  7. Select your table by name (and your second table if you created one to track multi-value data) and click Load.
    Load in data Power BI
  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.
    Completed Power BI report
  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.
    Publish to service from Power BI Desktop
  10. Open and navigate to the workspace where you saved your report. Select the associated dataset and click on the schedule refresh icon.
    Schedule refresh button
  11. Select Edit Credentials and enter your Office 365 credentials.
    Data source 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.
    Setting schedule refresh frequency

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.

Leave a Reply

Your email address will not be published. Required fields are marked *