Forum Discussion

hankmobley's avatar
hankmobley
Icon for Helper I rankHelper I
4 years ago

Calculate new value/column if time falls within a range

Hi all, I have a timestamp column called "Transaction Time" that I get via Direct Query in this format: "Sat Feb 26 08:36:53.656 AEDT 2022".  I would like to create a new column with the following c...
Need Help
davehus's avatar
davehus
Icon for Memorable Member rankMemorable Member
4 years ago

Hi hankmobley ,

 

In Power Query in the table with the Transaction time, create the following column to parse the time from your column.

 

ParseTime = Text.Middle([Date],11,8)

Change Data Type to Time and rename to Time

Add a custom column and input the calculation below

= if [Time] > Time.FromText("09:00:00") and [Time] < Time.From("18:59:00") then "Day" else "Night"

If this meets your needs, please accept my answer.

hankmobley's avatar
hankmobley
Icon for Helper I rankHelper I
4 years ago

Hi davehus 

Thanks for your reply.  Can you please clarify how I create the new ParseTime column?

When I'm in Power Query, do I select the column with the current long format transaction time and duplicate it?  Then once I duplicate it, do I enter: ParseTime = Text.Middle([Date],11,8) in the formula bar?

Thanks.

  • davehus's avatar
    davehus
    Icon for Memorable Member rankMemorable Member
    4 years ago

    Hi hankmobley ,

    No need to do that. Just create a custom colum and paste the formula in. Change [Date] to the desired column name in the formula and complete the rest of the steps. 🙂

     

    D

    • hankmobley's avatar
      hankmobley
      Icon for Helper I rankHelper I
      4 years ago

      Hi davehus 

      I created a custom column, pasted the formula and changed [Date] to the the desired column name:

       

      ...but when I cliked OK, it brings up this error:

      Can you see something I'm doing wrong from these screenshots?

      Thanks.