Solved: Time Calculation - Page 2

Anonymous · ‎01-09-2018

Hello all, as per the below screenshot i have the column 'historytime' which is in the format HHMMSS then 2 random zero's. I would like to format this into HH:MM:SS and remove the last 2 zero's.

Also anything prior for 12pm is has it in format HMMSS so need to take this into account.

RolandsP · ‎01-09-2018

Ouch. One comma was missing. I hope now it is working 🙂

let
Source = Sql.Databases("ghw-srv-09\xplansvr"),
XPLAN = Source{[Name="XPLAN"]}[Data],
dbo_XPLAN52 = XPLAN{[Schema="dbo",Item="XPLAN52"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(dbo_XPLAN52,{{"HistoryTime", Int64.Type}}),
#"Split Column by Position" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type", {{"HistoryTime", type text}}, "en-GB"), "HistoryTime", Splitter.SplitTextByPositions({0, 2}, true), {"HistoryTime.1", "HistoryTime.2"}),
#"Removed Columns" = Table.RemoveColumns(#"Split Column by Position",{"HistoryTime.2"}),
#"Split Column by Position1" = Table.SplitColumn(Table.TransformColumnTypes(#"Removed Columns", {{"HistoryTime.1", type text}}, "en-GB"), "HistoryTime.1", Splitter.SplitTextByPositions({0, 2}, true), {"HistoryTime.1.1", "HistoryTime.1.2"}),
#"Split Column by Position2" = Table.SplitColumn(#"Split Column by Position1", "HistoryTime.1.1", Splitter.SplitTextByPositions({0, 2}, true), {"HistoryTime.1.1.1", "HistoryTime.1.1.2"}),
#"Merged Columns" = Table.CombineColumns(#"Split Column by Position2",{"HistoryTime.1.1.1", "HistoryTime.1.1.2", "HistoryTime.1.2"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Time"),
#"Changed Type2" = Table.TransformColumnTypes(#"Merged Columns",{{"Time", type time}})
in
#"Changed Type2"

View solution in original post

Time Calculation

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024

How to Get Your Question Answered Quickly