Forum Discussion
Grouping Time by Hour Intervals
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!
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&""&_12. 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
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
10 Replies
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
Thank you! This one works
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"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?
Hour = TIMEVALUE( DateTime[Value] ) * 24Hi 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&""&_12. 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
Hello Lui,
I tried it out but im getting na error cause FORMAT is not allowed for direct query.
