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 return 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:

• A HTTP GET trigger which will provide the Endpoint for Power BI
• An iterator starting at 0 and being incremented on each loop
• A DO UNTIL loop which will stop when Get response details returns NULL
• An Array which will hold all answers
• Trim the final array by removing the excess with: take(variables('responses'),sub(variables('counter'),2))
• Finally consume the power automate flow through Power BI by creating a new data source based on Web data and point to the GET trigger endpoint

The full microsoft power automate flow is: