Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Assign value (text) based on time range

Hello,    This is a two part question:   Part 1: change time to 24-hour format. Currently the date format is looking like this: 0:00:00 AM 4:00:00 AM 10:00:00 AM 17:00:00 PM 19:00:00 PM ...
Need Help
Nolock's avatar
Nolock
Resident Rockstar
7 years ago

Hi Anonymous,

to the part Nr. 1: just change the data type to time and it will be converted automatically.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrAyACEFR1+lWJ1oJRNUriFCWgEiYA4VCIAqsETixwIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [TimeColumn = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TimeColumn", type time}})
in
    #"Changed Type"

 

To the part Nr. 2:

Create a DIM table called "Peak_Hour_DIM" with peak hours by 30 minutes steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdCrDsJAEIXhV2lWl2RnZ6+yAkdDBa6pqKAGFI63J82K0rPHfub8M/NsrOnNdf28v90wmqWfjSA4BEXwCAEhIiSEjFCasDa1aZUmVppa2XOHsZue66tCQIgICSFXuG/b5cBC0FmGwtAxVIae4X7F7XE0uoiQEDJCAVBb4bSkwtAxVIZ7/fT3TA0IESEhZIRS4bTkLUNh6BgqQ88wMIwME+LyAw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [TimeHalfHour = _t, Peak_Hour = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TimeHalfHour", Int64.Type}})
in
    #"Changed Type"

Convert time into 30 minutes intervals and join the DIM table with your time values:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrAyACGlWJ1oJRMktiGyhKE5MscSmWNkZYzMMULmmEA4sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Time = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Time", type time}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "TimeToHalfHour", each Time.Hour([Time]) * 2 + Number.RoundDown(Time.Minute([Time]) / 30)),
#"Merged Queries" = Table.Join(#"Added Custom", {"TimeToHalfHour"}, Peak_Hour_DIM, {"TimeHalfHour"}, JoinKind.Inner),
#"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"TimeToHalfHour", "TimeHalfHour"})
in
#"Removed Columns"