How to Get Data from Microsoft Forms to Power BI

Getting your survey, quiz, or poll data out of Microsoft Forms and into a Power BI report is a great way to bring your results to life. Instead of looking at a static list of responses, you can build dynamic dashboards that update automatically as new submissions come in. This guide will walk you through the entire process, step by step, using the most reliable method.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Why Bother Connecting Forms to Power BI?

You might be wondering if it's worth the effort. The answer is a clear yes. While the default summary inside Microsoft Forms is fine for a quick glance, it's very limited. Connecting to Power BI opens up a world of possibilities:

• Live, Automated Reporting: Your Power BI dashboard becomes a "living" report. As soon as someone submits a response to your form, the data can be refreshed in your dashboard automatically. No more downloading CSVs every Monday morning for your weekly report.
• Superior Visualizations: Power BI offers a massive library of charts, graphs, maps, and tables that go far beyond the basic charts in Forms. You can create donut charts for satisfaction scores, maps showing respondent locations, and tables with open-ended feedback.
• Combine Data from Other Sources: This is a game-changer. You could combine your customer satisfaction survey results (from Forms) with sales data from your CRM or financial system. This lets you answer questions like, "Do our high-spending customers give us better feedback?"
• Deeper Analysis: With Power BI's DAX (Data Analysis Expressions), you can create complex calculations and custom metrics. You could calculate Net Promoter Score (NPS), create custom scoring for a quiz, or track trends over time.

The Challenge: There’s No Direct "MS Forms Connector"

If you've searched in Power BI's "Get Data" options, you might have noticed something missing: a direct connector for Microsoft Forms. This feels strange since both are Microsoft products, but it's due to how and where Forms data is stored. Understanding this distinction is the key to a successful connection.

There are two primary types of Microsoft Forms, and they store data in different places:

1. Personal Forms: When you go to forms.office.com and create a form under your own account, it's a "Personal Form." Its data lives somewhere in Microsoft's cloud, linked only to your user profile. The responses are not automatically stored in an easily accessible file.
2. Group Forms: These are forms created within a Microsoft 365 Group. This is what you get when you create a form from a team in Microsoft Teams, a SharePoint site, or an Outlook Group. When you create a Group Form, Microsoft automatically creates an Excel workbook in the group's shared SharePoint document library to store all the responses. This is the workbook we will use.

Because Group Forms automatically create a synced Excel file in a predictable location (SharePoint), this is the most stable and reliable way to get your data into Power BI.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Solution: Use Excel Online as the Bridge

The best practice is to structure your form as a "Group Form." This creates a direct link to an Excel Online file that will continuously update with new responses. This Excel file then becomes the source for our Power BI report.

Let's set one up.

Creating a Group Form and Getting the Linked Excel File

The easiest way to do this is through Microsoft Teams, as many businesses use it as a central hub.

