How to rearrange data in power query

[Sunce]

I have a data sample from excel that is in the following format:

Name	Column 2	Column 3	Column 4	Column 5
Harry	Pig	Null	Null	Null
James	Apple Pie	Cat	Tom Ford	Ferrari
Stella	Dog	Gucci	Lasagna
Micheal	Corolla	Null	Fendi	Goat Curry

How do i rearrange this data set into the following format in power query. Assuming the names are all unique values.

Name	Pet	Favourite_Food	Favourite_Designer	Car
James	Cat	Apple Pie	Tom Ford	Ferrari
Stella	Dog	Lasagna	Gucci
Harry	Pig	Null	Null
MicheaL	Null	Goat Curry	Fendi	Corolla

[ronrsnfld · Accepted solution]

You'll need to create a translation table so you can look up an item and figure out the category (car, pet, designer, food).  Then it's a matter of unpivoting; joining; and pivoting to get your desired output.