0

Well, in this blog post I am going to cover a very common scenario – not having your data the right way around to do something with it i.e. make a pivot table from it. However I am going to show you how to do that with a not (as yet) well known add-in for Excel called Power Query. If you want to be able to sort and analyse your data properly, it needs to be in what is called a normalised format (basically organised down as opposed to across) but the natural tendency is go straight to organising it across.

The file is here:  unpivoted_data

Wong Way

Wong Way

 

Usual (not normalised way)

SURNAME FIRST NAME PERSONNEL NO. Grade 24-Nov 25-Nov 26-Nov 27-Nov 28-Nov
Hebron Lavinia 12345 Elf 7.5 7.5 7.5 7.5 7
Dogsbody Spaniel 12344 Hobbit 6 6 6 6 5.6
Waggletooth Festus 12222 Orc 7.5 7.5 7.5 7.5 7

Same data – Normalised (way you need to have it if you want to run a pivot table)

control_freak_right

SURNAME FIRST NAME PERSONNEL NO. Grade Attribute Value
Hebron Lavinia 12345 Elf 24-Nov 7.5
Hebron Lavinia 12345 Elf 25-Nov 7.5
Hebron Lavinia 12345 Elf 26-Nov 7.5
Hebron Lavinia 12345 Elf 27-Nov 7.5
Hebron Lavinia 12345 Elf 28-Nov 7
Hebron Lavinia 12345 Elf 29-Nov DO
Hebron Lavinia 12345 Elf 30-Nov DO
Dogsbody Spaniel 12344 Hobbit 24-Nov 6
Dogsbody Spaniel 12344 Hobbit 25-Nov 6
Dogsbody Spaniel 12344 Hobbit 26-Nov 6
Dogsbody Spaniel 12344 Hobbit 27-Nov 6
Dogsbody Spaniel 12344 Hobbit 28-Nov 5.6
Dogsbody Spaniel 12344 Hobbit 29-Nov DO
Dogsbody Spaniel 12344 Hobbit 30-Nov DO
Waggletooth Festus 12222 Orc 24-Nov 7.5
Waggletooth Festus 12222 Orc 25-Nov 7.5
Waggletooth Festus 12222 Orc 26-Nov 7.5
Waggletooth Festus 12222 Orc 27-Nov 7.5
Waggletooth Festus 12222 Orc 28-Nov 7
Waggletooth Festus 12222 Orc 29-Nov DO
Waggletooth Festus 12222 Orc 30-Nov DO

If you have to “normalise” data in Excel normally – well I’ll put it this way…it ain’t easy…but it’s literally the work of minutes with Power Query (I am using Excel 2010 – it’s not available in earlier versions)

These are the steps

  1. Make sure you have Power Query installed (you will probably need to google for it or ask your IT administrators to install it for you)
  2. It will appear as a new ribbon on your Excel.
  3. Click in your data
  4. Click on the Power Query tab.
  5. Click on From Table
  6. Power Query will select the data you have the cursor in (note that in this example you will have to make sure you have selected all the data all the way across)
  7. Click on OK.
  8. Power Query is fired up.
  9. Highlight the first four columns: Surname, First Name, Personnel no and Grade. You can use the Ctrl key to select more than one
  10. Click on the Transform Ribbon
  11. Click on the tiny triangle just to the right of Unpivot columns
  12. From the dropdown choose Unpivot Other columns
  13. Your data has now been unpivoted.
  14. We still have Dos in our Values column.success
  15. Highlight the Values column
  16. From Transform, click on Replace Values
  17. In the Values to Find box, type in DO
  18. In the Replace With box, enter 0 (zero ). Power Query hates blanks…
  19. When you are finished, click on Home – Close and Load and this will bring the (normalised) data back into Excel – ready to be pivoted.

Here is the same data ready to be pivoted.

 

Leave a Reply