Collab365

Pull data from Microsoft Forms into Power Query or Power BI

Q: Can I connect 20+ Forms to one Power BI dashboard?

Yes, use Power Automate to route all forms into a single SharePoint list with a 'Form Name' choice column for unified dashboard slicing.

Q: Does this work with Forms stored in my personal OneDrive?

Yes, but avoid it—personal forms risk permanent data loss after 30 days if owner leaves; move to M365 Group.

Q: Why are multiple-choice answers showing as code characters?

Multi-select answers come as JSON arrays; fix by adding a 'Join' action in Power Automate with comma delimiter before SharePoint/Power BI.

C

Collab365 TeamAuthorPublished Apr 23, 2026

2

At a Glance

Target Audience: Power BI Analysts, Data Engineers, M365 Admins
Problem Solved: Unreliable Forms data sync to Power BI: stale data, file locks, orphaned forms, limited refreshes.
Use Case: Real-time training quiz dashboards tracking scores, completion rates across multiple forms.

Yes, connect Forms to Power BI directly via Excel, Power Automate to SharePoint, or Graph API. We tested all three; Power Automate wins for real-time-ish updates. If you use Microsoft Forms v2.0 and Power BI Desktop April 2026, you can fully automate dashboard refreshes without manual exports. The Collab365 team found that the direct Excel connector can refresh every 15 minutes using a Power Automate trigger, but pushing data to SharePoint lists with choice columns for scores is far more reliable.

TL;DR / Quick Answer

1. Best for Scale: Power Automate to SharePoint List. Highly reliable, gives near real-time data, and avoids file lock errors. (Path: Power Automate > Create > Automated Cloud Flow > Trigger: When a new response is submitted).

2. Quickest: Direct Power BI Connector to Forms Excel Sheet. Fast setup, but relies on scheduled refreshes and manual file opens. (Path: Forms > Responses > Open in Excel > Copy link > Power BI Get Data > Web).

3. Advanced: Microsoft Graph API / Power Query Custom Connector. Best for data engineers who need to pull historical data programmatically using app registrations.

---

Where Does Microsoft Forms Store Responses in 2026?

Before we start building dashboards, we must understand where your quiz data actually lives. Microsoft Forms has changed over the years. Data storage depends entirely on how you created the form and which version of the product you use.

If you create a standard form from your personal account, the responses are stored in a hidden, Microsoft-managed database.1 When you click "Open in Excel", it generates a live file in your OneDrive. If you use Dynamics 365 Customer Voice (formerly Forms Pro), your data lives in Microsoft Dataverse.2

According to Collab365 analysis, knowing your storage location dictates your entire Power BI strategy. Here is exactly where your data sits today.

Form Type / Creation Method	Storage Location 2026	Best Power BI Connection Method
Personal Form (forms.office.com)	Microsoft-managed DB -> Excel on OneDrive	Power Automate to SharePoint
Group Form (Teams / SharePoint)	Excel file in SharePoint Document Library	Power BI SharePoint Folder Connector
Forms in Excel (Insert > Form)	Excel file in OneDrive / SharePoint	Direct Power BI Web Connector
Customer Voice (Forms Pro)	Microsoft Dataverse (Common Data Service)	Power BI Dataverse Connector

We wasted hours on manual exports until we mapped out these storage locations. If your data is in Dataverse, you can use DirectQuery. If it is in Excel, you are forced to use Import Mode.

Key Takeaway: Do not leave your training quizzes tied to a personal account. Always move your form to a Microsoft 365 Group. If the creator leaves the company, the form and its data vanish after 30 days.

---

Method 1: Quickest - Direct Power BI Connector to Forms Excel Sheet

If you have a single quiz and need a dashboard up quickly, connecting Power BI directly to the backing Excel file is the fastest route. In the past, this was a clunky process. Today, you can hook Power BI straight into the live Excel file on the web.

However, there is a massive catch that Microsoft introduced recently. In November 2024, Microsoft changed how Forms syncs with Excel. Responses from MS Forms no longer automatically push to the linked Excel file in the background. The sync only occurs when the Excel file is physically opened in Excel for the Web.1

This single change breaks thousands of Power BI reports every day. If nobody opens the Excel file, your Power BI scheduled refresh will pull stale data.1 We will show you how to fix this, but first, let us look at how to make the initial connection.

Step 1: Get the Clean Excel URL

You cannot just copy the link from your browser address bar. You need the exact file path.

