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

[@KeshavSharma-yb9ke]

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

[@efficiency365]

Thanks @KeshavSharma-yb9ke Check out my popular Excel videos How to enter and edit Excel Formulas - Back to Basics - kzbin.info/www/bejne/qKWnnYKIgdKdoqc Excel Green Marks - Error Checking - Best Practices - kzbin.info/www/bejne/jpavpZ-wmZ6fiLs Excel Best Practices - Part 1 of 3 - Data Management - kzbin.info/www/bejne/qKPCYpuXa8xposU Excel Best Practices - Part 2 of 3 - Formulas - kzbin.info/www/bejne/oavCq3Ssgq6Bd80 Excel Best Practices - Part 3 of 3 - Analytics - kzbin.info/www/bejne/gH-1ZqyulLWaaqM 10 Excel Settings You Must CHANGE! - kzbin.info/www/bejne/rInVo4uagN59fM0 Automatic data clean up with Excel Flash Fill - kzbin.info/www/bejne/hGTTkKuVob2HlcU Instant Excel Audit, Comparison and Analysis - Inquire - kzbin.info/www/bejne/mXXHp4iQrbSIn9U Six powerful Excel Navigation Shortcuts - kzbin.info/www/bejne/mIOQqoCYfLWka5I Handle millions of rows in Excel - Slow to fast - kzbin.info/www/bejne/b2TLaKWIqLF5bbs Convert crosstab to tabular - Unpivot - Excel Power Query - kzbin.info/www/bejne/o4XXgKeQfZKhhK8 Cheers. Doc

[@chrishawkes4268]

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

[@ayandapeter1681]

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

[@efficiency365]

Wow. It is heartening and encouraging to know about your growth and success. It is highly motivating to know the impact of the content I create. More power to you 🙏🏼 Cheers. Doc.

[@austinegwada7739]

perfect short and on target

[@waleadeniji6959]

Awesome.... explanation is on point

[@efficiency365]

Thanks @waleadeniji6959 Check out my popular Excel videos How to enter and edit Excel Formulas - Back to Basics - kzbin.info/www/bejne/qKWnnYKIgdKdoqc Excel Green Marks - Error Checking - Best Practices - kzbin.info/www/bejne/jpavpZ-wmZ6fiLs Excel Best Practices - Part 1 of 3 - Data Management - kzbin.info/www/bejne/qKPCYpuXa8xposU Excel Best Practices - Part 2 of 3 - Formulas - kzbin.info/www/bejne/oavCq3Ssgq6Bd80 Excel Best Practices - Part 3 of 3 - Analytics - kzbin.info/www/bejne/gH-1ZqyulLWaaqM 10 Excel Settings You Must CHANGE! - kzbin.info/www/bejne/rInVo4uagN59fM0 Automatic data clean up with Excel Flash Fill - kzbin.info/www/bejne/hGTTkKuVob2HlcU Instant Excel Audit, Comparison and Analysis - Inquire - kzbin.info/www/bejne/mXXHp4iQrbSIn9U Six powerful Excel Navigation Shortcuts - kzbin.info/www/bejne/mIOQqoCYfLWka5I Handle millions of rows in Excel - Slow to fast - kzbin.info/www/bejne/b2TLaKWIqLF5bbs Convert crosstab to tabular - Unpivot - Excel Power Query - kzbin.info/www/bejne/o4XXgKeQfZKhhK8 Cheers. Doc

[@papayuyu1189]

Thank you so much!

[@efficiency365]

