Please note javascript is required for full website functionality.
MVP

Blog

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.

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. 

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: 

= List.Dates(#date(Date.Year([Date]), Date.Month([Date]), Date.Day([Date])), 31, #duration(1, 0, 0, 0))

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!

Newsletter