Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
Solved! Go to Solution.
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!
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)
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!
Please see this article and videos.
Calculate and Format Durations in DAX – Hoosier BI
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.