Go to Microsoft Forms and open your quiz.
Click the Responses tab at the top.
Click Open in Excel.1
This opens Excel for the Web. Go to File > Info > Copy link.3
Paste the link into a blank Notepad document. It will look something like this: https://contoso.sharepoint.com/:x:/r/sites/HR/\_layouts/15/Doc.aspx?sourcedoc\=\{ID}\&file\=Quiz.xlsx\&action\=default\&mobileredirect\=true.
Delete everything from the question mark (?) onwards. Your clean URL must end in .xlsx.

Key Takeaway: Power BI will reject the Excel link and throw a web connection error if you leave the ?web=1 or ?action=default parameters at the end of the URL. Always clean the link first.

Step 2: Connect in Power BI Desktop

Now we pull that cleaned URL into our data model. Make sure you are using a recent version of Power BI Desktop. Starting in April 2026, the old file picker experience is deprecated, so the menus look slightly cleaner now.4

Open Power BI Desktop.
Click Get Data > Web from the Home ribbon. Do not choose the standard 'Excel Workbook' option, as that expects a local file path.
Paste your clean .xlsx URL into the basic URL box.3 Click OK.
When prompted for credentials, ignore the 'Anonymous' or 'Windows' tabs. Select Organizational Account on the left menu.6
Click Sign In and authenticate with your Microsoft 365 account.
The Navigator window will appear. Select your table (it is usually named Table1) and click Transform Data to open Power Query.

Screenshot: Power BI Get Data > Web connector with Forms Excel URL pasted.

How to Force a 15-Minute Refresh in 2026

Remember the November 2024 sync issue we mentioned? If you rely on native Power BI scheduled refreshes, it will only pull the data that was present the last time someone manually opened the Excel file.1

Furthermore, a standard Power BI Pro license limits you to 8 scheduled refreshes a day.7 If you are tracking a live training session, waiting three hours for data is useless. We can fix both issues using a Power Automate workaround.8

Go to Power Automate and click Create > Scheduled cloud flow.
Name your flow "Force Power BI Refresh".
Set it to repeat every 15 Minutes.8
Add a new action step: search for Power BI and select Refresh a dataset.8
Select your Workspace and your semantic model (dataset) name.
Save and turn the flow on.

This flow pings the Power BI API every 15 minutes.8 However, to fix the Excel sync issue, you should actually add one step before the Power BI refresh in your flow: an Excel action to "Add a row into a table". By having Power Automate directly interact with the Excel file every time a form is submitted, it bypasses the need for a human to open the file.1

Key Takeaway: Method 1 is fast, but it is fragile. If a user opens the Excel file in their desktop app and leaves it open, the file locks. Your Power BI refresh will then fail with a timeout error.

---

Method 2: Best for Scale - Power Automate to SharePoint List

If you are a data analyst managing 20+ forms tracking course scores, Method 1 will eventually fail you. Manual Excel exports fail at scale. File locks happen daily. Data gets corrupted when columns are accidentally shifted.

To solve this, we intercept the form submission the exact second it happens and write the data directly into a SharePoint List.1

SharePoint lists act like lightweight SQL databases. They are highly structured, they enforce column data types, and they never suffer from file lock errors. A Power BI dashboard connected to a SharePoint list is virtually unbreakable.

Step 1: Prepare Your SharePoint List Architecture

Before we touch Power Automate, we must build the destination. Go to your team's SharePoint site and create a new blank list.

You need to create columns that exactly match the questions in your quiz.10 For a typical training quiz, you might set up the following columns:

Respondent Email (Type: Single line of text)
Completion Date (Type: Date and Time)
Total Score (Type: Number)
Passed Quiz (Type: Yes/No)
Question 1 Answer (Type: Single line of text)
Course Name (Type: Choice column)

By adding a "Course Name" choice column, you can pipe 20 different Microsoft Forms into this exact same SharePoint list.11 You just tell Power Automate to tag each submission with the correct course name as it arrives. This is how you build a single, unified dashboard for all company training.

Step 2: Build the Power Automate Flow

Now we connect the plumbing. This is where the magic happens.

Open Power Automate and click Create > Automated cloud flow.12
Give your flow a name and choose the trigger: When a new response is submitted (Microsoft Forms).12 Click Create.
In the trigger block, select your specific Form ID from the dropdown menu.
The Critical Missing Step: Add a new action called Get response details.9 Many beginners skip this. The trigger only tells Power Automate that someone submitted a form. It does not actually grab their answers. You must use the "Get response details" action to fetch the data.
In this action block, select your Form ID again. Then, click into the "Response Id" field. A dynamic content menu will pop up. Select the dynamic Response Id provided by the trigger.9

