Power Query: Moving Date
14 March 2018
Welcome to our Power Query blog. This week, I look at how to transform extracted data into a useful table.
Regular readers will be familiar with my fictional salespeople and their tendency to supply data in the wrong format. Let’s meet John.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/03-mar/pq-67-image1.png/701adb389b1c4eca940b4b7c07609028.jpg)
Whilst John has supplied his expenses, the format I would like to see them in is something like this:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/03-mar/pq-67-image2.png/ea2e7362925c5794453a3737533722b4.jpg)
To start the process, I extract John’s data into Power Query:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/03-mar/pq-67-image3.png/cb279155b1e15dcc83188a91a863551b.jpg)
I can select the data and use ‘From Table’ on the ‘Get and Transform’ section of the ‘Data’ tab. My data will be converted to a table as part of the process.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/03-mar/pq-67-image4.png/5b914784fbdc4cc61a1f36f0ece998b8.jpg)
The first two rows are not useful to me, so my first step is to remove them using the ‘Remove Rows’ option in the ‘Reduce Rows’ section.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/03-mar/pq-67-image5.png/f23807a3f7adbe29b6029c540a6429ae.jpg)
I could remove them based on a parameter, but I just want to get rid of the first two rows so I choose the ‘Decimal Number’ option. I also remove the row of nulls beneath my ‘Date’ row by removing blank rows.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/03-mar/pq-67-image6.png/69aa0eee57283b813a4d0861ffd175b9.jpg)
I want to create a column from the 'Date' cell. The first step to achieving this is to right click on the Date cell and use the option to ‘Add as New Query’. This creates a new query in the queries panel on the left of the screen.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/03-mar/pq-67-image7.png/2c4ebae188808b961c0a45ce8fe52d79.jpg)
The new query, automatically called ‘Column2’, includes my earlier source steps and the value in the top row of Column2 – the date.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/03-mar/pq-67-image8.png/6e9d87a1a45eb266adcab4b45ac2943c.jpg)
Having created my query, I need to make sure the next steps I add are to the ‘Table1’ query. Now I can transform the rest of the data to appear in the format that I would like.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/03-mar/pq-67-image9.png/d400442c03db4e0d4948a95bb1317643.jpg)
The steps I have taken are;
- Removed Top Rows1 – having moved the date to a separate query, I could remove the ‘Date’ row
- Promoted Headers – since I wanted to just keep my expense types and values, I promoted the expense to headers to get rid of the generic ‘Column1’ etc. (the ‘Changed Type1’ step was an automated Power Query step)
- Unpivoted Columns – I didn’t want to keep my expense types as headers, I ultimately want to store them in a column under the heading ‘Expense Type’, so I unpivoted to get the data as it is shown above.
Now all that remains is to rename my columns and add a ‘Date’ column. To do this I will add a custom column.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/03-mar/pq-67-image10.png/0bb4719930057a56eb6ed51c5743a983.jpg)
Having referenced the other query (which is easy to check as I can see it in the left-hand pane), I click ‘OK’.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/03-mar/pq-67-image11.png/5f7ad5890c36ab74e7f45f4f3b79cc58.jpg)
The date appears as a new column.
Want to read more about Power Query? A complete list of all our Power Query blogs can be found here. Come back next time for more ways to use Power Query!