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"))
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.

 

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!

3 Replies

  • m_dekorte's avatar
    m_dekorte
    Resident 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
        Resident 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!