Un-pivoting plan or forecast data in Excel with PowerQuery

Technical Value

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 .

Neuen Kommentar schreiben

Der Inhalt dieses Feldes wird nicht öffentlich zugänglich angezeigt.


  • Keine HTML-Tags erlaubt.
  • HTML - Zeilenumbrüche und Absätze werden automatisch erzeugt.
  • Web page addresses and email addresses turn into links automatically.
Teilen auf

Newsletter Anmeldung

Abonnieren Sie unseren Newsletter!
Lassen Sie sich regelmäßig über alle Neuigkeiten rundum ORAYLIS und die BI- & Big-Data-Branche informieren.

Jetzt anmelden