Create Custom Time Range Field (power Query)

[Anonymous]

I want to create a custom field from a time field that generates time between intervals. For example, anytime between 12am - 3am will be "0 AM - 3 AM", 3am - 6am will be "3 AM - 6 AM" and so on like i have in the snapshot below. Thanks in advance

snapshot

 

my data looks like this

[lbendlin]

Let's ignore for a second that you have overlapping intervals.

Create a custom column with a simple if chain (pseudo code below)

snapshot = each if time < 3 then "0 - 3" else if time < 6 then "3 - 6"  ... else if time < 21 then "18 - 21" else "21 - 24" 

[Anonymous]

@lbendlin 

Thanks for responding, i created the code using your format but i got an error that it's not seeing the "Time" field but it's actually there. Could you please view my code and let me know what my be wrong. Thanks

= Table.AddColumn(#"Inserted Time", "Time Range", each if "Time" < 3 then "0 AM - 3 AM" else if Time < 6 then "3 AM - 6 AM" else if Time < 9 then "6 AM - 9 AM" else if Time < 12 then "9 AM - 12 PM" else if Time < 15 then "12 PM - 3 PM" else if Time < 18 then "3 PM - 6 PM" else if Time < 21 then "6 PM - 9 PM" else "21 - 24")

[lbendlin · Accepted solution]

This was just pseudo code. The actual code looks more like this

    #"Added Custom" = Table.AddColumn(#"Changed Type", "snapshot", each if [Time] < #time(3, 0, 0) then "0 AM - 3 AM" else if [Time] < #time(6, 0, 0) then "3 AM - 6 AM" else if [Time] < #time(9, 0, 0) then "6 AM - 9 AM" else if [Time] < #time(12, 0, 0) then "9 AM - 12 PM" else if [Time] < #time(15, 0, 0) then "12 PM - 3 PM" else if [Time] < #time(18, 0, 0) then "3 PM - 6 PM" else if [Time] < #time(21, 0, 0) then "6 PM - 9 PM" else "9 PM - 12 AM")

[Anonymous]

@lbendlin Thanks so much, it worked.