Thanks @papayuyu1189 Check out my popular Excel videos How to enter and edit Excel Formulas - Back to Basics - kzbin.info/www/bejne/qKWnnYKIgdKdoqc Excel Green Marks - Error Checking - Best Practices - kzbin.info/www/bejne/jpavpZ-wmZ6fiLs Excel Best Practices - Part 1 of 3 - Data Management - kzbin.info/www/bejne/qKPCYpuXa8xposU Excel Best Practices - Part 2 of 3 - Formulas - kzbin.info/www/bejne/oavCq3Ssgq6Bd80 Excel Best Practices - Part 3 of 3 - Analytics - kzbin.info/www/bejne/gH-1ZqyulLWaaqM 10 Excel Settings You Must CHANGE! - kzbin.info/www/bejne/rInVo4uagN59fM0 Automatic data clean up with Excel Flash Fill - kzbin.info/www/bejne/hGTTkKuVob2HlcU Instant Excel Audit, Comparison and Analysis - Inquire - kzbin.info/www/bejne/mXXHp4iQrbSIn9U Six powerful Excel Navigation Shortcuts - kzbin.info/www/bejne/mIOQqoCYfLWka5I Handle millions of rows in Excel - Slow to fast - kzbin.info/www/bejne/b2TLaKWIqLF5bbs Convert crosstab to tabular - Unpivot - Excel Power Query - kzbin.info/www/bejne/o4XXgKeQfZKhhK8 Cheers. Doc

[@mattsnow2376]

This is awesome Dr. P.

[@efficiency365]

Thanks Matt. Watch Excel Custom Sorting Part 1 and 2 - you will like it: kzbin.info/www/bejne/g4TNaqWYa5hmeK8 and kzbin.info/www/bejne/aKC1lpx4bdCaitk Cheers. Doc.

[@rollthers3157]

Excellent!

[@efficiency365]

Thanks Roll TheRs. Check out my other best practices videos as well. Efficient Tasks Management - kzbin.info/www/bejne/rH2poYqMm7qZh5o Microsoft Teams - 15 Best Practices - kzbin.info/www/bejne/jWm4l6uVr9N-rqs Smart and Effective Email - 5 powerful ways - kzbin.info/www/bejne/bH-od4aDpq2ah6c Outlook Calendar Best Practices : Part 1 - kzbin.info/www/bejne/favWgqGbmM90h9E and Part 2 - kzbin.info/www/bejne/ppLHfHturthoedU OneNote - Best Practices - kzbin.info/www/bejne/o16XcoxnmLKcbtU Microsoft 365 Best Practices - kzbin.info/www/bejne/oYemkIyZgZqCmLc Word Tables Best Practices - The ultimate guide - kzbin.info/www/bejne/q5audHuug7qCoaM Cheers. Doc.

