Power BI + Office 365 Forms

Today got challenged by a colleague on how to explore Microsoft Forms data with Power BI.

The easy answer is: export the Excel file from the “Responses” menu and explore it with Power BI:

But the goal was to avoid this manual process! There is a simple solution, just need to follow the following steps:

1 – Download the Excel using the “Open in Excel”

2 – Go to your download history and grab the url:

Copy the url into the following M code:

let
    Source = Web.Contents("PASTE URL IN HERE"),
    Excel = Excel.Workbook(Source),
    Table1_Table = Excel{[Item="Table1",Kind="Table"]}[Data]
in
    Table1_Table

Authenticate with your Office 365 credentials:

And your done! The data is ready to explore in Power BI and off course refreshable:

Note: The form url has a parameter “maxResponseId” that needs to be increased if you want to download the future entries, I’ve tried to input a maximum value of ~1000 and had no issues

10 thoughts on “Power BI + Office 365 Forms

  1. Nice tips, saves manual steps of downloading and refreshing the data.

    However, it’s a bit buggy when you refresh data from PowerBI Services. I can make it work once, but on all other scheduled refreshes it fails.

    Microsoft ought to provide a connector for Microsoft Forms, instead of going through the CSV transformation.

  2. Great post but there is a small problem, the URL that i take from downloads is unique for the amount of times people answer the form.
    for example it is a different URL to the excel file when 5 people OR 6 people answer the form.
    There isn’t a constant URL for any amount of answers?
    Thank you

      1. In my URL after “maxResponseID=” there is additional text, if I am changing the parameter to be “X” should I delete the text following it?

  3. Thank you so much. Helps save manual effort…. was thinking on how to automate this part and found this link. Appreciate.

  4. This is the first time I am using M code, when I go about the steps you have listed and write out the code, I am not prompted to fill in username and pass. Do you know what I am doing incorrectly?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s