Search This Blog

Monday, March 30, 2015

Un-pivoting plan or forecast data in Excel with PowerQuery

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.



Regions and allocation:


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:


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.