FASTEST Method for Reporting on SURVEY Data in Power BI

Рет қаралды 7,580

Күн бұрын

This technique unpivots Microsoft Forms data in Power Query to combine responses into a single column in Power BI. This lets us calculate percent of total and counts of responses by question without having to create a measure and visual for every question.
We connect to the Microsoft Forms data in SharePoint in this example, which we can schedule refresh on, but it will work survey data from other sources as well.
Here’s some relevant follow-up videos!
You could apply row-level security with your organizational hierarchy to show managers data for people who report to them with this technique:
• Dynamic Row-Level Secu...
Schedule refresh on SharePoint file sources (which MS Forms is!):
• How to Schedule REFRES...
0:00 Intro/demo
1:48 Connect to the data
4:16 Shape and clean the data
7:38 Create the measures
9:50 Configure the visual
12:50 Create a score “out of” a number
14:30 Other ideas

Пікірлер: 18

@bi-ome 4 ай бұрын

Someone requested the files for this video - here's the github link (this is the version in the intro, I happened not to save the exact one in the walkthrough - they're very close though): github.com/chpayton/BI-Files/tree/main/Survey%20Data

@mrneveradullmoment Ай бұрын

THANK YOU for the full walk through. I can't tell you how hard this is to find...

@maheshgupte1495 4 ай бұрын

One more awesome tutorial. Thank you for teaching us.

@Nalaka-Wanniarachchi 4 ай бұрын

Nice one. Bit of a few codes with ALLEXCEPT, % of Total = var countResponse = COUNTROWS('Responses') var allResponsesForQuestion = CALCULATE(COUNTROWS('Responses'), ALLEXCEPT(Responses,Responses[Question])) RETURN DIVIDE(countResponse, allResponsesForQuestion)

@zzota 4 ай бұрын

So quick and easy! Thanks Christine 🙂

@bi-ome 4 ай бұрын

You bet!

@michaelcrow700 2 ай бұрын

Very helpful - thank you

@user-le9iv1de2j 25 күн бұрын

This was a great Video. Thanks for uploading it. I am still new to Power BI but wanted to know if it was possible to set this up a template or model for future surveys? I'd like to spend time positioning graphs and charts, but can it be re used for a new survey. It would be the same exact one, but new answers from say a different department. Or will the fact that we need to create a new non table version of the data cause issues? Thank you.

@bi-ome 25 күн бұрын

How you set it up depends on how much manual intervention you're ok with. The least intervention would be the SP folder connector for Power BI, where it will pull all files in a folder. You can add filters to that for the file type, path, naming convention (e.g. contains "survey"). As long as you're using "unpivot other" and selecting the fields that will be the same every time when you unpivot the rest, it will be able to pull in all the data into one table. You'd have the filename associated with the survey, which should have the survey name in it to help tell responses apart. If you wanted to transform that to be a bit friendlier format you could split off the extension or something in the query editor.

@wwpharmacist 2 ай бұрын

Thank you What if I have multiple selection in the columns separated by semicolon, is Split in to rows will help, I analyzing stack overflow data set survey for 2023 and trying to solve these Columns but the data size increases dramatically, any suggestions Thanks

@bi-ome Ай бұрын

Anytime you have multivalue columns it adds a lot to the complexity. I usually put the multivalue column and the row ID into its own table, then split to new rows, and relate the two tables on the ID field. You may need to set the relationship to bidirectional depend on what you're visualizing. "Big" is relative in Power BI - it's able to handle millions of rows with no problems as long as you model it cleanly. Particularly with the multivalue field data, where those values tend to be basically the same values repeated lots of times (as opposed to paragraphs of unique text from open text fields), it usually doesn't actually take up much space at all in the model. It's the unique text that will really bog it down

@michelebuzzi4545 4 ай бұрын

I know this comment is completely out of context, but have you by any chance experienced issues with the new February version related to the message 'Cube Table must have exactly one island'?

@bi-ome 4 ай бұрын

No, I haven't - it looks like it's definitely a thing others are running into though, from this thread: community.fabric.microsoft.com/t5/Power-Query/Power-BI-error-quot-Cube-Table-must-have-exactly-one-island-quot/td-p/3089145

@maheshgupte1495 4 ай бұрын

Dear Cristine, I hope you are doing well, need one help in one of my power App Project. where I want to provide a user to 1- Download a Excel template 2 Fill the template 3 User should be able upload the template using power App. 4 and patch the records to database MS lists I found 2 Tutorial on KZfaq but it confusing to me please check if you can help me with this requirement it will be great help. Tutorial Link : 1- kzfaq.info/get/bejne/e65_gbqcvreyd3U.html 2- kzfaq.info/get/bejne/qZuUicKfuLumdI0.html

@bi-ome 4 ай бұрын

I think you probably want to use a Library instead of a List, if the record is Excel files. Libraries have the concept of file templates, that users can create files from via the +new menu, so you might not even need a Power App? They wouldn't have to download it, they'd just create the file from the template via the UI and edit it right in SharePoint. But, I have a tendency to not use Power Apps unless it's absolutely necessary, so I WOULD say that lol. (support.microsoft.com/en-us/office/set-a-custom-template-for-a-sharepoint-library-8e0177eb-46bd-4346-9998-330c32733c25)

@maheshgupte1495 4 ай бұрын

@@bi-ome Thank you very much for your response. have a great day.