Query with TimeDuration Power BI

[Syndicate_Admin]

Hi,

I have a query on a table with a timeduration field.

If i hit this query und PowerBI or on the Webservice, i'll get a value like:

P0DT11H38M32.216S

How do i get the actual time from this within powerbi ?

my query:

elements
    {
        dataitem(r; Resource)
        {
            column(No_; "No.")
            {

            }
            column(Name; Name)
            {

            }
            dataitem(TL; "Ticket Lines_SD")
            {
                dataitemlink = Ressource = r."No.";
                SqlJoinType = InnerJoin;
                column(TimeDuration; TimeDuration)
                {
                    Method = Sum;
                    
                }
                filter(BeginTime; BeginTime)
                {

                }

            }
        }
    }

[dax · Accepted solution]

Hi @Syndicate_Admin , 

I am not clear  about your requirement, if possible, could you please inform me your expected output. In addition, I test "P0DT11H38M32.216S" in power query, I find that it will show like below when change type

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjBwCTE09DC28DU20jMyNAtWio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"name", type duration}})
in
    #"Changed Type"

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.

[Syndicate_Admin]

This is an easy fix in Power BI's query editor.  Looking at the response it is a duration value that is 0 Days, 11 Hours 38 Minutes and 32.216 seconds long.  

There are multiple ways to handle this in Power BI but the easiest would be to split the field using the DT, H, M, then remove the P and the S.  

Then multiple the Day column by 24 and add to the Hour (no days in a Power BI Duration field).  

Then create a new column that concatenates the Hour, Minute and Second fields and change the field type to duration.

This doesn't require any special formatting or extension development.

[Syndicate_Admin]

// * Given a number of seconds, returns a format of "hh:mm:ss"

//

// We start with a duration in number of seconds

VAR Duration = [Duration in Seconds]

// There are 3,600 seconds in an hour

VAR Hours =

INT ( Duration / 3600)

// There are 60 seconds in a minute

VAR Minutes =

INT ( MOD( Duration - ( Hours * 3600 ),3600 ) / 60)

// Remaining seconds are the remainder of the seconds divided by 60 after subtracting out the hours

VAR Seconds =

ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0) // We round up here to get a whole number

// These intermediate variables ensure that we have leading zero's concatenated onto single digits

// Hours with leading zeros

VAR H =

IF ( LEN ( Hours ) = 1,

CONCATENATE ( "0", Hours ),

CONCATENATE ( "", Hours )

)

// Minutes with leading zeros

VAR M =

IF (

LEN ( Minutes ) = 1,

CONCATENATE ( "0", Minutes ),

CONCATENATE ( "", Minutes )

)

// Seconds with leading zeros

VAR S =

IF (

LEN ( Seconds ) = 1,

CONCATENATE ( "0", Seconds ),

CONCATENATE ( "", Seconds )

)

// Now return hours, minutes and seconds with leading zeros in the proper format "hh:mm:ss"

RETURN

CONCATENATE (

H,

CONCATENATE ( ":", CONCATENATE ( M, CONCATENATE ( ":", S ) ) )

)

Terrance

CRM Admin | Apps4Rent

[Syndicate_Admin]

Hello,

If this is still an issue, you may want to explore alternatives. We currently do not have dedicated Dev support via the Dynamics 365 Business Central forums, but I wanted to provide you some additional resources to assist.  If you need assistance with debugging or coding I would recommend discussing this on one of our communities.

www.yammer.com/dynamicsnavdev

dynamicsuser.net/.../developers

I will open this up to the community in case they have something to add.

Thanks.

[Syndicate_Admin]

Hi,

Thanks for your input, where would i convert the duration ?

There is no option within the cloumn field ?

[Syndicate_Admin]

In a Query object you can't use any AL-coding, so in this case you can't convert the datatypes. There are however some workarounds.

The best option, in my opinion, would be to convert the datatype in Power BI, in the Query editor. 'm sure you can find a way to do that there.

Another solution would be to add a field to the underlying table, using a table extension. Then find a way to populate that field and apply the desired conversion. Then you can add this new field in the query.

[Syndicate_Admin]

best you convert the duration in al code. 

you can use text.substring.