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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
alexcollin24
Frequent Visitor

Power Query list of all days between minimum and maximum date from a single column

Hi all,

 

I want to have a list of all the days between the minimum date and the maximum date of my column who looks like this : Capture.PNG

For instance, if my minimum date in this table (DUE_DATE) is 12/18/2014 and my maximum date is 3/25/2015, I want all the days between those two dates, example 3/20/2015, 3/21/2015, 3/22/2015... but I don't want to write these dates in case there is an update. I want to write in my formula maximum and minimum so it can update automatically if we change the dates. As you can see, some dates are written more than one time, but it doesn't matter, I can use only one of them without a problem.

 

Does someone know how to do this?

 

Thanks

1 ACCEPTED SOLUTION

If you are looking or a Power Query solution, if your DUE_DATE column is in Table1:

 

= Table.FromColumns({List.Transform({Number.From(List.Min(Table1[DUE_DATE]))..Number.From(List.Max(Table1[DUE_DATE]))},Date.From)},type table[Date = date])
Specializing in Power Query Formula Language (M)

View solution in original post

4 REPLIES 4
v-jiascu-msft
Employee
Employee

Hi @alexcollin24

 

Could you please mark the proper post as answer if it worked? More about this topic, please feel free to post here.

Best Regards!
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
mdannemiller
Frequent Visitor

You can use the Calendar function to set the value of a table.  Create a new table from the modeling tab in Power BI, that will open a table and let you write a DAX expression.  Just use TableName = Calendar("startdate", "enddate") and you have a table with one column that has a list of dates between the two dates.

 

I think you can go in and change those whenever you want.  

ok thank you

If you are looking or a Power Query solution, if your DUE_DATE column is in Table1:

 

= Table.FromColumns({List.Transform({Number.From(List.Min(Table1[DUE_DATE]))..Number.From(List.Max(Table1[DUE_DATE]))},Date.From)},type table[Date = date])
Specializing in Power Query Formula Language (M)

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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