Hilmar Buchta

PowerBI | PowerQuery

PowerQuery enhances Microsoft Excel by providing a self-service data integration toolset to combine external data with local data. But PowerQuery may also be used just to do useful transformation tasks within Excel. In this example, we start with a simple Excel workbook containing planning data for 5 products and 12 periods and a regional allocation key for 4 regions.

Products:

image

Regions and allocation:

image

For further processing a table of the following format is required with the plan values being un-pivoted and the allocation key being applied for each product, month and region:

image
                           …
image

So, how can we do this in Excel? Before PowerQuery we might have been using some Excel tricks with functions like index or mod, but this approach is not very flexible. What about adding an additional product or another region? What we really need is a simple transformation that allows us to refresh the resulting table based on any changes of the underlying tables.

The following video shows my step-by-step approach to this task.

Unpivoting Excel Data in PowerQuery

You can also download the final Excel file here.