cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Frequent Visitor

## - Help - covert excel formula to power query - CONVERT TIME TO SHIFT NAME

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,0,0)),"Morning",IF(AND(U3>=TIME(15,1,0),U3<=TIME(23,0,0)),"Evening",
"Night"))
U3 = COLUMN with time_for_shift_segmentation LIKE 10:59:00 I mean, based on the time, I want to know which shift it belongs.

1 ACCEPTED SOLUTION
Super User

You can copy the logic from my previous reply after the each-expression in the formula bar of that step

``if [time_for_shift_segmentation] >= #time(6, 0, 0) and [time_for_shift_segmentation] < #time(15, 0, 0) then "Morning" else if [time_for_shift_segmentation] >= #time(15, 0, 0) and [time_for_shift_segmentation] < #time(23, 0, 0) then "Evening" else "Night"``

Make sure to keep the closing parenthesis of the Table.AddColumn function.

Ps. If this helps solve your query please mark this post as Solution, thanks!

3 REPLIES 3
Super User

You can add a conditional column for that. Note that above you'd included that both start and end time in a Shift therefore 15:00 would belong to Morning not Evening, I adjusted that below but you can change that of course.

Give something like this a go

``````AddShift = Table.AddColumn( PrevStepNameHere, "Custom", each
if [time_for_shift_segmentation] >= #time(6, 0, 0) and [time_for_shift_segmentation] < #time(15, 0, 0) then "Morning"
else if [time_for_shift_segmentation] >= #time(15, 0, 0) and [time_for_shift_segmentation] < #time(23, 0, 0) then "Evening"
else "Night",
type text
)``````

Frequent Visitor

hi thank you !

its helps me

bay the way i have tried to use in conditional column but i didnt get what i need

Super User

You can copy the logic from my previous reply after the each-expression in the formula bar of that step

``if [time_for_shift_segmentation] >= #time(6, 0, 0) and [time_for_shift_segmentation] < #time(15, 0, 0) then "Morning" else if [time_for_shift_segmentation] >= #time(15, 0, 0) and [time_for_shift_segmentation] < #time(23, 0, 0) then "Evening" else "Night"``

Make sure to keep the closing parenthesis of the Table.AddColumn function.

Ps. If this helps solve your query please mark this post as Solution, thanks!