Forum Discussion
Microsoft Form Multiple Choice to Power BI: Standardising Answers
Hi
I have a list of over a 1000 stakeholders that I need people using my Microsoft Forms to input correctly so this data can then be pulled onto my Power BI dashboard. My issue is that Microsoft Forms have a limit on multiple choice options. Does anyone have a way that if I have it open text for Power BI or Excel to automatically tidy it up to match with my approved list of stakeholders. The trouble currently is that when someone enters a stakeholder name with even a capital letter missing it will create a new stakeholder on the list on the dashboard. Would appreciate any ideas to work around this issue? Thank you!
Hi Anonymous You can use Power Query in Power BI to clean and standardize names, applying transformations like capitalization and fuzzy matching to align entries with your approved list. Alternatively, use Excel Data Validation to restrict inputs or highlight mismatches for correction. Power Automate can preprocess entries by comparing and standardizing names before they reach Power BI.
8 Replies
- Akash_Varuna
Super User
Hi Anonymous You can use Power Query in Power BI to clean and standardize names, applying transformations like capitalization and fuzzy matching to align entries with your approved list. Alternatively, use Excel Data Validation to restrict inputs or highlight mismatches for correction. Power Automate can preprocess entries by comparing and standardizing names before they reach Power BI.
Thanks Akash_Varuna , these are really interesting suggestions. Do you know of any good tutorial videos or anything on how to set up Power Query to clean up and standardise the names before they hit Power BI? I tried google and YouTube but I'm not finding what I'm looking for.
Appreciate everyones help on this!
- Akash_Varuna
Anonymous Coudl you check this maybe this might be helpful for power query
https://www.youtube.com/watch?v=jf0SMos9g4c
- collinq
Hey Anonymous ,
If you don't want to use Power Automate or other tools, you could use Fuzzy Match in Power BI to make this happen. And, I am wondering if you might be able to have your form use a pre-existing stakeholder list and make it a selection so they can't free form it.
Thanks collinq, so the trouble is I have a stakeholder list with thousands of businesses and organisations and the multiple choice limit on Microsoft Forms means this option isn't really feasible. It's really frustrating cause if there was a drop down list with unlimited multiple choice then this would be a great solution.
Hi Anonymous,
Thanks for reaching out to the Microsoft fabric community forum.
Just following up to your previous conversation with collinq and Akash_Varuna, I'd like to confirm if you've successfully resolved this issue or if you need further help.
If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.
I would also take a moment to thank collinq and Akash_Varuna, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
If I misunderstand your needs or you still have problems on it, please feel free to let us know.
Best Regards,
Hammad.
Community Support TeamIf this post helps then please mark it as a solution, so that other members find it more quickly.
Thank you.
