List dates between two dates in Power Query
HTML-код
- Опубликовано: 2 окт 2024
- Need to create a list of dates between a start date and end date? Well, let's look at how Imke did it in the Power BI community, it will blow your mind :)
#curbal #powerquery #mlanguage #powerbi
Here is the thread on the Power BI community, where you can find Imke's code:
community.powe...
Here you can download all the pbix files: curbal.com/don...
SUBSCRIBE to learn more about Power and Excel BI!
/ @curbalen
Our PLAYLISTS:
Join our DAX Fridays! Series: goo.gl/FtUWUX
Power BI dashboards for beginners: goo.gl/9YzyDP
Power BI Tips & Tricks: goo.gl/H6kUbP
Power Bi and Google Analytics: goo.gl/ZNsY8l
☼☼☼☼☼☼☼☼☼☼
POWER BI COURSES:
Want to learn Power BI? How about you take one of our courses? Here you can find the available courses:
curbal.com/cou...
☼☼☼☼☼☼☼☼☼☼
ABOUT CURBAL:
Website: www.curbal.com
Contact us: www.curbal.com/...
▼▼▼▼▼▼▼▼▼▼
If you feel that any of the videos, downloads, blog posts that I have created have been useful to you and you want to help me keep on going, here you can do a small donation to support my work and keep the channel running:
curbal.com/pro...
Many thanks in advance!
▲▲▲▲▲▲▲▲▲▲
************
************
QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Linkedin ► goo.gl/3VW6Ky
Twitter ► @curbalen, @ruthpozuelo
Facebook ► goo.gl/bME2sB
I googled some awkward question about how to do this, and this was EXACTLY what I wanted. Thank you!
Yes! Cool I could help you (with the help of google ;)
/Ruth
I love you, you always have the solution to my problems :)
At your service ;)
This is great. Thank you very much. But, one question, how do I exclude the dates falls as weekends between my start & end day?
Good morning, I have created a list with : = Table.AddColumn(#"Changed Type", "Datesfecha", each let
AllDates=
{Number.From([#"Design #(lf)Actual Date"])..Number.From([#"Actual#(lf) Lofting Date"])},
stofMonthDates=
List.Transform(
AllDates,
each Date.From(_))
in
stofMonthDates).......it retrieves correctly in some rows, but when start date and end date have a difference grater than 20 days it shows less results in the lists. e.g. 6/11/2022 to 7/4/2022....reachs dates up to 6/30/2022. hope to be clear. Do you know why this happen? if possible please advise. thanks for sharing.
This helped me solve a problem where I already have a date dim table, but I had odd calendar periods that needed to be defined by its own table. Adding this list column and then creating a relationship to the date dim table solved being able to slice on the odd period definitions. Thank you!
The nerd in me loves how excited you are in this video. Great info, thanks! Just put it to good use.
Power query tricks melt my heart... cant help it ;)
/Ruth
Hi Ruth. That's a wonderful video. Is it possible to generate list of dates between 2 dates in same column. For example, I have dates column
11 nov 2021 , 3rd march 2022, and working hours is different for 2 dates. Is it possible to generate dates 11 nov 2021 to 2nd march 2022 with one working hours and from 3rd march its different working hours. Appreciate your help if you give tips on this.
In a table, cell A1 have 07/01/2021 and cell B1 have 10/02/2021, now, I want max date, between them, i.e. in C1 should come 10/02/2021 (With Power Query). Please guide me. I have table having multiple dates to compare. Plzz guide me.
Thanks for the demonstration. What if I have the opposite scenario.... I have a column with a timestamp (one process after another, order is important here) and I want to be able to place those processes within a StartTime and EndTime (hours, minutes)?
Can we do this for list of months between same Or different year instead if list of dates. e.g. sd: 8/2021 end date : 11/2021 . List will be 8/2021 9/2021 10/2021 11/2021
Hi, great tip, but i would like to have one date by month (4/1/2023, 5/1/2023, 6/1/2023) how to do this ?
are u a geek ?
How could we do the opposite?
Which is: Date Ranges (From & To in two Columns) FROM a list of dates
Awesome trick and explained so well Thank you. I am trying to create a list where min and max both are in same column?(unlike yours where min is one and max is another)
Great job, our virtuous teacher, I want to do the same way, but except for J.J. Day, you can explain to me how
How can I do this, but just months instead of days? :)
how to do in access to filter date between 2 dates for tasks to see in a report the list of task for the coming two weeks
Thank you
Thank you, thank you, thank you!!
😊😊😊
I think that if you had used List.Dates, that might be even better. For example:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUbLQN9Q3MjC0ADONIcxYnWglJ7CAOULO0Bwh6QwWMULWCWXHxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Task Name" = _t, Start = _t, End = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Task Name", type text}, {"Start", type date}, {"End", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Dates", each List.Dates([Start],Duration.Days([End]-[Start])+1,#duration(1,0,0,0))),
#"Expanded Dates" = Table.ExpandListColumn(#"Added Custom", "Dates"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Dates",{{"Dates", type date}})
in
#"Changed Type1"
Yes! That is also possible :)
Wonder which one performs better 🤔
/Ruth
Thank you so much, I have end dates that are nulls and when I run this it cannot apply due to nulls. What should I do?
Hi Curbal, new sub here, how can i display only the last 7 days worth of data?
This is my favourite Power BI hack ever. I figured there would be a good solution to this problem, but I had no idea it would be so simple and elegant.
M is beautiful!!
/Ruth
Can we use this list as a date in visual also for hierarchy and to create week number
Elegant Always!!
You're my go-to solution provider anytime I'm stucked with PQ & DAX Logics
Thanks Ruth for Always!!!
but where in the completa table? where is it extrating the dates from?
How to add the missing dates when you only have 1 Date coloumn?
Thank you ! So brilliant ! I won so much time ! :)
Hi ruth, amazing video!!. How would you do the reverse process? U have a video about it?
Hi Ruth, thank you for this great video :)
Just one question, is it possible to add an increment for dates here? My goal is to add a new row for every 7th date in Start-End period instead of every date.
Thank you so much..
It's reduce my work.
It's wonderful vedio❤❤❤❤❤❤❤❤
Hi Need one help.
Same scenarios.
if it is possible to find out the any dates/ Months/ year are missing in the start date column and end date column?
A piece of Gold! Saved my life! Thank you!!!!!!!
eu te amo muito de verdade você não faz ideia o quanto salvou a minha vida com isso
Hi Ruth, thanks for the video. Quick question about how to achieve following.
Master table has values till yesterday (sales and region). I want to append Master table with today's values. I want to perform this automatically for every day. Since I am using SAP BW, Incremental refresh doesnt work (Query folding not available).
I have tried to extract latest date from master table in a query (in my example, it is yesterday). Used the latest date query to fetch today's data (as start date). However while appending both tables, it throws cyclic error (rightfully so).
I would really appreciate your guidance.
Thanks!
Why would I get null for results and only 1 date?
Very nice Curbal. I have opposite situation hope you can do a video on it soon.
i have flight data as below,
How do i convert fly in and fly out to columns so i can use your video solution? Thanks
person column, status column, date column
person1, fly in, 1/2/21
person1, fly out, 11/2/21
person2, fly in 3/2/21
person2, fly out, 23/2/21
Hi Ruth, first of all thanks for your collaboration ! I'm looking for a formula very close of this example...but instead of all days from begin till the end....I just need one occurence per month till the end. Please see the example:
Begin: 19/07/2019
End: 19/10/2019
so, I would need.....19/07/19, 19/08/19, 19/09/19 and 19/10/19. Could you please support me ? Thanks in advance !
Thank you! I have the same problem, but it is time, not date. The time in the 1st table (i.e. 11/08/2021 9:51:52 am) need to match the time period in the 2nd table (i.e. start time 08/08/2021 9:14:15 am, end time 15/08/2021 5:20:19 pm). Could you please help share the solution? Thanks!
you are the best! This video saved my life :)
how to insert missing date? i only have start date
Thanks so much my dear! I was searching for this little process in many foruns. 😍😍🤩🤩
At your service!
Thanks, Ruth. This worked for me 2 years ago and still works now. However I am working on a bigger dataset than I previously did and this has added 100 million rows to my data, Is there any other way/upgrade of doing this in DAX other than columns?
Good evening thanks for your great knowledge, I have a question,
I have two columns a start date and another end date, how can I calculate the course of time in year, month and day, to be able in Power query
In Excel for me it was to use the function ifdate (f_ini, f_fin, "y") & "year" & ifdate (f_ini, f_fin, "ym") & "mounth" & ifdate (f_ini, f_fin, "md") & " day "
I hope you can help me solve this great concern that I have, thank you
Elegant solution as always, thank you!
How does this list work please explain
Amazing!! Was looking for exactly this solution, helped me a lot!
🎉🎉
nice but we have to provide the two dropdown list in report view by using filter visual for the study year & reference year in visuals in which user want to select the period in drop down list for current period based on selection and previous year based on selection list that is in quarterly form & if in study year if he choose any quarter then automatically last 3 quarters data would be included & same thing for reference year(previous year). So i can apply these formulas that u hv suggest but how can we create two drop down list even the source for date column is same so when i choose quarter automatically same period auto select in second drop down list in which user want any quarter from the last three years & then it also calculates last three quarter from the selected quarter. In report view using filter to create a drop down list but for two list in which we select any quarter automatically same quarter select in second list not couldn’t give the option to choose different from the firs drop down list.. please suggest
This was exactly what i was looking for, thank you so much! But i still have a problem: I need to create a list from a "Start Date" to one day before de End Date. I did "{(Number.From[Chegada])..(Number.From[Data de Saída])-1}", but its giving me a error. Someone can help me with this?
it was so helpful, many thanks
Nice video :) very helpful, one doubt I wanted to mark leave and working days together as leave or present in a matrix is this possible
this is great... what would be better would be if we could add an increment to the list so we are essentially adding 1 week or 1 quarter, etc to each row for the given period... I haven't been able to find a way to do this
How do we get count of days between two dates ?
hello kindly can you do it by months
You've saved the day, thank you so much for sharing this solution !
🥳
Thanks. Went through so many videos to find this. so simple :)
Simple is good :)
This is awesome!! Thanks so much!
Sometime I feel your voice is not audiable.. could you please increase sound quality?
Where do you watch the videos ? In which device?
/Ruth
@@CurbalEN I watch in mobile.
Oh ok, I thought you were going to say PC. Is it an Android? I checked on my iPhone and the sound is ok...
/Ruth
Thank you Ruth, a very elegant and welcome solution
My pleasure 😇
ABSOLUTELLY AMAZING! - This was EXACTLLY solution I was looking for. Simple, ellegant, and smart. Thanks for the video.
🎉🎉
/Ruth
This doesn't work if the data source is of DirectQuery type. Any solution for that?
wow thank u 🔻🔻
Thanks you a lot! You solved my working issues!!
Excellent!!
This is amazing! It solves so many of my problems. Big shout out to ImkeF. Thank you.
Awesome video, thank you! Follow-up question: I'm trying to do (I think) exactly what you're doing: show the list of dates between a Start and End date. But when I added my column to the table, I got a different Error: "Expression.Error: The key matched more than one row in the table." In your example, it seems your Task Name (Primary Key?) and other fields simply expanded to fit the new rows created by the list of dates in between Start and End. Any idea how I can get mine to do the same? Cheers!
OMG - this is so smart. I don't normally comment, but this is the solution I've been trying to find!
Great Help to my Project.. Thank You
My pleasure!
How can I find the time interval between two dates...like in hours and minutes
Amazing
super Like
super Love
super anything
You are awesome
I spent two days dreaming how to solve the same issue
🤩🤩🤩🤩🤩yey!!!
/Ruth
Great !!! Very helpful
Thank you very much
Very nice solution and thanks for sharing!
My pleasure!
I always find answers in your channel. Thanks !
Delighted to hear :)
Thanks much for the tutorial. Works great. Added a column to list day of week and then filtered out Saturday and Sunday to get only weekdays. :)
amazing! thank you
Hi, Can we do the same thing in power pivot.... please let me know if anyone can help me on this
Thanks Ruth...I keep forgetting how to do it so coming back to your video...also quite enjoying your enthusiasm and enjoyment of the solution :)
:)
Thank you so much...
Lots of love 💕
From india
Thanks dear and the same back :)
/Ruth
Thanks for the video I'm your fan
Thanks 😊
Used this today in anger. Thanks again Ruth.
It is a neat trick ;)
Thank you Ruth. This is amazing. Just wonder if we can list times between two times (ignore the date part) in a similar way? Thank you.
How to do it in dax?
@Curbal, Is this possible to do in hours? For example a list of hours between 7am and 7pm for each row. I have seen a few comments on here and have tried to watch videos that you suggested, however, I continue to get an error "Expression.Error: The number is out of range of a 32 bit integer value.
Details: 0.3125" when I follow your steps. Any tip would be helpful. Your videos are great.
Maybe it has to do with the regional settings ? It sounds like you are trying a format that is not allowed.
Can you post your code on the power bi community for detailed help?
/Ruth
Awesome and needed today so thank you very much
Perfect timing then!
OMG...I Always think "Nothing impossible in Excel". And now again you proved it.......Thank you so much
You mean Power Bi,right?
/Ruth
Thank you!!! :)
THANK YOU!
Thanks for shared! Amazing!
🥳🥳
MIND BLOWN! Thank you!
👏👏
great! thanks
Great trick!
Thank you!
thank you!
This just made my day, so helpful. Thanks Ruth!
Thank you! This gives me a new perspective to repopulate a table where data was missing. I could not manage to get a nice smooth rolling average curve.
is there a methodology to do this in python/sql for other platforms. Thanks
Probably but I don’t know how
@@CurbalEN toh :( :) for the amount of rows I need to do this for will be like millions and millions haha
😂
Many thanks
¡Bingo! Encontré la solución para construir una "snapshot" table.
Mi próximo deseo es enrolarme en tu curso de lenguaje M.
Gracias Ruth
Genial!!🥳
Wonder idea, This really gave me retrospective to work with dates.
Thank you thank you! This made my life so much easier. Great tips and tricks!
Brilliant. Exactly what I need in my reporting transformation project. I repeat, brilliant.
🥳