Screenshot: Power Automate flow builder highlighting the 'Get response details' action block.

Step 3: Write the Data to SharePoint

Now that Power Automate has the answers, we push them to our list.

Add a final action: search for SharePoint and select Create item.9
Select your SharePoint Site Address from the dropdown.
Select your List Name.
As soon as you select the list, all the columns you created in Step 1 will appear as blank fields on your screen.
Click into each field and use the dynamic content menu to map the quiz answers to the list columns. Put the 'Responder Email' into the Email field, the 'Submission Time' into the Date field, and so on.13
Save the flow and submit a test response on your quiz. You will see the data appear in your SharePoint list within seconds.

Key Takeaway: This setup gives you near real-time data. The moment a user hits submit on the quiz, Power Automate pushes it to SharePoint, ready for Power BI to consume.

Handling JSON Arrays in Multiple-Choice Questions

If your form has multiple-choice questions where users can select more than one answer (e.g., "Select all the correct safety procedures"), you will hit a roadblock.

Microsoft Forms sends multi-select answers to Power Automate as a raw JSON array. It looks like this: ``.14 If you try to push this directly into a SharePoint text column, your dashboard will display that ugly bracketed code.

To fix this, you must intercept the array before it hits SharePoint.

Add a Join data operation action right before your SharePoint 'Create item' step.
In the 'From' field, put the dynamic content of your multiple-choice question.
In the 'Join with' field, type a comma and a space (, ).
Now, in your SharePoint 'Create item' step, map that column to the output of your Join action, not the raw Forms data. The data will now arrive cleanly as "Answer A, Answer B".

Step 4: Connect Power BI to the SharePoint List

Connecting the dashboard is the easy part.

Open Power BI Desktop.
Go to Get Data > More > SharePoint Online List.15
Enter your core SharePoint site URL (do not paste the URL of the list itself, just the root site).
Select your specific list from the navigator window.
Click Transform Data.

When you open Power Query, SharePoint lists often hide the actual column data inside record structures. Look for your columns, click the small double-arrow icon at the top of the column header, and click Expand. Uncheck 'Use original column name as prefix' and hit OK. Your clean quiz data is now ready for DAX calculations.

---

Method 3: Advanced - Microsoft Graph API or Power Query Custom Connector

Methods 1 and 2 cover 95% of business needs. However, if you are a developer working in an enterprise environment, you might be tasked with pulling historical data from hundreds of forms simultaneously. Setting up 100 different Power Automate flows is inefficient. You need a programmatic API solution.

There is a major catch in 2026: Microsoft Graph API does not yet have a generally available, officially documented endpoint specifically for extracting Microsoft Forms responses.16 The standard v1.0 endpoints focus on users, mail, and groups, not forms.18

However, data engineers have discovered that you can tap into the underlying web API that powers the Forms user interface.19 Because this is an undocumented endpoint, Microsoft could change it without notice. We only recommend this for advanced users.

Registering the App in Entra ID

To interrogate the API, you cannot just use a username and password. You need a secure token.

Go to the Microsoft Entra ID (formerly Azure AD) admin centre.
Click App registrations > New registration. Give it a name like "Forms_PowerBI_Connector".
Under API permissions, click Add a permission. This is tricky because Forms is not listed in the standard Graph permissions. You must add the specific scope: https://forms.office.com/.default.[19](#sources)
Grant admin consent for your tenant.
Go to Certificates & secrets and generate a new Client Secret. Copy the Value immediately, as it will be hidden later.20
From the Overview page, copy your Application (client) ID and Directory (tenant) ID.19

Generating the Bearer Token and Calling the Endpoint

To get the data into Power BI, you must first write M code in Power Query to request an OAuth 2.0 Bearer Token, and then use that token to hit the Forms API.

The API endpoint structure looks like this for a personal form: https://forms.office.com/formapi/api/\{tenantid}/users/\{userid}/light/forms('\{formid}')/responses.[19](#sources)

If the form is owned by a Microsoft 365 Group (which we strongly recommend), the endpoint shifts to target the group ID: https://forms.office.com/formapi/api/\{tenantid}/groups/\{groupid}/forms('\{formid}')/responses.[17](#sources)

In Power BI Desktop, you would open a Blank Query and enter advanced M code. Your code must first make a POST request to https://login.microsoftonline.com/\{tenantid}/oauth2/v2.0/token using the client_credentials grant type.20

Once the token is returned, your M code makes a GET request to the Forms endpoint, passing the token in the Authorization header as "Bearer " & TokenValue.

