Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
moshe_Arama2022
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.

 

CONVERT TIME TO SHIFT EXCEL.PNG

1 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!

View solution in original post

3 REPLIES 3
m_dekorte
Super User
Super User

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
  )

 

hi thank you ! 

its helps me 

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

 

moshe_Arama2022_0-1682401279381.png

 

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!

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors