Unpivot in Power Query

In this blog I will demonstrate the use of unpivot functionality in Power Query.

Here is a sales report on horizontal axis we have countries, on vertical products. Countries are divided into Europe and Asia countries. Finland and Sweden belong to Scandinavia, Germany and Spain Central Europe, China and Korea North Asia, Singapore and Indonesia South Asia. Product from P1 to P10 belong to category 1 and P11 to P20 to category 2.

Can you make any sense with data ?

The data is not so far readable for Pivot. The table should be narrow and tall.

Let’s load the data into Power Query.

The issue is still that we should have data in pivot format to analyse the data. The data should be modified before analysing.

Activate all the countries, but not the product. Then select transform – unpivot columns.

Now the data looks better, the data is readable for pivot. The table is narrow and long. The columns are product, country and volume.

Just change the column names.

Then we need to download also the master data tables for countries and products.

As the Power Query did not recognize the first row as header, press the “use first row as header” parameter in home menu.

Then the products:

This is fine.

Let’s make the relations.

The diagram view.

Pretty simple data model: two master data tables and one transaction table.

The data to countries and data are products are both many to one cardinalities. Each product and country may appear just once in master data tables, but may appear many times in data table.