Key Takeaway: The API returns the data as a JSON payload. You will need to use Power Query's Json.Document() function to parse the payload, drill down into the 'value' array, and expand the response records into standard table rows.21

This method is incredibly fast and scales infinitely, but it requires deep M code knowledge to handle API pagination using the $top and $skip OData parameters, as the endpoint will limit the number of responses returned in a single call.21

---

Power BI Dashboard for Quiz Scores: Build It in 10 Minutes

Once your data is flowing into Power BI Desktop via Excel, SharePoint, or an API, the real analytics work begins. Training managers usually want to know two things: Who finished the quiz? and What was their score?

Here is exactly how we built a training dashboard last week.

Cleaning the Data in Power Query

Before you write any formulas, your data must be shaped correctly. Microsoft Forms records the "Submission Time" as a text string that often defaults to US date formats depending on your tenant settings.

In Power Query, you must highlight the Date column, click Change Type, and select Date/Time. If you are in the UK and the days and months are inverted, do not just click Date/Time. Right-click the column header, select Change Type > Using Locale, choose Date/Time, and select English (United States) to force Power Query to read it correctly before converting it to your local format.

Furthermore, if you want to analyse individual questions, you must unpivot the data. Highlight your 'Respondent Email' and 'Date' columns, right-click the header, and select Unpivot Other Columns. This turns your wide table (with 20 question columns) into a long, tall table with an 'Attribute' column (the question) and a 'Value' column (the answer).11 This makes visualising the most commonly failed questions incredibly easy.

Essential DAX Formulas for Training Scores

To calculate completion rates and average scores, you need Data Analysis Expressions (DAX).

1. Calculate the Completion Rate

If you have a separate roster table detailing everyone who was assigned the training, you can compare it against the Forms data to find the completion percentage.

Go to the Modeling tab and click New Measure.22 Enter this DAX code:

Completion Rate = DIVIDE(COUNTROWS(FILTER(Training, Training = "Completed")), COUNTROWS(Training), 0).23

This formula counts the rows where the quiz was finished and divides it by the total number of assigned staff.24 The , 0 at the end prevents a divide-by-zero error if the table is empty.

2. Convert Quiz Points to a Percentage If you use Microsoft Quiz, you can assign points to correct answers.14 Forms exports the total points achieved by the user in a column. To turn this into a standard percentage score for your dashboard, create this measure:

Quiz Percentage Score = DIVIDE(SUM(FormsData), [Max Possible Points], 0)

Drop this measure into a Card visual or a Table visual alongside the employee's name to instantly rank your top performers.

Enter Copilot: The 2026 Update

You might not even need to write DAX anymore. In March 2026, Microsoft launched the "Surveys Agent" and integrated Copilot Chat directly into Microsoft Forms and Power BI.25

If you hold a Microsoft 365 Copilot license, you can use conversational prompts to generate insights. The Surveys Agent acts like a professional researcher.27

Open the Copilot pane in your Power BI report and type:

"Summarise the top performers from the training quiz data based on Total Score, and identify which multiple-choice question had the lowest correct answer rate."

Copilot will analyse the semantic model, generate a plain English narrative, and even build the visual charts for you instantly.5 It can draft summary pages with a single click, totally changing how analysts present data to management.28

Key Takeaway: Do not waste hours building manual pie charts. Use the new 2026 Copilot Surveys Agent to pull conversational insights directly from your Forms dataset.25

---

Common Pitfalls and Fixes (We Learned the Hard Way)

Integrating Microsoft 365 platforms is brilliant when it works seamlessly, but it is incredibly frustrating when data pipelines break. Here are the top three errors we see on the Collab365 forums and exactly how to fix them.

1. The "Orphaned Form" Disaster

This is the most common and devastating issue. A staff member creates a brilliant training quiz on their personal Microsoft Forms account. It runs for months. Then, that staff member leaves the company. Their Microsoft 365 account is disabled by IT.

You have exactly 30 days from the account deletion date for an IT Administrator to access the admin centre and transfer ownership of that form.29 If you miss this 30-day window, the form, the Excel file, and all historical data are permanently deleted. There is no recovery. Your Power BI dashboard will instantly break.

The Fix: Never use personal forms for business processes. Always click "Move to a group" in the Forms web portal immediately after creating a form.31 This assigns ownership to a Microsoft 365 Group, ensuring the data survives even if the original creator leaves.

2. Power BI Refresh Fails Due to Excel File Locks

If you decided to use Method 1 (Direct Excel Connector), you will eventually see a scheduled refresh failure in the Power BI Service. This happens because the backing Excel file is locked.

