Expand Columns to Multiple Rows by Delimiter – Power Query Challenge

Every consulting assignment tricks me into some or the other peculiar data problem. If the problem is interesting enough, I pass it on to you guys.

Let’s start!

Power Query Challenge – Video

Consider this Hierarchy!

Locations → Businesses → Departments

• Locations are like – India, USA, UK
• Businesses are like – Trading, Heavy Equipment, Consulting, Manufacturing
• Departments are like – Sales, Finance, HR, IT, Legal, Ops

Note – All locations have all businesses and all businesses have all departments. So it’s like a cross join!

Now consider this Data!

Here is the story

• A company incurs cost that should be allocated to Locations, Businesses and Departments.
• The allocated Locations, Businesses and Departments are mentioned in their respective columns.

Expected Output – For instance Row 3 (above) – Branding Expense for 1.25 Mn, should be allocated like this..

And that becomes the desired output, through which I can slice and dice the allocated amount by Location, Business or Department.

Download Data

I hope made that clear enough! Now get to work and post your answers (M Code and snapshot of the result) in the Comments. Just one rule – Use Power Query!

Solution