Forum Discussion
Create Custom Time Range Field (power Query)
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 ...
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")
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")
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")
lbendlin Thanks so much, it worked.