How to fix time that has seconds greater than 60?

[Anonymous]

Format: TEXT "ABC

Example:

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!!

[Anonymous]

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

[Anonymous]

@Anonymous  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!

[Anonymous]

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

[Anonymous]

@Anonymous , @Anonymous 

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)

[Anonymous · Accepted solution]

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)))

 

[Anonymous]

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"