Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

Time Calculation

‎01-09-2018 12:51 AM

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.

 

Capture.PNG

Labels:
Message 1 of 14
1,010 Views
0
1 ACCEPTED SOLUTION
RolandsP
Resolver IV
Resolver IV
In response to Anonymous

‎01-09-2018 08:00 AM

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

Message 8 of 14
1,040 Views
0
13 REPLIES 13
RolandsP
Resolver IV
Resolver IV

‎01-09-2018 01:19 AM

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"
Message 2 of 14
988 Views
0
Anonymous
Not applicable
In response to RolandsP

‎01-09-2018 05:27 AM

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"

Message 3 of 14
976 Views
0
RolandsP
Resolver IV
Resolver IV
In response to Anonymous

‎01-09-2018 05:40 AM

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?

Message 4 of 14
971 Views
0
Anonymous
Not applicable
In response to RolandsP

‎01-09-2018 05:44 AM

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"

Message 5 of 14
968 Views
0
RolandsP
Resolver IV
Resolver IV
In response to Anonymous

‎01-09-2018 06:36 AM

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"
Message 14 of 14
917 Views
0
RolandsP
Resolver IV
Resolver IV
In response to Anonymous

‎01-09-2018 06:36 AM

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"
Message 6 of 14
960 Views
0
Anonymous
Not applicable
In response to RolandsP

‎01-09-2018 06:56 AM

I'm afraid not, screenshot attached

 

Capture.PNG

Message 7 of 14
953 Views
0
RolandsP
Resolver IV
Resolver IV
In response to Anonymous

‎01-09-2018 08:00 AM

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"
Message 8 of 14
1,041 Views
0
Anonymous
Not applicable
In response to RolandsP

‎01-09-2018 08:18 AM

I'm afraid not again

 

Capture.PNG

Message 9 of 14
940 Views
0
Anonymous
Not applicable
In response to Anonymous

‎01-09-2018 08:19 AM

sorry wromg screenshot 

 

Capture.PNG

Message 10 of 14
679 Views
0
RolandsP
Resolver IV
Resolver IV
In response to Anonymous

‎01-09-2018 08:51 AM

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.

 

Message 11 of 14
675 Views
0
Anonymous
Not applicable
In response to RolandsP

‎01-09-2018 09:39 AM

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

Message 12 of 14
667 Views
0
RolandsP
Resolver IV
Resolver IV
In response to Anonymous

‎01-09-2018 10:18 AM

All fine. Keep learning.

Message 13 of 14
662 Views
0

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

View All