Relative date / today in Power Query IF / conditional column
Вставка
- Опубліковано 13 лип 2024
- Power Query's conditional columns are super easy but they cannot work with comparing relative dates, you need custom code for that, but once you understand it there are dozens of date functions in Power Query. Remember to Wrap Date.From() around our functions to explicitly set the date format and always use upper case. If you want a copy of this workbook, leave me a comment & subscribe to my channel.
Learn all date functions here: docs.microsoft.com/en-us/powe...
Functions I showcase:
Today: Date.From(DateTime.LocalNow())
One quarter ago: Date.From(Date.AddQuarters(DateTime.LocalNow(),-1))
End of last month: Date.From(Date.EndOfMonth(DateTime.LocalNow()))
00:00 - Introduction
02:43 - Before today
05:55 - Before one quarter ago
08:44 - End of last month
10:38 - Filtering based on a relative date
11:54 - Date functions documentation - Навчання та стиль
Great job mate! Exactly was I was looking for!
Glad you found it useful !
You are a magician! Thanks a lot!!
Haha! Glad you like it. Thanks 😃
This was extremely helpful thank you very much for helping
No problem! Glad you liked it
Thanks man ... I was looking for this
Good to know! I just thought of it recently as a video - glad people find it useful!
Loved it.. thank you for providing this -:)
Glad you like it 😃
Very good content, Please more video on power query
Thanksss love the feedback! Have a few ideas for future videos! I have quite a few on my channel already!
2 hours to find this solution 😅 Thank you!
Amazing! I’m so glad it helped 🙃
My Polish Power Querry had errors with the funkcion TODA(). Thanks to You I avoided this problem. Many Thanks.
Yay! I’m so glad this video helped you! Thanks for the comment
David, thanks for the video -- would like a copy of the workbook. Thanks.
Sure! Get it at www.xlconsulting-asia.com/youtube-files.html
Hi David,
Thanks for the video! I have a report that I need to filter out the current day and prior business day. Instead of -1 I used -2 in the code. But I need to account for weekends (if Monday, then filter out Monday and Friday of prior week). Is there a way to do this? I am not concerned about US Federal Bank Holidays, but if there is a way to account for that it would be a plus! Thanks!
Heya you will provably need a date table with public holidays & weekends & then use merge queries to remove it. My latest video is all about date tables in power bi, check it out
Hi David, 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!
Sorry this is a pretty specific problem & I can’t help, maybe try one of the forums that way you can post images & the data itself maybe too
@@learnspreadsheets sure, thank you again!
Hi is there a formula or PQ way to change any transactions time that come between 12:00:00 AM and 7:59:59 AM To be 8:00:00 AM which is the start of working hour.
Hi yes there should be, you should extract the time out of a cell to show on its own then an IF formula is capable
How would you edit for one day less than today's date?
You can write -1 after local time now
Hi David, I have 2 date columns and one contains null values I want to replace the null value with previous date column can you solve this one..
I would add a conditional column… if col1 is blank then col 2 otherwise col1, seems like that should work
@@learnspreadsheets yes I applied it. It works
13min video for a 10 second answer @5:20
Glad you find it helpful. Others seem to as well 🙃
What I need is to create a column with Today() - "date" in order to know how many days I have...but I can't do it
Hi, this seems to work: = Table.AddColumn(#"Changed Type", "Custom", each Duration.Days([Date]-Date.From(DateTime.LocalNow())))
I'M TRYING TO FILTER MY DATES WITH POWER QUERY, I NEED TO SEE MY INVENTORY STARTING FROM 160 DAYS AFTER THE CURRENT DATE AND UP, BUT I CAN'T FIND MY WAY TO FILTER MY DATA THIS WAY... PLEASE HELP
Using the code I have in this video you can get the current date then wrap it inside a date.adddays function to move it 180 days in future. More here: learn.microsoft.com/en-us/powerquery-m/date-adddays
I used Date.From(Date.EndOfMonth(DateTime.LocalNow())) in a blank query but I got the end of this month (5/31/2022) , I want the end of last month (4/30/2022). I played around and added -1 at the end but I got an error. Any suggestions?
It’s definitely typing , -1 towards the end but the code is tricky so keep trying combinations & it should work or read the documentation on the Microsoft site for the functions
Try this for end of previous month : = Date.EndOfMonth(Date.AddMonths(DateTime.Date(DateTime.LocalNow()),-1))