Assign value (text) based on time range

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.