Forum Discussion
Query with TimeDuration Power BI
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:
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)
{
}
}
}
}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 ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
best you convert the duration in al code.
you can use text.substring.
Hi,
Thanks for your input, where would i convert the duration ?
There is no option within the cloumn field ?
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.
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.
dynamicsuser.net/.../developers
I will open this up to the community in case they have something to add.
Thanks.
// * 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
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.
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 ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
