Forum Discussion

Grizzlydad's avatar
Grizzlydad
Helper I
6 years ago
Solved

Custom Column Formula Time to total minutes only

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:19132
01:1272
03:54234
23:451425
Need Help
  • AlB's avatar
    AlB
    6 years ago

    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 

     

  • dax's avatar
    dax
    6 years ago

    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.

     

5 Replies

  • AlB's avatar
    AlB
    Community Champion
    6 years ago

    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's avatar
      Grizzlydad
      Helper I
      6 years ago

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

      • AlB's avatar
        AlB
        Community Champion
        6 years ago

        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