How to Fill Dates Between Start Date and End Date in Power BI (Power Query)

The other day, I encountered a problem where I had my data and its shape looked like the following:

Essentially, what I wanted was to fill dates between start date and end date keeping the value the same. I’m sure there are so many other ways than how I solved this, but I wanted to do this in Power Query before getting data into a data model, so I’ve done this in M language (MDX).

The solution itself shouldn’t be hard to understand or implement to both who are proficient in M and who are pretty much beginners in it. In this post, I’ll go through the process step by step so anyone can understand how I did it.

---

First you load the data, and it’ll look something like this:

And then you’ll go to “Add Column” tab, and choose “Custom Column”.

You see a pop up window as you have clicked that. And finally, you change the new column name to whatever you want and add the following code into the formula area:

{ Number.From([StartDate])..Number.From([EndDate]) }

What this code is doing is to convert StartDate and EndDate to numbers so that we can get them put into a list of numbers.

After you’ve done that step, you should see the following; a new column called Date has been added that you can expand since it contains a list of values.

You click on the right top corner in the Date column, and choose “Expand to New Rows”.

Now that the date column is expanded vertically, you can change its date type from number to Date.

---

Done! You can go ahead and remove other columns as needed. We’ve successfully filled dates between StartDate and EndDate. M language comes in handy for data transformation and manipulation.

Feel free to download files here: Fill Dates Between Dates