Pivot your Data using Power Query

by Excelside Established Member ‎08-02-2017 07:28 AM - edited ‎08-02-2017 08:01 AM

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:

ProdIDInfoValue
1Price      8,0 €
 Quantity5
 Discount6,0%
2Price       5,0 €
 Quantity9
 Discount3,0%
3Price       9,0 €
 Quantity14
 Discount6,0%
4Price      10,0 €
 Quantity14
 Discount7,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:

ProdIDPriceQuantityDiscount
1       8 €       56%
2       5 €       93%
3       9 €      146%
4      10 €      147%

 

Let's clean this table !

 

Table.Pivot in action

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

 

Pivot Menu.PNG

 

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

DoNotAggregateMenu.PNG

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.

ProdIDInfoValue
1Price8 €
 Quantity5
 Discount6,0%
2Price5 €
 Quantity9
 Discount3,0%
3Price9 €
 Quantity14
 Discount6,0%
4Price10 €
 Quantity14
 Discount7,0%
4Price5 €
 Quantity5
 Discount4,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?):

Error.PNG

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 AverageTable.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.

Attachment
Comments
by ZH2017 New Member
on ‎10-06-2017 07:34 AM

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???

by Excelside Established Member
on ‎10-11-2017 07:44 AM

Hi @ZH2017

 

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

tristan.malherbe07@gmail.com