Assign value (text) based on time range

[Anonymous]

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

Part 2:

assign a value (Peak Hour) into a new column based on whether another column (Time) belongs to one of the folllowing ranges:

Early AM: 0:00:00 and 7:00:00
AM Peak: 7:00:00 and 9:00:00
AM Off-Peak: 9:00:00 and 12:30:00
LT Peak: 12:30:00 and 15:00:00
LT Off-Peak: 15:00:00 and 17:00:00
PM Peak: 17:00:00 and 19:30:00
PM Off-Peak: 19:30:00 and 0:00:00

The output would look something like this:

Time                Peak_Hour
0:00:00             Early AM
4:00:00             Early AM
10:00:00            AM Off-Peak
17:00:00            LT Off-Peak
19:00:00            PM Peak

I'm not sure if this is doable on Power Bi. I'd rather not use the IF formula in excel because the csv file I'm using is really large.

Thanks.

[v-juanli-msft]

Hi @Anonymous 

Part 1:

change time to 24-hour format.

answer:

Time.ToText([Column1], "hh:mm:ss")

Part2:

As tested, if the time range is 

Early AM: 0:00:00 and 7:00:00 (0:00<=Early AM<7:00:00)
AM Peak: 7:00:00 and 9:00:00 (7:00:00 <=AM Peak<9:00:00)

The result should be

To get this, i create a query "dim date" using the following data as data source

et
    Source = Excel.Workbook(File.Contents("C:\Users\maggiel\Desktop\case\8\8.12\8.12.xlsx"), null, true),
    Sheet6_Sheet = Source{[Item="Sheet6",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sheet6_Sheet,{{"Column1", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, false), {"Column1.1", "Column1.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Column1.2", Splitter.SplitTextByDelimiter("and", QuoteStyle.Csv), {"Column1.2.1", "Column1.2.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Column1.2.1", type time}, {"Column1.2.2", type time}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type2",{{"Column1.1", Text.Trim, type text}}),
    #"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"Column1.1", Text.Clean, type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Cleaned Text",{{"Column1.1", "Peak Hour"}, {"Column1.2.1", "start"}, {"Column1.2.2", "end"}}),
    #"Added Custom2" = Table.AddColumn(#"Renamed Columns", "Custom", each List.Times(#time(0, 0, 0), 48, #duration(0, 0, 30, 0))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom2", "Custom"),
    #"Changed Type3" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type time}}),
    #"Added Custom3" = Table.AddColumn(#"Changed Type3", "Custom.1", each if ([Custom] >= [start] and [Custom]<[end]) or ([end]=#time(0, 0, 0) and [Custom]>=[start]) then 1 else null),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom3", each [Custom.1] <> null and [Custom.1] <> ""),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom.2", each Time.ToText([Custom], "hh:mm:ss")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"start", "end", "Custom.1"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Custom", "TIME"}, {"Custom.2", "TIME TEXT"}})
in
    #"Renamed Columns1"

Then in "Time" table, merge queries

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrEyMAAiBUdfpVidaCVDAzS+OZQfAOVbIvFjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"12:00:00 AM" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"12:00:00 AM", type time}}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type time}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "column time text", each Time.ToText([Column1], "hh:mm:ss")),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"column time text"}, #"dim date", {"TIME TEXT"}, "dim date", JoinKind.LeftOuter),
    #"Expanded dim date" = Table.ExpandTableColumn(#"Merged Queries", "dim date", {"Peak Hour"}, {"dim date.Peak Hour"})
in
    #"Expanded dim date"

You could open my file to see details.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello Maggie, 

Thanks for the response. Could you please attach the excel sheets as well so that I can follow with Column headings in your query? 

Azza

[v-juanli-msft]

Hi @Anonymous 

Nolock 's solution should be helpful.

If your second table has a structure as below

Early AM: 0:00:00 and 7:00:00

You could convert time range into a list, so that you could get a DIM table called "Peak_Hour_DIM" as Nolock provided.

https://community.powerbi.com/t5/Desktop/Convert-date-ranges-into-list-of-dates/td-p/129418

If it is not sloved, please let me know.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Nolock]

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"