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 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 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!
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
Great job mate! Exactly was I was looking for!
Glad you found it useful !
Just what I was looking for, thanks a lot!
Perfect! Glad it’s useful for you!
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 🙃
You are a magician! Thanks a lot!!
Haha! Glad you like it. Thanks 😃
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
Thanks man ... I was looking for this
Good to know! I just thought of it recently as a video - glad people find it useful!
This was extremely helpful thank you very much for helping
No problem! Glad you liked it
Great Video Thanks much
Very welcome!!
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 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
13min video for a 10 second answer @5:20
Glad you find it helpful. Others seem to as well 🙃
Loved it.. thank you for providing this -:)
Glad you like it 😃
How would you edit for one day less than today's date?
You can write -1 after local time now
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 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
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
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 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))