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!