A few days ago, someone asked me to think of a solution to show the responses of a Microsoft Form in Power BI. During this project, I used Forms, Power Automate, Excel, OneDrive, and of course Power BI. I found some interesting obstacles that I want to show you, but first, I start creating a form!
Microsoft Forms can be used to get (customer) feedback, surveys, or registrations. You can ask different kind of questions:
- Multiple choice (including multiple answers)
- Open Answers
- Likert scale
- File Upload
- Net Promoter Score (NPS).
For my project, I had multiple-choice questions, open answers, and Likert scale questions. For this blog, I’ll create a small form including some random example questions.
OneDrive & Excel
The next part is creating a folder in OneDrive, and an Excel file including columns for all questions. I also include two columns: One for the e-mail address of the participant and one for the DateTime it was sent:
As you can see I created three columns starting with Likert. Each of the questions will send a unique result.
Within Power Automate you create a new flow and select a template. When you search “Forms”, you get all kinds of flows related to Microsoft Flows. The one we are looking for is this one:
Now it’s just basically filling in the blanks:
After you mapped all columns, you’re done! It’s time to test the flow! You can do this by selecting Test in the top right corner. While the test is running, I’ll fill the form. After that: Power Automate gives me this result:
Now I’ll fill the form a few times extra to get some extra data and then it’s time for the last chapter: Power BI!
First, we have to create a new report and connect it to our data source. I once learned this trick to connect files which are in the cloud:
In Excel, go to info and copy the file path:
In Power BI, select the From Web connector, paste the path and remove the “?web=1” parameter.
Although a web connector only works with anonymous credentials, you have to enter the credentials of your OneDrive. You then get a warning that it only works with anonymous credentials, but it connects:
In the Query Editor, we can see that there are a few problems.
- There is no Identifier;
- Interest is a string, of multiple values, so I can’t count them.
First the identifier: I duplicate DateTime and Name and then merge these columns:
First I create a reference to the first query and make sure I only keep the CombKey and Interest column. After that, I split the column into rows:
After some cleaning, this is the result:
Now I create a new reference to the previous query and make sure I only keep the Interests column. After removing the duplicates, this is the result:
In the data model I create the following relationship:
Now I can create a visual with Interests from my dimension table, and when I include an easy formula like:
# Response = COUNTROWS(Response)
I can see how many times someone marked for example Azure as an interesting topic.
Now for the Likert questions. This is not a nice way of showing the results:
First of all, the “question” doesn’t make sense at all and it will be very difficult to put these questions in a matrix, showing the scores. So we will create something like before, but this time including some unpivot action.
I duplicate the CombKey_Interest query, but instead of keeping these two columns, I keep the Combkey and Likert columns. Then I rename these columns so they make a bit more sense. The next part is to unpivot the columns and rename them to Question and Answer.
The next part is to create a dimension with only questions, the same way as Interest. After that, you create the same relationships as before and now you can make a matrix:
And as you can see, this works perfectly! Or does it? If we add the names to the questions and collapse everything, the responses don’t add up:
This can be solved by creating a Sumx:
# Response Sum = SUMX(LikertQuestion, [# Response])
And here’s the result:
And that’s it! You can use these mechanisms for the other questions too and I hope this article will help you during your journey of Power BI, Power Automate, and Flow!