Final Friday Fix: February 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 concerns how to take a list of grouped value ranges, where each group contains a mix of individual values and value ranges, and unstack it into a Table.  The objective is to expand the ranges into individual entries whilst ensuring that each value remains associated with its respective group.

As an example:

Imagine a single-column dataset, where each entry contains a mix of grouped value ranges and individual values.  Your challenge is to transform this unorganised dataset (the yellow input table) into a clean, structured Table (the green Table) with clearly defined columns: Group and Value.  The final goal is to create a tabular format where each value is expanded and neatly organised under its corresponding group.

Why does this matter?  Inconsistent datasets like this often appear in the real world, ranging from sales figures to inventory logs or data extracted from various sources.  Without a clear structure, deriving insights or performing any analysis becomes nearly 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!