Forum Discussion

moshe_Arama2022's avatar
moshe_Arama2022
Frequent Visitor
3 years ago
Solved

- 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"...
General Comment
Need Help
  • m_dekorte's avatar
    m_dekorte
    3 years ago

    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!

m_dekorte's avatar
m_dekorte
Icon for Resident Rockstar rankResident Rockstar
3 years ago

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's avatar
    moshe_Arama2022
    Frequent Visitor
    3 years ago

    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's avatar
      m_dekorte
      Icon for Resident Rockstar rankResident Rockstar
      3 years ago

      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!