Grouping Time by Hour Intervals

[atjt217]

Hello, 

I hope someone can tell me how to fix this issue. I'm trying to create a calculated column that show time intervals by hour. What i did is i used add a column by hour on Power Query on CreatedOn filed which resulted to CreatedOn Hour then i used this formula: 

C_CreatedOn = IF(vu_Bi_UnableToFill_2019ToCurrent[CreatedOn Hour]>12, vu_Bi_UnableToFill_2019ToCurrent[CreatedOn Hour]-12&" "&"PM",vu_Bi_UnableToFill_2019ToCurrent[CreatedOn Hour]&" "&"AM")

To add the AM/PM on the hour. The problem is when the time is 12 instead of PM it shows AM. 

Please let me know what im doing wrong or if there i any other alternatives. Please note im using direct query. Thank you! 

[v-yangliu-msft · Accepted solution]

Hi  @atjt217 ,

Here are the steps you can follow：

1. Create calculated column.

C_CreateIOn =
var _1=FORMAT('Table'[CreateIOn],"AM/PM")
var _2=
IF(
    'Table'[CreateIOn Hour]>12,
    'Table'[CreateIOn Hour]-12,
    'Table'[CreateIOn Hour])
return
_2&""&_1

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[v-yangliu-msft · Accepted solution]

Hi  @atjt217 ,

Here are the steps you can follow：

1. Create calculated column.

C_CreateIOn =
SWITCH(
    TRUE(),
    'Table'[CreateIOn Hour]>12,'Table'[CreateIOn Hour]-12&""&"PM",
    'Table'[CreateIOn Hour]=12,'Table'[CreateIOn Hour]&""&"PM",
    'Table'[CreateIOn Hour]<12,'Table'[CreateIOn Hour]&""&"AM")

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[atjt217]

Thank you! This one works 

[v-yangliu-msft · Accepted solution]

Hi  @atjt217 ,

Here are the steps you can follow：

1. Create calculated column.

C_CreateIOn =
var _1=FORMAT('Table'[CreateIOn],"AM/PM")
var _2=
IF(
    'Table'[CreateIOn Hour]>12,
    'Table'[CreateIOn Hour]-12,
    'Table'[CreateIOn Hour])
return
_2&""&_1

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[atjt217]

Hello Lui, 

I tried it out but im getting na error cause FORMAT is not allowed for direct query. 

[CNENFRNL]

Hour = TIMEVALUE( DateTime[Value] ) * 24

[atjt217]

Hello CNENFRNL,

How can i add the AM/PM on the time?

[CNENFRNL]

Hour = FORMAT( DateTime[Value], "HH AM/PM" )

[atjt217]

Hi CNENFRNL, 

I tried this but im getting an error that "FORMAT" is not allowed on direct query Do you have any other options for me? 

[AllisonKennedy]

@atjt217  You can do all of it in Power Query, here's one example that should work:

let
StartDate = #datetime(2020,7,1,0,0,0),
EndDate = #datetime(2030,12,31,0,0,0),
DateTime = List.DateTimes(StartDate, Duration.Days(EndDate-StartDate), #duration(0,1,0,0)),
#"Converted to Table" = Table.FromList(DateTime, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "DateTime"}}),
#"Inserted Time" = Table.AddColumn(#"Renamed Columns", "Time", each DateTime.Time([DateTime]), type time),
#"Inserted Text Before Delimiter" = Table.AddColumn(#"Inserted Time", "Hour", each Text.BeforeDelimiter(Text.From([Time], "en-US"), ":"), type text),
#"Inserted Last Characters" = Table.AddColumn(#"Inserted Text Before Delimiter", "Last Characters", each Text.End(Text.From([Time], "en-US"), 2), type text)
in
#"Inserted Last Characters"

[atjt217]

Hello Allison, 

I tried this out but it switches my table to Import mode and i need this to still be a direct query. Do you have any other options for me?