How to Import Microsoft Forms Responses into Power BI Automatically
Question details
The user needs to import both existing data and future submissions from Microsoft Forms into Power BI automatically.
- Product
- Microsoft Forms, Power BI, Microsoft Excel
- Device & OS
- not provided
- Scenario
- Integrating survey or form data into a dashboard for visualization and automated reporting without manual data entry.
- Observed behavior
- Data is successfully collected in Microsoft Forms, but requires an automated pipeline to continuously reflect both past and new entries in Power BI dashboards.
Ensure you have an active Microsoft 365 account with access to Microsoft Forms, Power Automate, and OneDrive/SharePoint, along with Power BI Desktop installed.
Use Excel and Power Automate to Connect Forms to Power BI
Export your current responses to an Excel workbook stored in the cloud, connect it to Power BI, and use a Power Automate flow to append new form responses automatically.
Because Power BI does not have a native direct connector for Microsoft Forms, an Excel file hosted on OneDrive or SharePoint acts as the perfect intermediary database.
By formatting the Excel data as a Table, you allow Power Automate to push new submissions row by row, which Power BI can then refresh.
Open your Microsoft Form, navigate to the Responses tab, and click the 'Open in Excel' button to download the current dataset.
Upload the downloaded Excel file to OneDrive for Business or a SharePoint Online document library so it remains accessible to cloud services continuously.
Open the Excel file in Excel Online or Desktop, highlight your data range, and format it as a Table (Insert > Table). Assign the table a clear name.
Log into Power Automate, click 'Create', and choose 'Automated cloud flow'. Select 'When a new response is submitted' under Microsoft Forms as your trigger.
Add a new action called 'Add a row into a table' (Excel Online Business). Select your cloud location, file, and the Table you created, then map the Microsoft Form dynamic content to the corresponding Excel columns.
Open Power BI Desktop, click 'Get Data', select 'Web' or 'SharePoint folder' (to link to the cloud-hosted Excel file), and load the table. Build your visuals and publish the report.
Clean and Prepare Exported Form Data with WPS Office
While Power BI and Power Automate manage the automated data pipeline, WPS Spreadsheet is an excellent, free desktop application for reviewing, cleaning, and formatting your exported Microsoft Forms Excel files locally before uploading them to the cloud.
- 1. Open the Exported File: Launch WPS Spreadsheet, go to Menu > Open, and select the .xlsx file downloaded from Microsoft Forms.
- 2. Format as Table: Select the data range including headers, navigate to the Home tab, and click 'Format as Table' to prepare it for Power Automate integration.
- 3. Save and Upload: Save the file in .xlsx format and upload it to your OneDrive or SharePoint directory to act as the data source for Power BI.
Frequently Asked Questions
Why isn't Power Automate updating my Excel file with new Microsoft Forms responses?
This usually happens if the data in your Excel file is not formatted as a Table. Open the file, select your data range, press Ctrl+T (or use the ribbon) to create a Table, and reconfigure your Power Automate flow to target this specific Table.
Can I connect Microsoft Forms directly to Power BI without using Excel?
Currently, there is no native direct connector for Microsoft Forms in Power BI. Using an intermediary database, SharePoint list, or Excel file stored on OneDrive/SharePoint is the standard method for establishing this connection.
How do I ensure Power BI automatically refreshes the new data added by Power Automate?
You need to publish your Power BI report to the Power BI Service and set up a Scheduled Refresh in the dataset settings. This allows Power BI to periodically pull the updated rows from your cloud-hosted Excel file.
