Expanding Rows Using Power Query

I had an interesting question from one of my book readers and I thought it would make a good blog article. There are many ways of solving this problem, and I blogged about solving a similar problem Fill Table with Last Survey Result using DAX here. The solution I am providing in this blog post is using Power Query.

The Problem

Suppose you have data in the form of dates (not consecutive) with a value for each of the dates (see the table below left side). You need to expand the rows of the table (create the missing rows) so that you will have all the consecutive dates in the given range and each of the dates has the previous updated value (see the table below right side).

The Solution

The way I arrived at the solution was to do the following steps in Power Query.

1. Load the data table (table on the left side above) into Power Query.
2. Extract the min date or the first date (12/04/2019 in this example). You can use the Power Query UI to generate the code to do this.
3. Extract the max date or the last date (23/04/2019 in this example). You can use the Power Query UI to generate the code to do this.
4. Find the total number of days from min date to max date. This is the duration in days (12 days in this example). You can use M Duration.days function for this.
5. Create a list of dates from min date to max date, both inclusive. You can use M List.Dates function for this.
6. Convert the list to table.
7. Merge this query with the data table query (from step 1).
8. Sort the resulting table by Date column in ascending order.
9. Use Fill Down option to fill the null values with the previous non-null value in the column.

Close & Apply the query changes and your resulting solution table gets loaded into Power BI Desktop.

YouTube Video

I recorded how to do all the steps mentioned above in the following YouTube video.

Want to Learn Power Query in a Structured Way?

My Power Query Online training course consists of 7.5 hours of online video training material that will teach you everything you need to know about using this fabulous tool for both Power BI and Excel. You can watch some free sample videos and find out more about registration following the link above.