Monday Morning Mulling: 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

Last Friday’s challenge involved transforming a dataset with grouped value ranges into a structured Table by expanding the ranges into individual rows whilst maintaining their corresponding group labels.

As an example:

The single-column dataset (the yellow table) contained a mix of grouped value ranges and individual values.  Your challenge was to transform this unorganised dataset into a clean, structured Table (the green Table) with clearly defined columns: Group and Value.

Why did 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 could download the original question file here.

Remember, the solution had to adhere to the following:

• no VBA was allowed; this was a Power Query challenge
• other solutions using Excel functions or Python in Excel were welcome.

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

Suggested Solution

You can find our Excel file here, which shows our suggested solution.  The steps are detailed below.

Power Query Solution

The following steps outline how to resolve the challenge using Power Query, leveraging dynamic and reusable techniques to clean and restructure the data.

Firstly, we need to load the source data.  Select the ‘SplitValueRanges’ table (our name for the input tale) with the source data in a single column.  Click on any cell within the table, go to the Data tab in the Ribbon and click on ‘From Table/Range’ within ‘Get & Transform Data’ group.

When the Power Query Editor window appears, rename the query by entering ‘Data’ into the ‘Name’ field within the Query Settings pane.

Next, we will split the Group column.  The first step is to split it by commas to separate multiple values into individual entries.  To do this, select the Group column.  In the Power Query Ribbon, click ‘Split Column’button within ‘Transform’ group, then click ‘By Delimiter’.

In the ‘Split Column by Delimiter’ dialog, select ‘--Custom--’ and enter “, ” (with a space after the comma).  Expand ‘Advanced options’ and select ‘Rows’, then click ‘OK’. 

It should then look like the following screenshot: 

Next, we continue splitting the Group column, this time separating the alphabetic characters from the numeric values to create a clearer structure.  In the Power Query Ribbon, click ‘Split Column’ button within ‘Transform’ group, then click ‘Non-Digit to Digit’.

To streamline the process and prepare for the next steps, click on the Formula Bar and manually rename the column headers by modifying the following formula:

to

It should then look like the following screenshot: 

Then, we need to remove the hyphens.  In the Power Query Ribbon, click ‘Replace Values’ button within ‘Any Column’ group.

In the dialog box ‘Replace Values’, enter "-" in ‘Value to Find’ and blank in ‘Replace With’.  Click ‘OK’.

 It should then look like the following screenshot: 

The next step is to change the data type for the Start and End columns to convert the numbers into correct ‘Whole Number’ data type.  To do this, click the icon next to the Start header, select Whole Number, and repeat the same process for the End column.

It should then look like the following screenshot: 

The next step is to generate a list of ranges for each row.  We create a list of values based upon the Start and End columns.  If End is null, it returns only the Start value as a single-item list.  Otherwise, it generates a sequence from Start to End, ensuring both individual values and ranges are correctly captured.  To do this, we add a column by clicking ‘Custom Column’ under ‘Add Column’ Tab.  Please note that Power Query is case sensitive.  In the ‘Custom Column’ dialog, enter the column name as Value and then the formula as below:

Click ‘OK’ and it should then look like the following screenshot: 

Now the Value has been created, if we click at the blank area next to each ‘List’, we will see the preview of the range list appears at the bottom area like this:

Since the Start and End values are already included in the generated list, we can remove these columns from the table.  Hold CTRL while selecting the Group and Value columns.  Then, right-click on any of the selected headers and choose ‘Remove Other Columns’.

It should then look like the following screenshot:

Then we need to expand Value.  We can expand the Value downward into rows by selecting icon next to Value header and select ‘Expand to New Rows’.

It should then look like the following screenshot:

Again, the new Value column has been set to defalt format, we have to change it to the whole number.  To do this, click the icon next to the Value column header, select ‘Whole Number’.

After completing the steps, refresh the queries to ensure all changes are applied, click ‘Close & Load’.

We appreciate there are many, many ways this could have been achieved.  If you have produced an alternative, radically different approach, congratulations – that’s half the fun of Excel!

The Final Friday Fix will return on Friday 28 March 2025 with a new Excel Challenge.  In the meantime, please look out for the Daily Excel Tip on our home page and watch out for a new blog every business working day.