Solved: How to convert and round a time column with second...

BSXS · ‎10-19-2017

I have a fact table with a 24h time column 00:23:03, 13:56:45 etc. I would like to create a new column without seconds and where minutes is rounded to 00, 15, 30 or 45.

First 14 minutes goes to 13:00, minutes 15 -29 goes to 13:15, minutes 30-44 goes to 13:30, 45-59 goes to 13:45

Output to NEW text column like 1315, 0030 or 13:30

Suggestions in M query (preferabale) or as calculated column

Thanks

Omega · ‎10-19-2017

I was able to do it by creating a calculated column as I'm not an expert in M query

Try the following

Change time column format to text instead of date/time. Then create a calculated column:

Column = LEFT(Sheet1[Time],2)&":"&
SWITCH(TRUE(),
VALUE(MID(Sheet1[Time],4,2))<15,"00",
AND(VALUE(MID(Sheet1[Time],4,2))>=15,VALUE(MID(Sheet1[Time],4,2))<30),"15",
AND(VALUE(MID(Sheet1[Time],4,2))>=30,VALUE(MID(Sheet1[Time],4,2))<45),"30",
"45")

View solution in original post

BSXS · ‎10-19-2017

Calculated Column:

It work fine with all 890 000 rows

But I had to change to text column inside Query. It didnt work to just change in Desktop

Thanks all and now I will try inside M Query.

One question though, peformance wise I knwo the differenxe between Measure (=CPU) and Calc. column (RAM)

But doing a new column in PBI M Query compared to a calculated column?

View solution in original post

dilumd · ‎10-19-2017

Hi

I think you can do this in power quary.

Please refer my below query. (Hope you can understand this) else tell I'll explain it for you.

let
    Source = Excel.Workbook(File.Contents("C:\Users\Dilumd\OneDrive - \PowerBi\Help\time.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"#", type text}, {"Time", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Time", "Time - Copy"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Time - Copy", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Time - Copy.1", "Time - Copy.2", "Time - Copy.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Time - Copy.1", type text}, {"Time - Copy.2", Int64.Type}, {"Time - Copy.3", Int64.Type}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type1", "Min_Rounding", each if [#"Time - Copy.2"] <= 15 then "00" else if [#"Time - Copy.2"] <= 30 then "15" else if [#"Time - Copy.2"] <= 45 then "30" else if [#"Time - Copy.2"] <= 59 then "45" else "00" ),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Min_Rounding", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type2", "Time_New", each [#"Time - Copy.1"]&":"&[Min_Rounding]),
    #"Changed Type3" = Table.TransformColumnTypes(#"Added Custom",{{"Time_New", type text}})
in
    #"Changed Type3"

BSXS · ‎10-19-2017

Skärmklipp.JPG

Hi, didnt work well for me. Here is my script. See screenshot as well. My Problem is also to add a ZERO to the singel digit value ´to the hours from 0-9 but maybe your solution is handling that?

let
    Source = Sql.Databases("XXXX01"),
    XXXX_RPT = Source{[Name="SLLIT_RPT"]}[Data],
    rpt_vCallData = XXXX_RPT{[Schema="rpt",Item="vCallData"]}[Data],
    #"Duplicated Column" = Table.DuplicateColumn(rpt_vCallData, "CreatedTime", "CreatedTime - Copy"),
    #"Calculated Start of Hour" = Table.TransformColumns(#"Duplicated Column",{{"CreatedTime - Copy", Time.StartOfHour, type time}}),
    #"Extracted Hour" = Table.TransformColumns(#"Calculated Start of Hour",{{"CreatedTime - Copy", Time.Hour}}),
    #"Renamed Columns" = Table.RenameColumns(#"Extracted Hour",{{"CreatedTime - Copy", "Hour"}}),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Renamed Columns", "CreatedTime", "CreatedTime - Copy"),
    #"Renamed Columns1" = Table.RenameColumns(#"Duplicated Column1",{{"CreatedTime - Copy", "Timmar"}}),
    #"Calculated Start of Hour1" = Table.TransformColumns(#"Renamed Columns1",{{"Timmar", Time.StartOfHour, type time}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Calculated Start of Hour1",{{"Timmar", type time}, {"CreatedTime", type text}}),
    #"Duplicated Column2" = Table.DuplicateColumn(#"Changed Type", "CreatedTime", "CreatedTime - Copy"),
    #"Renamed Columns2" = Table.RenameColumns(#"Duplicated Column2",{{"CreatedTime - Copy", "INTTime"}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Renamed Columns2", "INTTime", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"INTTime.1", "INTTime.2", "INTTime.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"INTTime.1", Int64.Type}, {"INTTime.2", Int64.Type}, {"INTTime.3", Currency.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"INTTime.3"}),
    #"Added Conditional Column" = Table.AddColumn(#"Removed Columns", "Custom", each if [INTTime.2] < 15 then "00" else if [INTTime.2] < 30 then "15" else if [INTTime.2] < 45 then "30" else if [INTTime.2] <= 59 then "45" else "00" ),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Custom", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type2", "Custom.1", each [INTTime.1]&[Custom])
in
    #"Added Custom"

MarcelBeug · ‎10-19-2017

Maybe try my solution?

Specializing in Power Query Formula Language (M)

MarcelBeug · ‎10-19-2017

Or just:

let
    Source = #table(type table[Time = time],{{#time(3,14,59)},{#time(13,15,00)},{#time(13,46,00)}}),
    #"Added Custom" = Table.AddColumn(Source, "TimeText", each 100*Time.Hour([Time])+15*Number.IntegerDivide(Time.Minute([Time]),15)),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"TimeText", type text}}),
    LeadingZeroes = Table.TransformColumns(#"Changed Type",{{"TimeText", each Text.PadStart(_,4,"0")}})
in
    LeadingZeroes