cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

How to fix time that has seconds greater than 60?

Format: TEXT "ABC

Example:

 

HORA.PNG

Expected result: 07:40:00 FOMAT "TIME"

 


I've tried to convert directly to the column, but it results in an error

 

Would anyone have a solution?

 

Thanks!!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Memorable Member
Memorable Member

Re: uRe: How to fix time that has seconds greater than 60?

another safe use of List.Accumulate

 

= Table.AddColumn(
      #"Changed Type", 
      "textToTime", 
      each Time.From(Number.Mod(List.Accumulate(Text.Split([Column1], ":"),0,(s,c)=>s*60+ Number.From(c)),(24*60*60))/(24*60*60)))

 image.png

View solution in original post

6 REPLIES 6
Highlighted
Solution Sage
Solution Sage

Re: How to fix time that has seconds greater than 60?

Can you not just highlight the column and change all "60" values to either "59" or "00" 

Highlighted
Regular Visitor

Re: How to fix time that has seconds greater than 60?

@Karlos  no, for two reasons

 

1- these numbers are sql conversion errors, so you must have exactly the number of seconds that exist.

2- as i said the number i sent was an example, but i have hours with 60, 61, 80, anyway, several others that creating a rule for each of them would be unfeasible.

 

in excel i can convert using the formula "ABS", which transforms the text into a number. being in number it is possible to convert at the right time again. However, the abs formula in the power query does not work, returns in error.

 

but thanks for the help!

Highlighted
Memorable Member
Memorable Member

Re: How to fix time that has seconds greater than 60?

it's not completely clear all the situations you can have as input, but this script can handle at least a good part of them

 

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrcytrQyM1CK1YlWMjADccxNYRxTSytDI2Mwz8gYxDMzVIqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "textToTime", each let t=Text.Split([Column1],":"),s=Number.From(t{2}),m=Number.From(t{1}),h=Number.From(t{0}) in 
    #time(   Number.Mod(h+Number.IntegerDivide(m+Number.IntegerDivide(s,60 ),60 ),24)  , Number.Mod(m+Number.IntegerDivide(s,60 ),60 ) ,Number.Mod(s,60 )))
in
    #"Added Custom"

 

 

 

Highlighted
Solution Sage
Solution Sage

uRe: How to fix time that has seconds greater than 60?

Fair enough, 

 

Well, the only way I can think of is by splitting it, creating conditional columns, then merging them back together. there will likely be a more elegant solution.

    #"Split Column by Delimiter" = Table.SplitColumn(Source, "HR_ENT", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"HR_ENT.1", "HR_ENT.2", "HR_ENT.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"HR_ENT.1", Int64.Type}, {"HR_ENT.2", Int64.Type}, {"HR_ENT.3", Int64.Type}}),
    #"Added Conditional Column1" = Table.AddColumn(#"Changed Type1", "Custom.1", each if [HR_ENT.2] >= 60 then 59 else [HR_ENT.2]),
    #"Added Conditional Column" = Table.AddColumn(#"Added Conditional Column1", "Custom", each if [HR_ENT.3] >= 60 then 59 else [HR_ENT.3]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"HR_ENT.2", "HR_ENT.3"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Removed Columns", {{"HR_ENT.1", type text}, {"Custom.1", type text}, {"Custom", type text}}, "en-GB"),{"HR_ENT.1", "Custom.1", "Custom"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Merged Columns",{{"Merged", type time}})
in
    #"Changed Type2"

 

See this pbix file to see how I did it 

https://1drv.ms/u/s!AnIEh6WhI4JogrAnieEvmoXWH53lHw?e=DCXCpj

Highlighted
Memorable Member
Memorable Member

Re: uRe: How to fix time that has seconds greater than 60?

another safe use of List.Accumulate

 

= Table.AddColumn(
      #"Changed Type", 
      "textToTime", 
      each Time.From(Number.Mod(List.Accumulate(Text.Split([Column1], ":"),0,(s,c)=>s*60+ Number.From(c)),(24*60*60))/(24*60*60)))

 image.png

View solution in original post

Highlighted
Regular Visitor

Re: uRe: How to fix time that has seconds greater than 60?

@Karlos , @Rocco_sprmnt21 

 

thanks for help!

 

Doing some tests I managed to arrive at this solution:

 

I used the function Text.Start and Text.End to cut the text, and the function Number.FromText to transform the text into value. I multiplied the value corresponding to hours by 3600, and the value of minutes by 60. I added up all the values ​​and divided the total by 86400. Arriving at the result I used the column formatting to transform the decimal number in hour.

 

#"Added custom" = Table.AddColumn(#"Filtered rows", "Custom", each (Number.FromText(Text.Start([HR_ENT], 2))*3600 + Number.FromText(Text.End(Text.Start([HR_ENT],5),2)) * 60 + Number.FromText(Text.End([HR_ENT],2)))/86400)

 

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors