Create Variable Columns in Power Query – Goodly Challenge – The First Class Edition

/in /by

Here is an interesting challenge. With a column of names, and a column of musical preferences and each person has multiple music preferences, how can the preferences be split such that you have TRUE if a person has that preference, and FALSE if a person does not?

One twist: what if there’s a person added/removed or a music preference is added/removed?

This challenge came from Chandeep Chhabra at the Goodly YouTube channel.
My solution in Power Query includes:
Left Outer Join with 2 criteria
Cross Join
Conditional Column
Pivot Don’t Aggregate

All handy features that can help you create dynamic solutions in Excel and Power Query.

See the challenge as described by Chandeep: https://youtu.be/7Vow1L8Mu9g

0:00 Intro
0:54 Explaining the challenge
2:15 Starting the solution
4:39 Cross Join
6:34 Merge queries with 2 criteria
8:29 Pivot Don’t Aggregate
9:50 Add more data
10:49 Outro

#ExcelChallenge #FirstClass #PowerQuery

For a list of my Excel courses at Lynda/LinkedIn:
https://www.linkedin.com/learning/instructors/oz-du-soleil
There are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 3rd Edition
https://www.mrexcel.com/products/guerrilla-data-analysis-3rd-edition/
My old blog: http://datascopic.net/blog-2-2