Published by Barry Francis on the 11 of December 2019.

This blog post seeks to assist Microsoft Form users with the ability to process Microsoft Form submissions using Power Automate to push through submissions to Microsoft Excel, for consumption by a Power BI report.

CONTENTS

Excel Document setup
Power Automate setup
Power BI setup

Excel document setup

In this scenario, we will be setting an Excel document through the Teams application. You can do the same by creating an Excel document and adding it to a SharePoint folder at any point.

Open Teams and find the File tab in a team channel. There, create a new Excel spreadsheet with a name of your choosing.

Then, give headers for the data you want captured under each heading. For example, First Name as a header will capture all first name submissions.

Once set, click on Insert and choose to insert a Table (Make sure you’re highlighting ONLY the first row with ALL the headers required).

Once created, make sure to get a link for the above Excel document location and keep it handy for point 3.

To do this, choose to Open in SharePoint:

The URL in the browser should show the sites and the site name:

Keep this whole URL for use in Point 3 (Power BI).

Power Automate setup

Log into Power Automate and click on the My Flows tab. Then select the New drop down list and choose the Instant option.

Skip the trigger by clicking on the small blue text: Skip

Type into the trigger box: Microsoft Forms when a new

This will show the following trigger below, select it (Microsoft Forms – When a new response is submitted).

Click on the + New Step button under the trigger to add another step. Add the Get response details from Microsoft Forms.

Form ID: the form you’ve already created, select it from this list

Response ID: click in the box and choose to push through the list of response from the previous step (select the List of response notifications Response Id block to push it into the Response Id field)

Click on the + New Step button under the above action to add another step. Add the Add a row into a table from Microsoft Excel.

Once selected, set the following fields:

Location – The document SharePoint sites will be listed here, search for the teams name.

Document Library – Generally called documents if linked to a team.

File – Select the folder icon, then choose the channel name and finally select the Excel document you’ve created in that channel.

Table – If you didn’t follow the initial section on creating an Excel document, please do this now and make sure a table is inserted with the appropriate headers.

Once set, the list of headers will appear and allow you the ability to map the Microsoft Form fields to the Excel document headers:

Click Save to save the changes. Now whenever a form is submitted, the Excel document will be updated.

Power BI setup

Download the Power BI Desktop tool.

Once downloaded, open it and close the Get Data window that opens.

Choose to Sign In on the top right of the tool with your own O365 user account.

Then choose to Get Data, select More and find the SharePoint Folder.

Enter in only the link with sites/TEAMNAME into the site url. For example:

https://[ENVIRONMENT].sharepoint.com/sites/[SITENAME]/Shared%20Documents/Forms/AllItems.aspx?

Only enter: https://[ENVIRONMENT].sharepoint.com/sites/[SITENAME]/

Click OK to continue. If it asks you to sign in, make sure you use the organizational account and use your O365 account to sign in.

Click on the Edit button from the full page of documents stored to make sure we only set a single document into its query.

We can now see our SampleData File and the folder path.  Make sure to set a filter extension on your Excel document before continuing.

Next click the double down arrows to load the excel file.

Power BI Desktop will then go to the SharePoint site and download the information inside your excel file.

For my data I have all the information retained in a table within my excel document.  The table name is call MyDataTable.  Thus, clicking on the Table link in the MyDataTable row I will be able to open all the data within this table.

Finally the data is loaded from the excel table.  Click Close & Apply on the Home ribbon to load the data into Power BI:

Then use the data to your hearts content in whatever format you choose to display it in Power BI.