Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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
Anonymous
Not applicable

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

Anonymous
Not applicable

@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
Not applicable

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
Not applicable

@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
Not applicable

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

Anonymous
Not applicable

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"

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors