Analyse survey results with multiple answers using Power Query - Excel and Power BI

Do you have data containing multiple answers in a single column?
Do not just split the column. Use this Power Query approach to give you all types of analysis quickly and easily.

In this video, I will show you how to use Power Query to split a column by a delimiter and create new rows for each value. This is useful when you want to analyze survey results that have multiple responses in one cell.

Contents 🔖
00:00 - Introduction
00:09 - Solution
00:20 - Power Query Transform
00:30 - Add index column
00:40 - Create a reference
00:47 - Split to rows
01:28 - Import into data model
01:45 - Create relationship
02:12 - Analyze with Pivot
02:55 - Multiple column survey data
03:34 - Repeat for each question
05:03 - Correcting mistakes

Buy Dr Nitin's latest book
-----------------------------------------
Efficiency Best Practices for Microsoft 365
130 best practices everyone must know
Work Smarter, Grow Faster

Dr Nitin Paranjape is the pioneer in maximizing work efficiency in a modern work scenario.
Dr Nitin's Office Masterclass
----------------------------------------------

Online Courses by Dr Nitin Paranjape
--------------------------------------------------------------

Learn more from 1000+ blog articles by Dr Nitin Paranjape

Production
--------------------

[KeshavSharma-ybke]

Helped me a lot sir ! Valuable video. I don't usually comment on yt videos but this is a gem !!

[chrishawkes]

Perfect, works great in Power Bi too, I was really stuck with this! 👌

[ayandapeter]

I will always have respect for Dr Nitin, he once spoke about the importance of an official excel table and from that I became interested in MS Excel and Data Analysis, today I'm the Head of Business Intelligence

[Slayonus]

Hi, great video, there is very little good information on this online, so thanks for providing it.

A few questions:
First, when there are multiple multiple-choice questions, why do you make all of them in seperate tables and not just keep splitting them in a single table? Else creating this many tables causes everything to slow down tremendously, especially if you have let's say 30 multiple-choice questions.
Secondly, related to this, how would you go about doing this for very many multiple choice questions?

[الدعوةإلىاللهلاتنسواذكرالله]

What is the vision of this exal? And how to get all these properties?

[tamoghnaacharyya]

My first impression was spilt by comma and perform unpivot. But split by row changed everything. Thanks

[holamala]

Sir, I have a question. How to make yes no item/criteria into the list data as like as data in your thumbnail sir? I want some of tutorial, if this case can do by using query in excel. Thank you 🙏🏻

[narakdk]

how about when the survey give like 4 coloumns for 1 question and sometimes they can answer more than one of those 4?