Time Calculation

[Anonymous]

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]

Here is the M code to get the result you need. 

Just import the data and copy the code into Advanced Editor (correct source of your file)

let
    Source = Excel.Workbook(File.Contents("C:\Users\rolan\Downloads\Data_test_5.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Split Column by Position" = Table.SplitColumn(Table.TransformColumnTypes(#"Promoted Headers", {{"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 Type" = Table.TransformColumnTypes(#"Merged Columns",{{"Time", type time}})
in
    #"Changed Type"

[Anonymous]

Hi Rolands, thank you for the response. I'm very new to Power Bi so sorry if this seems like a stupid question. The import has already been done and is from an SQL connection. 

If i change the line from the excel work book and add it like this, I get an error. I guess I have to change quite a bit?

let Source = (XPLAN52[HistoryTime], null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Split Column by Position" = Table.SplitColumn(Table.TransformColumnTypes(#"Promoted Headers", {{"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 Type" = Table.TransformColumnTypes(#"Merged Columns",{{"Time", type time}})
in
#"Changed Type"

[RolandsP]

Yes, you will need to make some small adjustments to the code.
Could you please send me the Query editor code you have right after data import?

[Anonymous]

Hi Rolands, not sure if this is correc? If not let me know how to get this (very noob to this)

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}})
in
#"Changed Type"

[RolandsP]

Yes, that is what I need.

Please try this code.

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"

[RolandsP]

Yes, that is what I need.

Please try this code.

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"

[Anonymous]

I'm afraid not, screenshot attached

[RolandsP · Accepted solution]

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"

[Anonymous]

I'm afraid not again

[Anonymous]

sorry wromg screenshot 

[RolandsP]

I just noticed that you are actually placing this code in wrong place - in Formula Bar in Power BI Desktop where you write DAX expressions for measure and columns.

You should be placing it in Advanced Editor in the same place from where you copied the code for querying SQL database.

If you do not know how to find it, let me know I will write detailed instructions.

[Anonymous]

Hi Rolands, told you I was noob. This certainly did the trick. Thank you so much

[RolandsP]

All fine. Keep learning.