Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Get Fabric Certified for FREE during Fabric Data Days. Don't miss your chance! Learn more

Reply
Babycakes
Helper II
Helper II

Power Query IF statement

‎10-02-2023 01:48 PM

Hi all  

I have attempted this several ways however cannot get the outcome.

I have a data set where i am trying to apply rules that if before and after certain times it is either afternoon or day rate.

 

if

"Shift Finish" is before or equal to 6 pm = Day rate

"Shift Start” is equal to or after 6 am = Day Rate

"Shift Start" is after 6 pm = Afternoon rate

 

However the shifts where they are over night with a sleepover start and end cause anoomlaies

The 3 pm to 10 pm component is Afternoon rate and the 6 am to 9 am is Day rate.

Any suggestions how I can overcome this?

 

Thanks 

Labels:
Message 1 of 4
559 Views
0
2 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

‎10-02-2023 07:56 PM

@Babycakes , A new column in the power query

 

Shift Rate =
if [Shift Start] >= #time(6, 0, 0) and [Shift Finish] <= #time(18, 0, 0) then "Day Rate"
else if [Shift Start] >= #time(15, 0, 0) and [Shift Finish] <= #time(22, 0, 0) then "Afternoon Rate"
else if [Shift Finish] <= #time(6, 0, 0) or [Shift Start] >= #time(18, 0, 0) then "Day Rate"
else "Invalid Shift"

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

Message 3 of 4
525 Views
Ritaf1983
Super User
Super User

‎10-02-2023 07:58 PM

Hi @Babycakes 
Please refer to the linked discussion:

https://community.fabric.microsoft.com/t5/Power-Query/Day-time-Night-time/td-p/1540432

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

 
Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

View solution in original post

Message 4 of 4
525 Views
0
3 REPLIES 3
Ritaf1983
Super User
Super User

‎10-02-2023 07:58 PM

Hi @Babycakes 
Please refer to the linked discussion:

https://community.fabric.microsoft.com/t5/Power-Query/Day-time-Night-time/td-p/1540432

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

 
Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile
Message 4 of 4
526 Views
0
amitchandak
Super User
Super User

‎10-02-2023 07:56 PM

@Babycakes , A new column in the power query

 

Shift Rate =
if [Shift Start] >= #time(6, 0, 0) and [Shift Finish] <= #time(18, 0, 0) then "Day Rate"
else if [Shift Start] >= #time(15, 0, 0) and [Shift Finish] <= #time(22, 0, 0) then "Afternoon Rate"
else if [Shift Finish] <= #time(6, 0, 0) or [Shift Start] >= #time(18, 0, 0) then "Day Rate"
else "Invalid Shift"

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Message 3 of 4
526 Views
Babycakes
Helper II
Helper II

