by Wyn Hopkins

Challenge of the month – Flex your Power Query Skills

From time to time, we post fun, technical challenges in Excel & Power BI.

For this one, take the source data in the blue table and turn it into the format shown in the green table.

Your solution needs to automatically handle extra Projects & Allocation columns being added in future.

Assume they’re added with the same format as shown here.

If there is more than one project, the Final GL Code needs to show [Code]|A, [Code]|B etc.

If 100% is allocated to just one project, the Final GL Code column should just show the GL code.

Accept the challenge below with our free download.

 

 

Our solution…

The pick of the bunch

Here are some of the approaches people took including Wyn Hopkins’ solution and our top pick by Peter Tholstrup:

• Peter Tholstrup
• Jerry Ball
• Phil Whyman
• Pavel Adam
• Ivan Zhelyazkov
• Owen Auger
• Bill Szysz

Extra Kudos to Pavel who did the entire process in 1 applied step! following a comment Wyn made on LinkedIn about the number of steps.

Thanks to everyone for participating & stay tuned for the next one!