[@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?

[@efficiency365]

1. Why make separate table? The reason is that every time you split to rows, the number of rows multiplies. If you repeat this process for multiple columns, the number of rows expands exponentially. Even if you can manage the analysis with increased rows, filtering on individual answer item becomes difficult in UI. Lastly, creating separate tables creates a more optimal data structure which performs faster. (This is normalized design. Everything to rows is denormalized table) 2. How to make separate tables automatically? Conceptually, this is possible using some Power Query coding. I will research this and if there is a solution, I will publish it soon. Check out this video as well : kzbin.info/www/bejne/qXiaeXuFn7OZobs

[@Slayonus]

@@efficiency365 Thanks for the reply! Very helpful

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

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

[@efficiency365]

Thanks @user-wf2pg3si5c Check out my other best practices videos as well. Efficient Tasks Management - kzbin.info/www/bejne/rH2poYqMm7qZh5o Task Apps Comparison: kzbin.info/www/bejne/jJq6e3ywgrWEkMk Teams Meeting - Agenda, Action Points, Notes - kzbin.info/www/bejne/oWHXaZKjg9CAetk Microsoft Teams - 15 Best Practices - kzbin.info/www/bejne/jWm4l6uVr9N-rqs OneDrive Best Practices Part 1 - kzbin.info/www/bejne/emi9o5mmnbxnrNE Part 2 - kzbin.info/www/bejne/pHfbZ4yHrchgmJY Smart and Effective Email - 5 powerful ways - kzbin.info/www/bejne/bH-od4aDpq2ah6c Outlook Calendar Best Practices : Part 1 - kzbin.info/www/bejne/favWgqGbmM90h9E and Part 2 - kzbin.info/www/bejne/ppLHfHturthoedU OneNote - Best Practices - kzbin.info/www/bejne/o16XcoxnmLKcbtU Microsoft 365 Best Practices - kzbin.info/www/bejne/oYemkIyZgZqCmLc Cheers. Doc.

[@canirmalchoudhary8173]

Simple and amazing

[@efficiency365]

Split to rows is ok if there was ONE field. With multiple fields, you end up with too many rows unnecessarily.

[@narakdk]

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

[@efficiency365]

Thanks @narakdk That is multi-select. That is the exact option shown here. Check out my popular Excel videos How to enter and edit Excel Formulas - Back to Basics - kzbin.info/www/bejne/qKWnnYKIgdKdoqc Excel Green Marks - Error Checking - Best Practices - kzbin.info/www/bejne/jpavpZ-wmZ6fiLs Excel Best Practices - Part 1 of 3 - Data Management - kzbin.info/www/bejne/qKPCYpuXa8xposU Excel Best Practices - Part 2 of 3 - Formulas - kzbin.info/www/bejne/oavCq3Ssgq6Bd80 Excel Best Practices - Part 3 of 3 - Analytics - kzbin.info/www/bejne/gH-1ZqyulLWaaqM 10 Excel Settings You Must CHANGE! - kzbin.info/www/bejne/rInVo4uagN59fM0 Automatic data clean up with Excel Flash Fill - kzbin.info/www/bejne/hGTTkKuVob2HlcU Instant Excel Audit, Comparison and Analysis - Inquire - kzbin.info/www/bejne/mXXHp4iQrbSIn9U Six powerful Excel Navigation Shortcuts - kzbin.info/www/bejne/mIOQqoCYfLWka5I Handle millions of rows in Excel - Slow to fast - kzbin.info/www/bejne/b2TLaKWIqLF5bbs Convert crosstab to tabular - Unpivot - Excel Power Query - kzbin.info/www/bejne/o4XXgKeQfZKhhK8 Cheers. Doc

[@narakdk]

@@efficiency365 If I understand that video correct, then it is not possible afterwards the unpivoting to cross-reference (interact) with other questions and their responses, because they are no longer in the same row of the dataset but on each own row in the same coloumn. I have solved it by making a new datatable for each multi-question and link it together, but I just think there must be an easier solution : )

[@tamoghnaacharyya3717]

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

[@efficiency365]

Yes. My general rule is - the first thought which comes to mind is usually inefficient! (At least in case of Office apps)

[@tamoghnaacharyya3717]

@@efficiency365 The video was so useful I could immediately apply the concept in my data. Checking the spell error by removing the duplicates was profound.. Thank you very much !

[@efficiency365]

As you learn more, you will realize that each feature has multiple use case. Just because you found the obvious (and originally intended) use case should not constrain you from thinking of more relevant scenarios.

[@holamala8691]

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 🙏🏻

[@efficiency365]

Thanks @holamala8691 If yes / no, only one answer at a time. It will work just fine. No need to split and unpivot. Check out my popular Excel videos How to enter and edit Excel Formulas - Back to Basics - kzbin.info/www/bejne/qKWnnYKIgdKdoqc Excel Green Marks - Error Checking - Best Practices - kzbin.info/www/bejne/jpavpZ-wmZ6fiLs Excel Best Practices - Part 1 of 3 - Data Management - kzbin.info/www/bejne/qKPCYpuXa8xposU Excel Best Practices - Part 2 of 3 - Formulas - kzbin.info/www/bejne/oavCq3Ssgq6Bd80 Excel Best Practices - Part 3 of 3 - Analytics - kzbin.info/www/bejne/gH-1ZqyulLWaaqM 10 Excel Settings You Must CHANGE! - kzbin.info/www/bejne/rInVo4uagN59fM0 Automatic data clean up with Excel Flash Fill - kzbin.info/www/bejne/hGTTkKuVob2HlcU Instant Excel Audit, Comparison and Analysis - Inquire - kzbin.info/www/bejne/mXXHp4iQrbSIn9U Six powerful Excel Navigation Shortcuts - kzbin.info/www/bejne/mIOQqoCYfLWka5I Handle millions of rows in Excel - Slow to fast - kzbin.info/www/bejne/b2TLaKWIqLF5bbs Convert crosstab to tabular - Unpivot - Excel Power Query - kzbin.info/www/bejne/o4XXgKeQfZKhhK8 Cheers. Doc