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

9 thoughts on “Power BI + Office 365 Forms

  1. Anonymous says:

    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

  3. Mahesh Mk says:

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

  4. KS says:

    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