Final Friday Fix: April 2022 Challenge

On the final Friday of each month, we are going to set an Excel / Power BI challenge for you to puzzle over so that you can get your “Excel fix”.  Challenge your office colleagues to see who can solve the puzzle quickest.  There are no prizes at this stage: you are playing for bragging rights only!

The Challenge

We have a table called ‘Data’ that contains details of some new items and their suppliers in a clean horizontal structure, as below.  We need to import this data into an inventory management system.

However, the system requires a template that only allows one row per item.  Therefore, the data needs to be laid out as follows.  

In this challenge, the objective is to use Power Query to transform structure of the data from vertical format to horizontal as shown in the pictures above.

You can download the question file here.

As always, there are some requirements:

• repeated columns are listed in the second table of Question Data.  They have to be in that specific order (i.e. ‘Note’ is the last column of every repeated group)
• all column headers need to be dynamic.  If they are changed, the queries still need to work
• the number of suppliers for each item may be varied.

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