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

Thanks @KeshavSharma-yb9ke Check out my popular Excel videos How to enter and edit Excel Formulas - Back to Basics - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-rtDlORLmjlE.html Excel Green Marks - Error Checking - Best Practices - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-XeLtlzd9lRY.html Excel Best Practices - Part 1 of 3 - Data Management - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-rr_1ha6g6lc.html Excel Best Practices - Part 2 of 3 - Formulas - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-kz_zAvMINAk.html Excel Best Practices - Part 3 of 3 - Analytics - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-JNR5yx_Pg4A.html 10 Excel Settings You Must CHANGE! - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-vXrrXdKyJFk.html Automatic data clean up with Excel Flash Fill - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-N3p_x_lXT_c.html Instant Excel Audit, Comparison and Analysis - Inquire - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-cDdvUZxOUis.html Six powerful Excel Navigation Shortcuts - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-bR-yMbGPq50.html Handle millions of rows in Excel - Slow to fast - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-93h7rRsLF7Y.html Convert crosstab to tabular - Unpivot - Excel Power Query - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-mTtOtZH-nNM.html Cheers. Doc

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

Thanks @waleadeniji6959 Check out my popular Excel videos How to enter and edit Excel Formulas - Back to Basics - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-rtDlORLmjlE.html Excel Green Marks - Error Checking - Best Practices - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-XeLtlzd9lRY.html Excel Best Practices - Part 1 of 3 - Data Management - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-rr_1ha6g6lc.html Excel Best Practices - Part 2 of 3 - Formulas - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-kz_zAvMINAk.html Excel Best Practices - Part 3 of 3 - Analytics - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-JNR5yx_Pg4A.html 10 Excel Settings You Must CHANGE! - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-vXrrXdKyJFk.html Automatic data clean up with Excel Flash Fill - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-N3p_x_lXT_c.html Instant Excel Audit, Comparison and Analysis - Inquire - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-cDdvUZxOUis.html Six powerful Excel Navigation Shortcuts - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-bR-yMbGPq50.html Handle millions of rows in Excel - Slow to fast - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-93h7rRsLF7Y.html Convert crosstab to tabular - Unpivot - Excel Power Query - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-mTtOtZH-nNM.html Cheers. Doc

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 : ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-sG7HHOjNfkY.html

@@efficiency365 Thanks for the reply! Very helpful

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 - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-rtDlORLmjlE.html Excel Green Marks - Error Checking - Best Practices - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-XeLtlzd9lRY.html Excel Best Practices - Part 1 of 3 - Data Management - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-rr_1ha6g6lc.html Excel Best Practices - Part 2 of 3 - Formulas - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-kz_zAvMINAk.html Excel Best Practices - Part 3 of 3 - Analytics - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-JNR5yx_Pg4A.html 10 Excel Settings You Must CHANGE! - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-vXrrXdKyJFk.html Automatic data clean up with Excel Flash Fill - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-N3p_x_lXT_c.html Instant Excel Audit, Comparison and Analysis - Inquire - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-cDdvUZxOUis.html Six powerful Excel Navigation Shortcuts - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-bR-yMbGPq50.html Handle millions of rows in Excel - Slow to fast - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-93h7rRsLF7Y.html Convert crosstab to tabular - Unpivot - Excel Power Query - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-mTtOtZH-nNM.html Cheers. Doc

@@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 : )

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 - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-rtDlORLmjlE.html Excel Green Marks - Error Checking - Best Practices - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-XeLtlzd9lRY.html Excel Best Practices - Part 1 of 3 - Data Management - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-rr_1ha6g6lc.html Excel Best Practices - Part 2 of 3 - Formulas - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-kz_zAvMINAk.html Excel Best Practices - Part 3 of 3 - Analytics - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-JNR5yx_Pg4A.html 10 Excel Settings You Must CHANGE! - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-vXrrXdKyJFk.html Automatic data clean up with Excel Flash Fill - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-N3p_x_lXT_c.html Instant Excel Audit, Comparison and Analysis - Inquire - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-cDdvUZxOUis.html Six powerful Excel Navigation Shortcuts - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-bR-yMbGPq50.html Handle millions of rows in Excel - Slow to fast - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-93h7rRsLF7Y.html Convert crosstab to tabular - Unpivot - Excel Power Query - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-mTtOtZH-nNM.html Cheers. Doc

Thanks @user-wf2pg3si5c Check out my other best practices videos as well. Efficient Tasks Management - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-vLFpWVfUfQ8.html Task Apps Comparison: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-ViWJIzMPQZg.html Teams Meeting - Agenda, Action Points, Notes - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-k0t8_mNkMDw.html Microsoft Teams - 15 Best Practices - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-W8Ufx_znKxI.html OneDrive Best Practices Part 1 - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-D7ZrfphW4vo.html Part 2 - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-nFx6YQxc-b4.html Smart and Effective Email - 5 powerful ways - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-6NEFSMqHgQE.html Outlook Calendar Best Practices : Part 1 - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-GzsQnecjAQo.html and Part 2 - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-padKH8ys5Cs.html OneNote - Best Practices - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-m-4AY1cMi8s.html Microsoft 365 Best Practices - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-kVC_YcL5ObU.html Cheers. Doc.

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

@@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 !

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.

Thanks Matt. Watch Excel Custom Sorting Part 1 and 2 - you will like it: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-MSj9rb633BM.html and ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-2oReiB8kgTw.html Cheers. Doc.

Thanks Roll TheRs. Check out my other best practices videos as well. Efficient Tasks Management - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-vLFpWVfUfQ8.html Microsoft Teams - 15 Best Practices - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-W8Ufx_znKxI.html Smart and Effective Email - 5 powerful ways - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-6NEFSMqHgQE.html Outlook Calendar Best Practices : Part 1 - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-GzsQnecjAQo.html and Part 2 - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-padKH8ys5Cs.html OneNote - Best Practices - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-m-4AY1cMi8s.html Microsoft 365 Best Practices - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-kVC_YcL5ObU.html Word Tables Best Practices - The ultimate guide - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-ueKCHxNUOkA.html Cheers. Doc.