How to export Microsoft Forms Answers to PowerBI
A lot of companies we helped to optimize Microsoft 365 license costs had some sort of Microsoft Form survey running. When they wanted to have a better understanding of the survey responses using Power BI they couldn't export the data to a dataflow without losing the existing data. So we gave them a hand and decided to share our solution.
Most of the usual solutions include a complex setup which involves sharepoint lists or re-creating a new Microsoft Form, this one will work with existing forms and you only need a flow to export the content to Power BI
The Microsoft Power automate flow to export Form answers
The key for getting the Microsoft Form answers in power BI is a power automate action called "Get response details". This action receives a Microsoft Form ID along with a Response ID and outputs a JSON with the response.
The great thing about Response ID is that it's
sequential, so if you have 20 answers you can simply export them by iterating from 1 to 20.
The other great thing is that if you go over the number of total answers the action will eventually
NULL which is perfect for a while stop condition
So, to summarize, in order for you to export a Microsoft Form answers to Power BI you just need:
HTTP GETtrigger which will provide the
Endpointfor Power BI
- An iterator starting at 0 and being incremented on each loop
DO UNTIL loopwhich will stop when Get response details returns
Arraywhich will hold all answers
- Trim the final array by removing the excess with:
- Finally consume the power automate flow through
Power BI by creating a new data source based on Web data and point to the
The full microsoft power automate flow is: