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

[moshe_Arama2022]

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.

[m_dekorte]

Hi @moshe_Arama2022,

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
  )

[moshe_Arama2022]

hi thank you ! 

its helps me 

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

[m_dekorte · Accepted solution]

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!