Final Friday Fix: January 2025 Challenge

On the final Friday of each month, we set an Excel / Power Pivot / Power Query / Power BI problem for you to puzzle over for the weekend.  On the Monday, we publish a solution.  If you think there is an alternative answer, feel free to email us.  We’ll feel free to ignore you.

The Challenge

This month’s challenge is how to take a list of movies with the released year, movie title, director’s name and genre, and unstack it into a table taking into consideration that not all movies have genres allocated to them.  

Imagine a single-column dataset, as shown in the top left of the image above.  Your challenge is to transform this chaotic dataset into a clean, structured table with clearly defined columns: Year, Movie Title, Director(s) and Genre(s).  The column headers to structure the data are provided in a separate table, as shown on the right side of the image.  Using these headers, the final goal is to create a tabular format, as illustrated in the final table at the bottom of the image.

Why does this matter?  Inconsistent datasets like this often crop up in the real world—ranging from survey results to logs or scraped data.  Without a clean structure, deriving insights or performing any analysis is next to impossible. 

You can download the original question file here.

This month’s solution should adhere to the following requirements:

• no VBA is allowed; this is a Power Query challenge
• other solutions using Excel functions or Python in Excel are welcome though!

The goal is to systematically extract and organise this data into structured columns for easier analysis and visualisation.

Sounds easy?  Then why not have a go?  We’ll publish one solution in Monday’s blog.  Have a great weekend in the meantime!