Get Forms results directly to Power BI

Old fashioned form analyzing requires extra protection.

Forms is a handy tool to collect user input for various purposes. You can check the results directly from Forms service online, but for more in depth analytics the results can be downloaded in an Excel file.

That means that the results can be interpreted and refined in Power BI with ease. So, let's get on to it.

Create a form with Forms

If you haven't had the chance to create forms with Forms before, there's no need to be afraid. It's really straight forward, so I won't go through the process here. Really, there's nothing to it. There's plenty of tutorials out there. For example check out Kevin's excellent video on the subject: How to use Microsoft Forms

Get form id

The part we are interested in your newly created questionnaire is the form id. Open you form if it isn't open already in editor or in preview. Check the URL from the browser and look for part starting with FormId=

Starting after the equals sign to the end of the URL is your form id. As the name suggests, it identifies your form and thus is a really long and obscure. Copy it somewhere, eg. Notepad.

Enter test data

For maximum effort enter some dummy data into the form, like 10 or so different answers so there's something to look at in the next steps.

Get data to Power BI

In PBI Desktop use Get Data >> Web. Paste this url:

https://forms.office.com/formapi/DownloadExcelFile.ashx?formid=YOURFORMID&timezoneOffset=YOURTIMEZONEOFFSETINMINUTES&minResponseId=1&maxResponseId=1000

Replace YOURFORMID with your forms real Id, and find out your timezone offset in minutes. I used this: https://www.tutorialspoint.com/javascript/date_gettimezoneoffset.htm. Daylight savings causes the offset not to be constant, so beware, if it is crucial. The final URL is really just a link to download your form results in an Excel file. If you paste it into a browser (and you are signed in to O365) it will download the file.

Now, after the URL is complete, connect and sign in with your Office credentials, organizational account or whatever works. After a successful login you should be presented with this view:

Select the Excel Workbook.

From the row 2 click on Table. There you go, the results of your form. Check the data types, make your M magic, Close & Apply and go on with the visualizations and DAX and what not.

Instant results fresh from the source

Just hit the refresh button in PBI and you are presented with the up to date results straight from the Forms form. The future is now, old man.

Dude checking out some fine results. Damn those pie charts are fancy.  

For your homework I'll leave a simple task:

Create a function that accepts form id and outputs the form.