‎10-02-2023 01:51 PM
Shift StartSleepover StartSleepover FinishAllocated Shift Break (mins)Shift FinishWorked Hours in DayShift SplitsTotal shift hoursWeekDay
4:00 PM11:00 PM7:00 AM09:00 AM7.00 420, 480, 12092Wednesday
4:00 PM11:00 PM7:00 AM09:00 AM2.00 420, 480, 12092Thursday
4:00 PM11:00 PM7:00 AM09:00 AM7.00 420, 480, 12092Monday
4:00 PM11:00 PM7:00 AM09:00 AM2.00 420, 480, 12092Tuesday
4:00 PM11:00 PM7:00 AM09:00 AM7.00 420, 480, 12091Saturday
4:00 PM11:00 PM7:00 AM09:00 AM2.00 420, 480, 12091Sunday
4:00 PM11:00 PM7:00 AM09:00 AM7.00 420, 480, 12091Thursday
4:00 PM11:00 PM7:00 AM09:00 AM2.00 420, 480, 12091Friday
9:15 AM  01:15 PM4.00 41Thursday
9:15 AM  01:15 PM4.00 42Thursday
9:00 AM  01:00 PM4.00 41Tuesday
9:15 AM  01:15 PM4.00 41Wednesday
3:00 PM10:00 PM6:00 AM09:00 AM7.00 420, 480, 180101Monday
3:00 PM10:00 PM6:00 AM09:00 AM3.00 420, 480, 180101Tuesday
2:00 PM10:00 PM6:00 AM09:00 AM8.00 480, 480, 180112Wednesday
2:00 PM10:00 PM6:00 AM09:00 AM3.00 480, 480, 180112Thursday
2:00 PM10:00 PM6:00 AM09:00 AM8.00 480, 480, 180111Wednesday
2:00 PM10:00 PM6:00 AM09:00 AM3.00 480, 480, 180111Thursday
3:00 PM10:00 PM6:00 AM06:00 AM7.00 420, 48072Sunday
3:00 PM10:00 PM6:00 AM06:00 AM0.00 420, 48071Monday
2:00 PM10:00 PM6:00 AM09:00 AM8.00 480, 480, 180111Thursday
2:00 PM10:00 PM6:00 AM09:00 AM3.00 480, 480, 180111Friday
12:30 PM  010:30 PM10.00 101Sunday
5:15 PM  011:00 PM5.75 5.752Thursday
9:00 AM  03:00 PM6.00 62Wednesday
9:00 AM  03:00 PM6.00 61Wednesday
9:00 AM  03:00 PM6.00 61Friday
9:00 AM  03:00 PM6.00 62Friday
10:00 AM  04:00 PM6.00 61Tuesday
10:00 AM  04:00 PM6.00 62Tuesday
9:00 AM  012:00 PM3.00 31Thursday
9:00 AM  012:00 PM3.00 32Thursday
12:00 PM  03:00 PM3.00 31Friday
12:00 PM  03:00 PM3.00 32Monday
12:00 PM  03:00 PM3.00 32Sunday
12:00 PM  03:00 PM3.00 31Friday
12:00 PM  03:00 PM3.00 32Friday
3:00 PM  05:00 PM2.00 21Tuesday
3:00 PM  05:00 PM2.00 21Wednesday
1:00 PM  03:00 PM2.00 21Thursday
1:00 PM  03:00 PM2.00 22Monday
1:00 PM  03:00 PM2.00 22Tuesday
3:00 PM  011:00 PM8.00 81Wednesday
3:00 PM10:00 PM6:00 AM09:00 AM7.00 420, 480, 180102Saturday
3:00 PM10:00 PM6:00 AM09:00 AM3.00 420, 480, 180102Sunday
3:00 PM10:00 PM6:00 AM09:00 AM7.00 420, 480, 180101Friday
3:00 PM10:00 PM6:00 AM09:00 AM3.00 420, 480, 180101Saturday
5:00 PM10:30 PM6:30 AM09:00 AM5.50 330, 480, 15081Thursday
5:00 PM10:30 PM6:30 AM09:00 AM2.50 330, 480, 15081Friday
5:00 PM10:30 PM6:30 AM09:00 AM5.50 330, 480, 15082Tuesday
5:00 PM10:30 PM6:30 AM09:00 AM2.50 330, 480, 15082Wednesday
5:00 PM10:30 PM6:30 AM09:00 AM5.50 330, 480, 15081Wednesday
5:00 PM10:30 PM6:30 AM09:00 AM2.50 330, 480, 15081Thursday
5:00 PM10:30 PM6:30 AM09:00 AM5.50 330, 480, 15081Saturday
5:00 PM10:30 PM6:30 AM09:00 AM2.50 330, 480, 15081Sunday
2:00 PM  011:00 PM9.00 92Monday
11:00 PM  07:00 AM1.00 60, 42082Sunday
11:00 PM  07:00 AM7.00 60, 42081Monday
3:00 PM  011:00 PM8.00 81Thursday
7:00 AM  03:00 PM8.00 82Saturday
2:00 PM  011:00 PM9.00 92Sunday
Message 2 of 4
497 Views
0

Helpful resources

Announcements
Fabric Data Days Carousel

Fabric Data Days

Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

View All