Power Query: Mind the Gap

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.

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 4^th to May 19^th, so she hasn’t created any data for these dates. I want to add the missing rows.

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.

Now I need to add the missing rows.

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.

I now have two queries containing Mary’s data; I am going to savage this query by only keeping one row!

I can do this by using the ‘Keep Rows’ option

I choose to keep just the top row.

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: 

I expand the list in Each_Date to new rows.

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.

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.

Now all I need to do is put Mary’s data back in.

I choose ‘Merge Queries as New’ from the ‘Merge Queries’ section from the ‘Home’ tab.

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.

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.

I now have data for each date – but it needs to be tidied. I want zeroes instead of nulls in my currency columns.

I can replace values to achieve this.

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!