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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
D_PBI
Post Patron
Post Patron

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 Patron
Post Patron

@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 Patron
Post Patron

@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
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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