Pivot your Data using Power Query

[Datatouille]

Power Query is a very powerful tool. It has a rich interface from which users can easily clean and reshape their data.

Table.Unpivot is one of my favourite feature. It IS a real game changer for cleaning data because business users tend to "build their data in the format that a PivotTable produces, not in the format that a PivotTable consumes"  (@KenPuls)

You can find here some useful examples here or here (from my fellow @MattAllington) which both illustrate the 'unpivot power'.

However, I faced a situation a few days ago where I actually needed to pivot (not unpivot) my table. This will be the topic of this article.

The case

I was sent a table similar to this format:

ProdID	Info	Value
1	Price	8,0 €
	Quantity	5
	Discount	6,0%
2	Price	5,0 €
	Quantity	9
	Discount	3,0%
3	Price	9,0 €
	Quantity	14
	Discount	6,0%
4	Price	10,0 €
	Quantity	14
	Discount	7,0%

The structure of this table is not very convenient for data analysis. I am not going to sum the 'Value' column and then slice it by Price, Quantity or Discount - It doesn't make sense !

I would rather have a pivoted table like this:

ProdID	Price	Quantity	Discount
1	8 €	5	6%
2	5 €	9	3%
3	9 €	14	6%
4	10 €	14	7%

Let's clean this table !

Table.Pivot in action

So, after filling-down the "ProdID" column, I could pivot the "Info" column:

Then, I chose the column which needed to be pivoted ("Value" Column) and "Don't aggregate" in the advanced options.

I ended up with the required table, which makes it easier for me to analyse the data.

What if there are duplicates ?

Let's assume the Product ID n°4 appears twice in the database.

ProdID	Info	Value
1	Price	8 €
	Quantity	5
	Discount	6,0%
2	Price	5 €
	Quantity	9
	Discount	3,0%
3	Price	9 €
	Quantity	14
	Discount	6,0%
4	Price	10 €
	Quantity	14
	Discount	7,0%
4	Price	5 €
	Quantity	5
	Discount	4,0%

The Table.Pivot shown above will return an error for Product ID n°4 because Power Query is unable to "unaggregate" data when there are duplicates (which seems to be logical, right?):

You can somehow use all the other functions in the Table.Pivot advanced options (such as Sum, Average, Median...) to solve the issue.

This will however aggregate the data for Product ID n°4:

Table.Pivot with an Average

In this case, it doesn't really make sense and if you want to keep your rows separate while pivoting your data, you need at least another column which makes each row unique.

In the example below, the product ID n°4 appears twice but on different dates:

ProdID	Dates	Info	Value
1	01/01/2017	Price	8 €
	01/01/2017	Quantity	5
	01/01/2017	Discount	6,0%
2	01/01/2017	Price	5 €
	01/01/2017	Quantity	9
	01/01/2017	Discount	3,0%
3	01/01/2017	Price	9 €
	01/01/2017	Quantity	14
	01/01/2017	Discount	6,0%
4	01/01/2017	Price	10 €
	01/01/2017	Quantity	14
	01/01/2017	Discount	7,0%
4	15/01/2017	Price	5 €
	15/01/2017	Quantity	5
	15/01/2017	Discount	4,0%

In this case, you can peacefully pivot the table and use the "do not aggregate" feature to end up with something like this:

ProdID	Dates	Price	Quantity	Discount
1	01/01/2017	8 €	5	6%
2	01/01/2017	5 €	9	3%
3	01/01/2017	9 €	14	6%
4	01/01/2017	10 €	14	7%
4	15/01/2017	5 €	5	4%

I hope you are now ready to leverage Unpivot AND Pivot features to reshape your data and prepare it for wonderful Power BI dashboards!

Note: The examples are attached in the .pbix file below.

Table.Pivot.pbix

[ZH2017]

Dear,

i am facing problem with dates while doing unpivot the sceniro is as below:

date fromat   dd/mm/yyyy e.g 10/05/2012

 when i try to convert it to date in power query it become like this

10/05/2017

when i use the format mm/dd/yyyy then it convert it properly.. what the reason behind???

[Datatouille]

Hi @ZH2017

Can you please send me the file so I can investigate ?

tristan.malherbe07@gmail.com

[mvoelkergabel]

Great article! I also think your username is a laugh 🙂