Forum Discussion

smjzahid's avatar
smjzahid
Helper V
4 years ago
Solved

How to add TIME values in DAX or Power Query

I have a colum of type TIME datatype and I want to sum the values in this column to find out TOTAL HOURS or TOTAL MINUTES WORKED.

 

Doing this in excel is really straight forward, but I cannot do this in Power BI Desktop using (DAX or Power Query)

 

How do I do this in DAX or Power Query, 

Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    4 years ago

    Hi  smjzahid ,

     

    Using below M codes to get the total hours and minutes:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrIyNLAyMFWK1YlWMrAyArINoGwDA6CUUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Total Time" = _t]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Total Time", type time}}, "zh-CN"),
    #"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "hours", each Time.Hour([Total Time])+Time.Minute([Total Time])/60+Time.Second([Total Time])/3600),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Total hours", each List.Sum(#"Added Custom"[hours])),
    #"Extracted Text Range" = Table.TransformColumns(#"Added Custom1", {{"Total hours", each Text.Middle(Text.From(_, "en-US"), 0, 5), type text}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Extracted Text Range",{{"Total hours", type number}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type", "Minutes", each Time.Hour([Total Time])*60+Time.Minute([Total Time])+Time.Second([Total Time])/60),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Total Minutes", each List.Sum(#"Added Custom2"[Minutes])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"Minutes", "hours"})
in
    #"Removed Columns"

    And you will see:

     

    Or you could create 4 columns using dax expressions to get the result.

    For details,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my reply as a solution!

4 Replies

  • HotChilli's avatar
    HotChilli
    Community Champion
    4 years ago

    A Time datatype in powerbi/power query is a time of the day e.g. 12.51pm  so adding them doesn't make sense.

    If you can change the datatype to be duration in Power Query there are functions to get TotalHours or TotalMinutes.

     

    In DAX, you can parse your time as a datetime or text and use an algorithm to work out total hours or minutes .  I think there are some blog posts on here about it.  There are definitely lots of posts about it.

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    4 years ago

    Hi  smjzahid ,

     

    Using below M codes to get the total hours and minutes:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrIyNLAyMFWK1YlWMrAyArINoGwDA6CUUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Total Time" = _t]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Total Time", type time}}, "zh-CN"),
    #"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "hours", each Time.Hour([Total Time])+Time.Minute([Total Time])/60+Time.Second([Total Time])/3600),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Total hours", each List.Sum(#"Added Custom"[hours])),
    #"Extracted Text Range" = Table.TransformColumns(#"Added Custom1", {{"Total hours", each Text.Middle(Text.From(_, "en-US"), 0, 5), type text}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Extracted Text Range",{{"Total hours", type number}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type", "Minutes", each Time.Hour([Total Time])*60+Time.Minute([Total Time])+Time.Second([Total Time])/60),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Total Minutes", each List.Sum(#"Added Custom2"[Minutes])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"Minutes", "hours"})
in
    #"Removed Columns"

    And you will see:

     

    Or you could create 4 columns using dax expressions to get the result.

    For details,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my reply as a solution!

  • FrankG's avatar
    FrankG
    Regular Visitor
    3 years ago

    Hi, 

    Maybe there are more elgant solutions, but I think the below will work:

     

    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each Time.ToRecord(Time.From([Time]/86400))),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"Hour", "Minute", "Second"}, {"Custom.Hour", "Custom.Minute", "Custom.Second"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "DateTime", each DateTime.From([Date] + #duration(0,8,[Custom.Minute],[Custom.Second])), type datetime)