1. Create a Team (if you don't have one): Every Team automatically has a Microsoft 365 Group behind it, which includes a SharePoint site for file storage.
2. Add the Forms App to a Channel: Go to the channel where you want your team to collaborate on the form. Click the "+" icon at the top to add a new tab, and search for "Forms."
3. Create a New Form: Choose to "Create a shared form that your team can edit and see results." Give it a name and click "Save."

That's it! When you do this, two things happen automatically:

• A new form is created that is owned by the Team/Group, not just you.
• More importantly, an Excel workbook with the same name as your form is instantly created in your Team’s SharePoint document library. You can find it by going to the "Files" tab in your Team's General channel and navigating to Documents > General.

Now, every time someone fills out the form, their response is automatically added as a new row in this live Excel file. We have our reliable data source ready to go.

Note: What if you already have a personal form with data? You can still make this work. Go to your form, click the "Responses" tab, and click "Open in Excel." This will generate a new Excel file in your personal OneDrive that stays synced to your form. The steps to connect to Power BI will be nearly identical, but the file path will point to your OneDrive instead of SharePoint.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step-by-Step Guide: Connecting the Excel File to Power BI Desktop

With our auto-updating Excel file ready, we can now hook it up to Power BI.

Step 1: Get the Correct File Path

Power BI can't use a standard SharePoint browser URL. We need a direct path to the file. Here's the fail-safe way to get it:

1. Navigate to the SharePoint document library or your OneDrive where the Excel file is stored.
2. Click the three dots next to the file name and select "Open" → "Open in app." This will open the file in your desktop version of Excel.
3. In the desktop Excel app, go to File → Info.
4. Click the "Copy path" button. This copies the direct URL to your clipboard.
5. Paste the path into a text editor (like Notepad). It will look something like: https://yourcompany.sharepoint.com/sites/YourTeam/Shared%20Documents/General/My%20Survey.xlsx?web=1
6. Delete everything from the question mark onward. Your final, clean URL should look like: https://yourcompany.sharepoint.com/sites/YourTeam/Shared%20Documents/General/My%20Survey.xlsx

This clean URL is what we will give to Power BI.

Step 2: Connect Power BI Using the "Web" Connector

Now we switch over to Power BI Desktop.

1. Open a new or existing Power BI report.
2. From the "Home" ribbon, click "Get data" and select "Web" from the dropdown list.
3. In the "From Web" dialog box, paste the clean URL you copied in the previous step. Click "OK."
4. Authentication is key! A new window will appear asking how to connect. Choose "Organizational account" from the options on the left.
5. Click "Sign in" and use your standard Microsoft 365/Office credentials (the same you use for Teams and Outlook).
6. Once you've signed in, click "Connect."

Step 3: Shape Your Data in Power Query

After successfully connecting, the Power Query Editor will launch. This is a powerful tool to clean and prepare your data before you start building visuals. Here are a few essential steps:

• Select a Table: In the "Navigator" pane that appears, you'll see a few options based on your Excel file. Forms data is always stored in "Table1." Check the box next to it to see a preview of your data. Click "Transform Data."
• Remove Irrelevant Columns: Your Form's data will include columns like ID, Start time, Completion time, Email, and Name. You can right-click the header of any column you don't need for your report (like Start time) and select "Remove."
• Check Data Types: Power Query is pretty smart, but it's good practice to double-check. Ensure dates are recognized as dates, not text. Any questions that require numeric answers (like a satisfaction score from 1-5) should be set to "Whole Number." You can change the data type by clicking the icon in the column header.
• Rename Columns: The column headers will be the full questions from your form (e.g., "On a scale of 1 to 10, how likely are you to recommend our product?"). These long headers make bad labels for charts. Right-click any column header and choose "Rename" to give it a short, simple name like "NPS Score."

Once you are happy with the data's shape, click "Close & Apply" in the top-left corner of the Power Query window.

Building Your First Microsoft Forms Dashboard

With your data loaded, you can now build your report. Drag and drop fields from the "Data" pane onto the report canvas and choose your visuals.

Here's a quick example for a customer satisfaction survey:

• Total responses: Use a Card visual. Drag the unique "ID" field onto the card and set its aggregation to "Count (Distinct)."
• Satisfaction score breakdown: For a multiple-choice question like "How satisfied were you?", use a Donut chart. Drag the question's field to both the "Legend" and "Values" wells (and set Values to "Count").
• Verbatim feedback: To show open-ended text answers, use a Table visual. Drag the corresponding text field into the table to create a scrollable list of comments.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Publishing and Setting Up a Refresh

Once your report is built, use the "Publish" button on the Home ribbon to save it to the Power BI Service (app.powerbi.com). In the online service, navigate to your dataset's settings, find "Scheduled refresh," and set it to update automatically as often as you like (e.g., every day at 9 AM). Now your report will always show the latest submissions without any manual work.

Final Thoughts

Connecting Microsoft Forms to Power BI turns simple survey data into a powerful, automated decision-making tool. By using an intermediary Excel file stored in SharePoint or OneDrive, you create a seamless pipeline that keeps your dashboards refreshed with the latest responses, saving an incredible amount of time.

While handling data from within the Microsoft ecosystem is manageable, we know that modern marketing and sales stacks are far more complicated. Your team's really important insights are often spread across a dozen tools like Google Analytics, Shopify, Salesforce, and Facebook Ads. We built Graphed to solve this bigger problem, making it easy to connect all your data sources in one place and build the dashboards you need by simply describing them in plain English, putting an end to endless hours of manual report building.