Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rjm
New Member

Microsoft Form 'Likert' responses in PowerBI

Hello,

 

This question seems to have been asked before, but with no satisfactory answer.

 

I have a Microsoft Form, which includes a 'Likert' table containing various statements, to which the respondent is asked to provide an answer from a standard multiple choice, e.g. 'All the time', 'Some of the time', 'Rarely', 'None of the time' etc.

 

There are quite a few of these questions.  The summary provided within Microsoft Forms uses a stacked bar chart, with the x-axis containing the question and then the stack containing the balance of responses.

 

I would like to replicate the same, given the standard output of the Microsoft Form in a spreadsheet.  I'm using PowerBI, because I have need to filter the data in various different ways, which the reporting within Microsoft Forms does not support.

 

Has anyone achieved this goal?  It seems a pretty basic requirement, which does not seem very straightforward to achieve.

 

Any help much appreciated.

4 REPLIES 4
mparowski
Advocate I
Advocate I

So this isn't a perfect approach, but here's what I did...

 

Like someone else mentioned, in Power Query I first copied the query, Chose Columns to include only the ID and the Likert Items, and then unpivoted the Likert scale so that I had a zillion rows of Attribute/Value pairs.  Then, I added a Conditional Column (cheating using Column from Examples) to give a numeric value to each of my values.  In my case, Strongly Disagree = -2, Disagree = 1, Neither Agree nor Disagree = 0, Agree = 1, Strongly Agree = 2.

 

Loading this into the data model, Power BI auto-detected the ID as a key field and built the relationship between my main table and my "Likert Question" table.

 

To visualize, I just used a horizontal barchart using the Question for the axis and the "Response_Numeric" for the values (using the "Average" summarization), plus a custom X axis label (with a bunch of underscores, since PBI ignored multiple spaces).  I also force-set the X axis so that the Min was -2 and the Max was 2 so that the scale stayed true to the feedback.

 

It doesn't give the full nuance that the native MS Forms tool does and it's not super-pretty, but it does show the gist of the data as you apply filters based on other demographics.  Here's what it looked like (note that this was just a subset of my data, but it highlights how it looks):

LikertVisualization.png

 

Anonymous
Not applicable

Hello, I don't know if the answers above served you but I have a similar problem. I need to link a form template that already has their answers with Power BI. Bring it to me as a table without downloading it in excel.

amitchandak
Super User
Super User

@rjm , You can add power App to power Bi that can act as form. We have done it google sheet and one drive.

https://community.powerbi.com/t5/Power-Platform-Integrations/Power-Apps-Power-BI-Better-together-Wri...

 

One drive few steps changed, refer :

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-use-onedrive-business-links

mahoneypat
Employee
Employee

Yes.  This is doable.  Also, fyi that you can connect directly to the Excel workbook that stores the Form responses (instead of exporting the file every time you want to refresh).  Here are the steps for that:

1. In Forms, "Move" the form to be owned by an existing O365 Group (e.g., a Team)

https://support.microsoft.com/en-us/office/transfer-ownership-of-a-form-921a6361-a4e5-44ea-bce9-c4ed...

2. Export the data from the Form one time (this exports the file and stores in on the SharePoint site supporting your O365 Group/Team

3. Get data from the Excel using SharePoint Folder or Web connector.

 

One you get the data, your Likert responses will be in the column for that question.  Typically, I unpivot all the question columns to simplify analysis, but depending on similarity of your questions, you may not want to.

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.