A client recently asked me if it was possible to build a Power BI report using data from a Microsoft Form. I have come across this question a few times now, and you could be excused for thinking this would be a straightforward task. Despite the fact that Microsoft has developed both Power BI and Microsoft Forms, the process of using the data in a Form with Power BI is not super easy; certainly not as easy as you may expect.
Microsoft Forms (or Forms for short) is an online survey tool that comes bundled with a Microsoft 365 subscription. Forms provide a friendly, simple user interface to create and share surveys. The data is collected online through a browser, and the responses are stored in an Excel file. The solution I present below will show you the best way to configure a Form so that you can report on the responses using Power BI.
Why can’t we use a standard MS Form?
In the example below, I created a new form directly in Microsoft Forms. Easy, right?
- Navigate to forms.office.com
- Click the New Form button
Create the survey, share the form, and gather the responses. Once you have collected responses you will notice that it is possible to export the results to Excel as shown below.
But here’s the issue; this export must be done manually and hence this introduces a manual process that makes the whole process harder than it needs to be. If you take this approach, you will need to rely on manual intervention to download the latest response data every time you want to update your report in Power BI. If you use the above approach, you cannot connect directly to the Excel file without manual intervention.
Microsoft Forms with OneDrive
The best way to remove the need for manual intervention is to trigger the creation of the form from OneDrive, rather than from the Forms home page.
Note 1: For this solution we will use OneDrive, but these same steps can also be applied inside a SharePoint Group. In this example, I will build the form initially in a folder within My Files in my Personal OneDrive for Business account from a browser. The easiest way to find the online version of my OneDrive folder is to navigate to my OneDrive folder in Explorer, right click on the OneDrive folder (#1 below) and select View Online (#2 below).
Note 2: This process works as long as you do not manually edit the table of data stored in Excel. You should not try to manually manipulate the Excel table yourself – if you do, you should expect things to stop workings (you have been warned).
Once you have the browser open, navigate to the location where you would like the survey results to be stored, then click New\Forms for Excel as shown below.
OneDrive will then ask you to give the new Excel file a name and will save it to the folder when you click Create.
You will then be taken to MS Forms, where you create the form as normal. However, because this form was initiated in OneDrive and not directly in MS Forms, when you receive responses in the form, all new responses will be added to the Excel file you just created AND you can directly connect to that file without manual intervention.
To check if you have successfully created the form correctly, you will need to find the Open in Excel button. To find the Open in Excel button, you will need to be in the Responses view of your form. Simply click the Responses tab (#1 below) to switch from the Questions (form creation view) to the Responses (view results) view.
You will know the form has been built correctly if you see a cloud on the bottom of the Open in Excel icon from within MS Forms.
Caution when using a Shared Library as the Home folder
These same steps can also be followed using a Shared Library, however there are potentially two different scenarios you will be presented with when choosing a Shared Library to host your Excel workbook.
1. If your Shared Library is your main SharePoint site, you will be presented with the option to create an Excel Survey. This is not the same as an MS Form, and I recommend you don’t use this approach.
2. If your Shared Library is part of a Microsoft Group, then you will see the Forms for Excel option shown earlier. You should avoid using the “Excel Survey” approach and instead use “Forms for Excel”.
Giving others access to the responses.
The solution I have demonstrated here works, however be aware that the Excel file will be stored in your Personal OneDrive folder. No one else will be able to access the responses (or refresh the Power BI report) unless you instead place the Excel file in a Shared Library Office Group. But all is not lost. There is an additional step that we can apply to our newly created form to change the home folder from your Personal OneDrive to a centralised Shared Library location. Of course you could have created it in the Share Library from the start.
In the image above, you will notice that I have access to two Shared Libraries, the Excelerator BI Pty Ltd Team Site and the Excelerator BI Team. The Excelerator BI Team is an Office Group within Excelerator BI Pty Ltd Team Site and is a perfect location to store my newly created form.
To move the form into this folder, return to MS Forms home page, locate your form, and click on the ellipsis on the right-hand corner of your form (1). Then, simply select the move option from the pop-up menu (2).
When the popup menu appears, select the group you wish to move the form into, and click Move.
To confirm the form moved correctly, check the name assigned to the form. If you succeeded in moving the form to your Shared Library, instead of showing your name as the form owner, it will now display the group.
After the move, you will see the Excel file in the shared folder.
Building the Power BI Report
With our form now in production, we are ready to add it as a data source to Power BI. If you have your Shared Library synchronised to your PC, then you simply need to use the Excel connector (1) and load the data as you normally would from an Excel source. If, however, your Shared Library isn’t sync’d to your PC or you prefer to access it via the cloud to alleviate the need for a gateway, we then have the option to use either the Web or SharePoint Folder connector (2).
Connecting via SharePoint
If you are choosing to connect via the SharePoint Folder connector, you may already be aware that Power BI and SharePoint do not play together particularly well. For more information in this regard, Matt has previously posted an article.
As an aside to the main story of this article, if your SharePoint site has many files stored on it, the standard SharePoint Folder connector will be slow and painful. This is because it is connecting to the site using the SharePoint.Files M function, and thus checking for each and every file present on the site (read “slow and tedious”).
A simple trick to help improve the performance in this regard is to switch the connection to SharePoint.Contents.
With the SharePoint.Contents connector, you will be able to navigate the folder structure of the SharePoint site rather than the individual files, so this is a good solution if all the files you want to load are located within a single folder path. It is less helpful if you want to combine multiple files that are in multiple locations within your site.
Connecting via Web Connector
If you want to connect to the Excel file using the Web.BrowserContents connector you must do it a particular way. You cannot simply copy the URL you see when you navigate to the file in SharePoint.
If you try to connect with this URL, you will see an error.
The correct way to connect to the file is to first connect to SharePoint and then navigate to the path of the file.
To find the path, first open Excel on your computer.
- Go to the browser, click on the ellipsis next to the Excel file (1),
- select Open (2),
- and then select Open in app (3).
Once the file is open in Excel on your PC, go to File, Info (1) and click on Copy Path (2).
Paste the path into the Web connector URL bucket in Power BI Desktop. Remove everything after the .xlsx as shown below
Once authenticated into the SharePoint site, follow the data loading navigator prompts as you normally would, to load your MS Forms response data into Power BI.
What do you think?
If you liked this article, let me what you think by filling in a response on the OneDrive MS Form, or drop a comment below. If you have a better approach, I would love to hear about it.