Power Query: Mind the Gap
17 October 2018
Welcome to our Power Query blog. This week, I look at how to fill in rows when there is a gap in dates.
I have the following data from my imaginary salesperson, Mary.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/10-oct/98/image1.png/e774d10cbbb9450fc45efbe51abdf434.jpg)
Mary has supplied me with her sales figures for May 2016. I need to add these to existing data, but there is a problem. Mary was on holiday from May 4th to May 19th, so she hasn’t created any data for these dates. I want to add the missing rows.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/10-oct/98/image2.png/f32e5a15e2cf9c3e4d2d058458ce054d.jpg)
I begin by creating a query ‘From Table’ in the ‘Get & Transform’ section of the ‘Data’ tab. I am prompted to define the boundaries of my Table (and to check that headers exist) and I take the defaults.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/10-oct/98/image3.png/f1140ff857fc3b6f5f97a6a24f4a6fc7.jpg)
Now I need to add the missing rows.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/10-oct/98/image4.png/72aa864d2854c6fefb1083fba0ab5792.jpg)
I start by creating a copy of the existing query, and to do this I will create a reference query. For more on the differences between reference and duplicate queries see Power Query: Cleanse, Tone and Upload.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/10-oct/98/image5.png/36776d1da4d05b45bb5a5d09375f407c.jpg)
I now have two queries containing Mary’s data; I am going to savage this query by only keeping one row!
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/10-oct/98/image6.png/23912d3b1671861e02bebcd5183f1607.jpg)
I can do this by using the ‘Keep Rows’ option
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/10-oct/98/image7.png/6f49c288a0d88a66b427eaf4ece923d6.jpg)
I choose to keep just the top row.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/10-oct/98/image8.png/b9ee28d90e6b5bc92ea4aeafdad51628.jpg)
I create a new column by using ‘Custom Column’ from the ‘Add Column’ tab. I use this to create a list of dates from the date on the row for 31 days:
= List.Dates(#date(Date.Year([Date]), Date.Month([Date]), Date.Day([Date])), 31, #duration(1, 0, 0, 0))
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/10-oct/98/image9.png/0485ccbc83bdeec1d741bad442a1ea5f.jpg)
I expand the list in Each_Date to new rows.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/10-oct/98/image10.png/daf8c4f0259ce428269c0d3d4badd32b.jpg)
I now have a row for each date in the date range. My new column doesn’t look like a date though, so I need to change the data type ready for the next step.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/10-oct/98/image11.png/22c6daeb82d7d69ac88f878227e04b28.jpg)
I want to simplify my query as I don’t need the original Date, Sales and Commission columns – these will come from Mary’s data.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/10-oct/98/image12.png/a1537847463e660a31158c8032525438.jpg)
Now all I need to do is put Mary’s data back in.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/10-oct/98/image13.png/917da985be13220165c8d2823e95344f.jpg)
I choose ‘Merge Queries as New’ from the ‘Merge Queries’ section from the ‘Home’ tab.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/10-oct/98/image14.png/8c3be7af9f73d031acae69ed85a2e148.jpg)
I want all the rows from my first query and matching rows (with the sales data) from my second query. I use the ‘Left Outer’ join.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/10-oct/98/image15.png/e63c0a4c21afc9afb438aacc09a59317.jpg)
A new query ‘Merge1’ is created, and Mary’s data is held in column Mary Data with Gap which contains a table. I choose to expand the Sales and Commission columns. I will (of course) uncheck the ‘Use original column name as prefix’ option.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/10-oct/98/image16.png/d082e3477129350b8a2a589156028e63.jpg)
I now have data for each date – but it needs to be tidied. I want zeroes instead of nulls in my currency columns.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/10-oct/98/image17.png/a468c9c28195e8f18fdedfa59a0cf042.jpg)
I can replace values to achieve this.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/10-oct/98/image18.png/61935f470d8b9f21b7b7683ffca40895.jpg)
I now have entries for each date so that this data can be combined with other similar data.
Come back next time for more ways to use Power Query!