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

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now! Learn more

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
Power BI DataViz World Championships

Power BI Dataviz World Championships

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now!

December 2025 Power BI Update Carousel

Power BI Monthly Update - December 2025

Check out the December 2025 Power BI Holiday Recap!

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.