Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
D_PBI
Post Partisan
Post Partisan

How to create a full list of dates between a column MIN and MAX dates in Power Query?

Hi,
I have a Power Query table that contains a list of dates, however these dates are not consecutive. Take a look at the column in question, below:

D_PBI_0-1724165158069.png


I need to make to make the column's list of dates includes every single dates between the column's MIN and MAX dates. For example, the column MIN date is 01/08/2008 (as you can see in the above screenshot). The MAX date is 03/08/2024. I need the column list to be every date between those two dates.
How do I achieve this?  It has to be done in Power Query.
Thanks.

1 ACCEPTED SOLUTION
D_PBI
Post Partisan
Post Partisan

@HotChilli thanks for the quick response. I came across the post you linked a little earlier and my attempt at is it below, but it's erroring. I'm clearly misunderstanding something. [Date] is the date column within the table in question.
=Table.AddColumn( #"Removed Duplicates", "Date List", each List.Dates( List.Min( [Date] ), Duration.Days( List.Max( [Date] ) - List.Min( [Date] ) ), 1,0,0,0))

EDIT: I've just found a video that helped me achieve my aim. I just needed that extra visual on it @HotChilli, but thanks the same. The video's link is below in case it's helpful for others.
https://www.youtube.com/watch?v=Ase0yNEHApI

 

View solution in original post

3 REPLIES 3
D_PBI
Post Partisan
Post Partisan

@HotChilli thanks for the quick response. I came across the post you linked a little earlier and my attempt at is it below, but it's erroring. I'm clearly misunderstanding something. [Date] is the date column within the table in question.
=Table.AddColumn( #"Removed Duplicates", "Date List", each List.Dates( List.Min( [Date] ), Duration.Days( List.Max( [Date] ) - List.Min( [Date] ) ), 1,0,0,0))

EDIT: I've just found a video that helped me achieve my aim. I just needed that extra visual on it @HotChilli, but thanks the same. The video's link is below in case it's helpful for others.
https://www.youtube.com/watch?v=Ase0yNEHApI

 

Jason_1981
Helper I
Helper I

Why does it have to be done in Power Query? If using Power BI you can just create a date reference table with every date forever and use that as your date reference. Link that to your current date and use the "all dates" table as your filter date reference so you can select any possible date and if that date does not appear in your data then it will just show no data for that day. 

HotChilli
Super User
Super User

You can do this sort of thing

https://community.fabric.microsoft.com/t5/Power-Query/Create-a-list-of-dates-between-a-start-and-end... 

 

or you can create a list from min date to max date and then expand it:

{Number.From([MinDate])..Number.From([MaxDate]) }

-> you need to get the mindate and maxdate but I'll leave that for you to do

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.