Custom Column Formula Time to total minutes only

[Grizzlydad]

Hi all

Hopefully this is very simple to do, I want the Mins column below to have a time value shown in minutes only so where there is an hour value it should count as total minutes,  most of them in the column are minutes only.  How do I write a custom column formula to do that.  In the example below 02:19 should show as 139 (minutes) and 01:12 should show as 72 (minutes).

All the best

 Time   Mins                               

02:19	132
01:12	72
03:54	234
23:45	1425

[AlB · Accepted solution]

@Grizzlydad 

Copy the previous code in a blank query so that you can see the steps and from there adapt it to what you have.

The code for the calculated column is:

Number.From(Text.Split([Time],":"){0})*60 + Number.From(Text.Split([Time],":"){1})

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB]

Hi @Grizzlydad 

Assuming your Time column is of type text, add a custom column:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjCyMrRUitUBsgytDI0gLGMrUxMwy8jYysRUKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Time = _t]),
    
   #"Added Custom" = Table.AddColumn(Source, "Mins", each Number.From(Text.Split([Time],":"){0})*60 + Number.From(Text.Split([Time],":"){1}) )
in
    #"Added Custom"

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any BI needs, tutoring, etc.

Cheers 

[Grizzlydad]

Thanks but this just returned Table as the entrie in the custom column?

[AlB · Accepted solution]

@Grizzlydad 

Copy the previous code in a blank query so that you can see the steps and from there adapt it to what you have.

The code for the calculated column is:

Number.From(Text.Split([Time],":"){0})*60 + Number.From(Text.Split([Time],":"){1})

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Grizzlydad]

Hiya, thnaks again but still returns Table in Mins column?

The column name for changed into mins is Time taken for response

[dax · Accepted solution]

Hi @Grizzlydad , 

You could refer to @AlB 's suggestion. I think you might use code like below in custom column, so you will get table instead of column value.

You  need to change this like below

Check code in Advanced Editor to see whether is similar to @AlB 's code. Or you could upload your M code, then we will try to test this in my environment

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.