Menu
Stuff by Yuki
  • Home
  • Data Engineering
    • Python
  • Business Intelligence
    • Power BI
    • Tableau
  • Perspectives
  • About
  • Contact
Stuff by Yuki

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

Posted on May 26, 2021June 15, 2021

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

1 thought on “How to Fill Dates Between Start Date and End Date in Power BI (Power Query)”

  1. Chia Ming says:
    March 22, 2022 at 10:02 pm

    Hi Yuki, was shared your content by a friend and loving it! I currently dealing with a dataset that has a start date, end date and a value(or notional amount in case). It also has an additional column that is the yield (or the interest rate return of an asset). Would you know how to do this in Python or SQL?

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Recent Posts

  • Where I’m Headed in the Next 5 Years
  • Open-Source vs Vendor Data Tools
  • Developing the Habit of Writing
  • How to Inspect and Optimize Query Plans in Python Polars
  • Learn Python Polars with Polars Cookbook

Popular Posts

  • A Running Total Calculation with Quick Measure in Power BI
  • A Complete Guide to Git Integration in Power BI
  • How To Copy And Paste Report Page in Power BI
  • Handling Missing Values in Polars
  • How to Convert String to Date or Datetime in Polars

connect with me

  • LinkedIn
  • Twitter
  • Github
  • Website

Search Articles

©2025 Stuff by Yuki | Powered by SuperbThemes