How to add hours to datatime based on a condition in Power Query?

[Brij]

Hi,

I need some help in adding a new DateTime column based on a condition on an existing DateTime column.

Requirement:

Thank you,

Brijesh

[VahidDM]

Hi @Brij 

Update: Oh you need PQ solution, sorry missed that part

Try this DAX code to add a calculated column to your table:

Final Output =
VAR _Time =
    TIMEVALUE ( 'Table'[Date Time] )
VAR _Date =
    DATEVALUE ( 'Table'[Date Time] )
VAR _5_PM =
    TIME ( 17, 00, 00 )
VAR _9_AM =
    TIME ( 9, 0, 0 )
RETURN
    IF ( _Time > _5_PM, _Date + 1 + _9_AM, 'Table'[Date Time] )

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn | Twitter | Blog | YouTube 

[Brij]

Thanks Vahid,

Actually, I was looking for this solution in Power Query as I need to convert this output column data type to 'Date' to be able to make the relationship with DimDate table in the model. Are you able to help in Power BI? 

[VahidDM]

Hi @Brij 

Try this DAX to change the output to the Date, and use that for relationship (make sure the format is Date for that column)

Final Output =
VAR _Time =
    TIMEVALUE ( 'Table'[Date Time] )
VAR _Date =
    DATEVALUE ( 'Table'[Date Time] )
VAR _5_PM =
    TIME ( 17, 00, 00 )
VAR _9_AM =
    TIME ( 9, 0, 0 )
Var _Out = 
    IF ( _Time > _5_PM, _Date + 1 + _9_AM, 'Table'[Date Time] )
return
    DATEVALUE ( _Out )

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn | Twitter | Blog | YouTube 

[Brij]

Hi @VahidDM 

Thank you again for the effort.

This looks good. However, one thing I have noticed with this solution is that if a 'start_date_time' is on Friday after 5 PM then it is returning Saturday's date with 9 AM time! Is it possible if it checks for the weekend and return the following Monday date? 

Thank you

Brijesh