If a manager opens the raw Excel file in their desktop application to 'take a quick look' at the responses and leaves the window open, the file becomes locked for editing. When Power BI attempts its scheduled background refresh, it cannot access the file and throws a timeout error.1

The Fix: Strictly enforce a policy that users can only view responses via the Power BI dashboard or the Forms web portal. They must never open the raw Excel file. Better yet, accept that Excel is a fragile database and switch to Method 2 (SharePoint Lists), which never suffers from file locks.1

3. Missing Gateway Connection Errors

If you are pulling cloud data from Microsoft Forms, but you are joining it in your Power BI model with on-premises data (like an old local SQL server holding HR employee records), Power BI will suddenly demand an On-Premises Data Gateway.7

Cloud-to-cloud connections do not need gateways, but the moment you mix in local data, the entire dataset requires one.7

The Fix: Ensure your Gateway is installed on a server that is always awake. More importantly, keep it updated. Microsoft frequently deprecates older drivers. For example, in the April 2026 release, Microsoft deprecated the built-in Netezza ODBC driver and forced updates to the Simba Vertica drivers.4 If your gateway runs on outdated drivers, your entire hybrid dataset will fail to refresh, taking your Forms dashboard down with it. Keep your gateway patched to the latest monthly release.

---

Forms vs Alternatives: When to Switch to Quiz or Customer Voice

Microsoft offers three different tiers of survey tools. You might be struggling to build your Power BI dashboard simply because you are using the wrong tool for the job. Do not force standard Forms to do enterprise work.

Feature	Standard Microsoft Forms	Microsoft Quiz	Dynamics 365 Customer Voice
Best Used For	Quick polls, internal event feedback.	Scored training, tests, math equations.	Enterprise customer feedback, Net Promoter Score (NPS).
Data Storage	Excel (OneDrive/SharePoint).34	Excel (OneDrive/SharePoint).	Microsoft Dataverse.2
Automated Scoring	No. Data is just text.	Yes. Assign points to correct answers.	Yes, advanced sentiment analysis via AI.2
Power BI Integration	Web Connector or Power Automate.	Web Connector or Power Automate.	Native Dataverse Connector (DirectQuery available).
Cost	Included in M365 basic plans.35	Included in M365 basic plans.	Requires premium Dynamics 365 licensing.36

If you need advanced metrics like NPS tracking, conditional routing based on expense amounts, or AI sentiment analysis on open-ended text answers, you should upgrade to Customer Voice.2 Customer Voice is the evolution of Forms Pro. It writes data directly to Dataverse tables, which Power BI connects to effortlessly using the native Dataverse connector, allowing for true DirectQuery live reporting.2

---

Frequently Asked Questions (FAQ)

Can I connect 20+ Forms to one Power BI dashboard? Yes. The most robust way is to use Power Automate to route all 20 forms into a single, unified SharePoint list.9 Add a choice column to your SharePoint list called "Form Name". Inside your Power Automate flow, hardcode the name of the specific form into that column. Your Power BI dashboard will only need to connect to one SharePoint list, and you can use the "Form Name" column as a slicer to filter the visuals.

Does this work with Forms stored in my personal OneDrive? Yes, but we strongly advise against it. As detailed in our pitfalls section, if you leave the organisation, the Power BI dashboard will break, and the raw data could be permanently deleted after 30 days.29 Always move the form to a SharePoint Group.

Why are my multiple-choice answers showing up as code characters? If you pull raw data via Power Automate, multiple-choice questions where users select several answers appear in JSON format (e.g., ["Apples","Oranges"]).14 You must use a Join data operation action in Power Automate, using a comma delimiter, to flatten the array into plain text before it hits Power BI or SharePoint.

Can I embed my Power BI report back into a Microsoft Form? No. You cannot embed Power BI visuals into a Form. However, you can embed a Power App into a Power BI dashboard to allow users to write data back directly from the report interface.38 Be aware that the Power Apps visual can sometimes suffer from rendering delays upon page load.38

Do I need a Premium license for a 15-minute refresh? Normally, yes. Standard Power BI Pro licenses are strictly limited to 8 scheduled refreshes a day (every 3 hours).7 However, by creating a Power Automate flow that triggers the "Refresh a dataset" action every 15 minutes, you can bypass the native scheduler limitations regardless of your license type.8

Start with Method 1 today if you are new to this and need a quick win for your manager. However, aim to build the Power Automate to SharePoint pipeline (Method 2) as your permanent, long-term solution. It is virtually unbreakable once running. For insights and more advanced DAX formulas, check the Power BI Space on Collab365 Spaces.