Forum Discussion

cdizzle22's avatar
cdizzle22
Frequent Visitor
4 years ago
Solved

Formatting Time and Null Time

Hello Community,   I am using Power BI to measure the time a ticket was opened using some cold data. The time is built off of when it was put into a resolved state minus when it was opened. The cal...
Tips and Tricks
  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    Hi cdizzle22 

     

    You can replace null with the current datetime with the following code. Replace "previous step name" with your last step name in Power Query Editor. 

    = Table.ReplaceValue(#"previous step name",null,DateTime.LocalNow(),Replacer.ReplaceValue,{"Resolved Time"})

     

    Then create a custom column with [Resolved Time] - [Open Time] to get the duration. 

     

    Add another custom column to format the Duration values. The new Duration column will be Text type. 

    = Table.AddColumn(#"Added Custom", "Duration new", each Text.From(Duration.Days([Duration])) & "Days," & Text.From(Duration.Hours([Duration])) & "Hours," & Text.From(Duration.Minutes([Duration])) & "Minutes")

     

    If you want to convert format with DAX method at report side, you can refer to the following blogs:

    Converting Decimal Time to Days, Hours, Minutes, Seconds in Power BI • My Online Training Hub

    Calculate Duration in Days Hours Minutes and Seconds Dynamically in Power BI using DAX - RADACAD

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

artemus's avatar
artemus
Microsoft Employee
4 years ago

If you are using the duration data type to do the calculations, you need to add a new column with the value converted to text first. Then once loaded into the model, you need to set the sort column of the text column with the duration to that of the decimal column.

 

If you are only working in decimals in Power Query, you can use a custom column with:

[Ticket_Open_Time] * #duration(1, 0, 0, 0) 

assuming that [Ticket_Open_Time] is the column name that contains the decimal value.