Power Query Dates and Time Challenge

🔽More Information below🔽
Here's Bill Jelen's video re the 2029 bug
• Excel - Understanding ...
And the 2049 trick that I’d forgotten I’d applied
• Excel - Which Century ...
Here's a link to the challenge and the file if you want to have a go before or after watching my solution and sign up for our newsletter while you are there to get future challenge notifications.
accessanalytic...
I love reading your comments and questions so feel free to post those
Follow me on various platforms: wyn.bio.link/
Cheers
Wyn

[@davidfamilydoctor9430]

FYI - this man searched for "power query challenges". Found this playlist.

[@AccessAnalytic]

Hope you find what you’re looking for, and welcome to the channel

[@wmfexcel]

Thanks for the video! I didn't know about the second argument for Start of Week, which is super useful! Glad to learn something new.
Btw, I would say that's a feature not a bug... as Excel allows users to determine the century when a two-digit year is input (although most of Excel users are not aware of it... ) 😅😅
As a good practice, always use four digits year to avoid confusion, or even always use YYYY-MM-DD to avoid ambiguity.

[@AccessAnalytic]

Cheers, yeah features that do unexpected things can be labelled in many ways 😁

[@ibrahim82649]

Thanks for this tutorial very helpful, can power query assist me in calcluating hours worked from 22:00pm to 06:00pm

[@AccessAnalytic]

Yep, if you have a date time column for each then click EndDateTime column, hold Ctrl, click StartDateTime column then go to Add Column tab, time button, subtract

[@Faisal63045]

Hi
I’ve PQ challenge
The daily Work Hours:
Start from 8:00:00 AM
End time 10:00:00 PM
The challenge
1- is to change any transaction that comes after work time to the start of work time on the Next day (8 AM)
2- is to change any transaction time between (12:00:00 AM to 7:59:59 AM) AM to 8:00:00 AM

[@AccessAnalytic]

Do you have the answer too Faisal?

[@Faisal63045]

@@AccessAnalytic
15-apr-2022 10:57:00 PM
Should be changed to 16-apr-2022 8:00:00 AM
I've idea but i don't know what tool that would be helpful
1- split column to be date only and time only
Time only.. Start of hour then merge column
But how can I automate the transaction Tha come after 10PM to be the next day Date

[@AccessAnalytic]

@@Faisal63045 That would need a Custom Column to say if Hour > 22 then Date Add 1 day, then another custom column to say if hour is between 22 and 8 then make then as 8.

[@JonathanExcels]

Fun

[@peterbartholomew7409]

I would contend that the 'bug' is not Excel's; it belongs to the database that exported dates as text with 2 character years.
I appreciate that this is a Power Query challenge so an answer of 'avoid taking Excel data into PQ without good reason' does not meet the brief.
For example, days since Friday is given by = MOD(Table1[Forecast Date], 7) and the other two are only just worth breaking out a Lambda function to hold the formula.
AdjustYearλ = LAMBDA(date, YEAR(date)+100*(YEAR(date)

[@AccessAnalytic]

Thanks Peter, I’m spending most of my days in Power BI so Excel formula solutions aren’t so relevant to my day to day life anymore.

[@peterbartholomew7409]

@@AccessAnalytic Yes, I realise that. I keep an eye on what you are doing and one day I will make the effort to master these complex environments, but there appears to be a lot of learning to be done. Meanwhile, I have chosen to stick with Excel and attempt to run with the new functional programming environment that is taking shape. One thing is clear, either with PQ or Lambda, building reusable functionality bears little resemblance to the traditional spreadsheet. It may be that DAX and Lambda have more similarities with one an other than either does with the tips and tricks world of the spreadsheet.

[@AccessAnalytic]

@@peterbartholomew7409 Things are definitely changing and Excel continues its growth as a programming platform. Thanks for following along

[@moshearama6612]

Hi !
how can i make function like excel in the power query
the excel function is :
=IF(AND(U3>=TIME(6,0,0),U3=TIME(15,1,0),U3

[@AccessAnalytic]

Paste something like this into a Custom Column
Saved in Temp folder
let
MorningStart=Time.FromText("6:00 AM"),
EveningStart = Time.FromText("3:00 PM"),
NightStart = Time.FromText("11:00 PM"),
Result =
if [Time] >= MorningStart and [Time] < EveningStart then "Morning"
else
if [Time] >= EveningStart and [Time] < NightStart then "Afternoon"
else
"Night"
in
Result

[@Dev_Bartwal]

Cool .. Very Good way of teaching

[@AccessAnalytic]

Thanks

[@norbertschmidt6532]

Dear Wyn,
thanks, GREAT as ever. SUPER.

[@AccessAnalytic]

Cheers Norbert

[@wayneedmondson1065]

Great solutions! Thanks Wyn!!

[@AccessAnalytic]

Cheers Wayne

[@Girhofamily]

Good on bro.

[@AccessAnalytic]

Thanks

[@MrWish332]

This is really very cool

[@AccessAnalytic]

Cheers Vishal

[@Bhavik_Khatri]

Thank you very much.

[@AccessAnalytic]

You’re welcome Bhavik