Convert Decimal to Time format

DAX DAX Calculations
1

I need to calculate the time spent with a client and display it in h:mm format. The data has a date column and decimal numbers for the hour and minutes. It also has columns that show total hours and minutes. I made a calculated column of total seconds, but when I change the data type to time, I get an error. I’ve tried merging the three columns to get a date/time stamp, but that doesn’t work either.

image

image

image

I changed the data type to duration and it displays everything as days.

image

2

Hi @RDW,

Welcome to the forum.
Give this a go. Just copy the full script into a new blank query.

let
    Source = Table.TransformColumnTypes( Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ11zUyMLRU0lEyNAYSxgYgFpqgiSmQAEuYKsXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [BEG_DATE = _t, START_HR = _t, START_MIN = _t, END_DATE = _t, END_HR = _t, END_MIN = _t, HRS = _t, MINS = _t]),{{"BEG_DATE", type date}, {"END_DATE", type date}, {"START_HR", type number}, {"START_MIN", type number}, {"END_HR", type number}, {"END_MIN", type number}, {"HRS", type number}, {"MINS", type number}}),
    AddDuration = Table.AddColumn(Source, "Duration", each Time.From( Text.From([END_HR]) & ":" & Text.From([END_MIN])) - Time.From( Text.From([START_HR]) & ":" & Text.From([START_MIN])), type duration)
in
    AddDuration

I hope this is helpful

3

using only UI

immagine
immagine1116×732 21.3 KB

let
Source = Table.TransformColumnTypes( Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ11zUyMLRU0lEyNAYSxgYgFpqgiSmQAEuYKsXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [BEG_DATE = _t, START_HR = _t, START_MIN = _t, END_DATE = _t, END_HR = _t, END_MIN = _t, HRS = _t, MINS = _t]),{{"BEG_DATE", type date}, {"END_DATE", type date}, {"START_HR", type number}, {"START_MIN", type number}, {"END_HR", type number}, {"END_MIN", type number}, {"HRS", type number}, {"MINS", type number}}),
#"Rimosse colonne" = Table.RemoveColumns(Source,{"HRS", "MINS"}),
#"Merge di colonne" = Table.CombineColumns(Table.TransformColumnTypes(#"Rimosse colonne", {{"START_HR", type text}, {"START_MIN", type text}}, "it-IT"),{"START_HR", "START_MIN"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"ST"),
#"Merge di colonne1" = Table.CombineColumns(Table.TransformColumnTypes(#"Merge di colonne", {{"END_HR", type text}, {"END_MIN", type text}}, "it-IT"),{"END_HR", "END_MIN"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"ET"),
#"Merge di colonne2" = Table.CombineColumns(Table.TransformColumnTypes(#"Merge di colonne1", {{"BEG_DATE", type text}}, "it-IT"),{"BEG_DATE", "ST"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"start time"),
#"Merge di colonne3" = Table.CombineColumns(Table.TransformColumnTypes(#"Merge di colonne2", {{"END_DATE", type text}}, "it-IT"),{"END_DATE", "ET"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"end time"),
#"Modificato tipo" = Table.TransformColumnTypes(#"Merge di colonne3",{{"start time", type datetime}, {"end time", type datetime}}),
#"Aggiunta colonna personalizzata" = Table.AddColumn(#"Modificato tipo", "dur", each [end time]-[start time])

in
#“Aggiunta colonna personalizzata”

4

Hi Melissa,

The script works beautifully as long as the sum of the duration column is less than 24, once it exceeds that, it starts back at 0. I would also like to use measures with the duration column to calculate averages. For example : a duration of 32:10 over two days should return an average of 16:05.

5

Hi @RDW,

Sorry about that, forgot to include the date, try this.

let
    Source = Table.TransformColumnTypes( Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ11zUyMLRU0lEyNAYSxgYgFpqgiSmQAEuYKsXq4NBkhE9TLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [BEG_DATE = _t, START_HR = _t, START_MIN = _t, END_DATE = _t, END_HR = _t, END_MIN = _t, HRS = _t, MINS = _t]),{{"BEG_DATE", type date}, {"END_DATE", type date}, {"START_HR", type number}, {"START_MIN", type number}, {"END_HR", type number}, {"END_MIN", type number}, {"HRS", type number}, {"MINS", type number}}),
    AddDuration = Table.AddColumn(Source, "Duration", each ([END_DATE] & Time.From( Text.From([END_HR]) & ":" & Text.From([END_MIN]))) - ([BEG_DATE] & Time.From( Text.From([START_HR]) & ":" & Text.From([START_MIN]))), type duration),
    TotalMinutes = Table.AddColumn(AddDuration, "Total Minutes", each Duration.TotalMinutes([Duration]), type number)
in
    TotalMinutes

I hope this is helpful

6

Hi @RDW, did the response provided by the users and experts help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

I hope that you are having a great experience using the Support Forum so far. We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

7

Hi @RDW, we’ve noticed that no response has been received from you since the 30th of April. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

8

Hi @RDW, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!