Final Friday Fix: April 2023 Challenge

On the final Friday of each month, we are going to set an Excel / Power Pivot / Power Query / 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

This month, we have a Power Query challenge.  We have provided a challenge file which includes the following Table of data for our imaginary tent salespeople:

One of our esteemed salespeople has been promoted to be the manager of the team.  This information appears in a Named Range pointing at cell $D$13:

You can download the challenge file here.

This month’s challenge is to combine the Table with the information in the Named Range, and present it in a new Table:

As always, there are some conditions:

• This is a Power Query challenge - No Excel formulae allowed!
• No hard-coding should be used to determine the contents or the name of column Manager.

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