How to group time into 2

[jmars]

Hello everyone,

I am relatively new to Power Query and have encountered a problem that requires assistance from experienced users.

I have datasets where I need to group time into two categories.

Category 1: 12:00 AM to 03:00 PM

Category 2: 03:01 PM to 11:59 PM

How can I achieve this?

Any help would be greatly appreciated.

Thank you.

[Dhairya · Accepted solution]

Hey @jmars 

Method 1: Using DAX (calculated column)

I tried creating your scenario and took the below data as input

Input Table:

Please follow the below steps to get your expected output:

Step 1: Created a calculated column using the below code

time = FORMAT(TableTime[date], "hh:nn")

You will get the output below

Step 2: Created a new calculated column to split your date into expected category

Category = 
IF(
    TableTime[time] >= TIME(00,00,00) && TableTime[time] <= TIME(15,00,00), 
    "Category 1",
    "Category 2"
) 

You will get the output below

Method 2: Using Power Query

Input Table:

Please follow the below steps to get your expected output:

Step 1:  Create a Time column from the transform tab as shown below

Step 2:  Now create a custom column using the below code as shown in screenshot

if 
[Time] >= #time(00,00,00) and 
[Time] <= #time(15,00,00)
then "Category 1"
else "Category 2"

You will get the expected output as below

Here is the whole Power Query for all transformation

let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"i45WMrDUN7DQNzIwMlIwNLIyMAAiBUdfpVgdFCkDQ9xSxlCpAKxShjikTHHqMjS0MrWEScUCAA==", 
BinaryEncoding.Base64
), 
Compression.Deflate
)
), 
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [date = _t]
), 
#"Changed Type" = Table.TransformColumnTypes(Source, {{"date", type datetime}}), 
#"Inserted Time" = Table.AddColumn(#"Changed Type", "Time", each DateTime.Time([date]), type time), 
#"Added Custom" = Table.AddColumn(
#"Inserted Time", 
"Category", 
each 
if [Time] >= #time(00, 00, 00) and [Time] <= #time(15, 00, 00) then
"Category 1"
else
"Category 2"
)
in
#"Added Custom"

If this helps you then please mark my solution as accepted so that others can find it quickly while facing a similar issue. Thank You!

[jmars]

Thank you! It worked as expected!