Forum Discussion

slcekala77's avatar
slcekala77
New Member
2 years ago

Show milliseconds from data source with Power Query

Hi, I'm using PowerQuery to import data that has a time column with the data in this format (as shown in the editor when I select a cell in that column): 19:56:40.0560000 (7:56:40 pm with 56 millis...
Need Help
slcekala77's avatar
slcekala77
New Member
2 years ago

Thanks for the reply. I do understand that the values will be preserved in the data.  I'm trying to get all the way through PQ before dabbling in PowerBI, so I'll keep in mind that this is possible in PowerBI and look at that sooner than I otherwise might have.  I'll also take a closer look at the duration type.

 

I'm transitioning this data from a in-worksheet dataset where one of the things I'm computing is the difference in milliseconds between rows to identify those records where the difference is over (for example) 2 milliseconds.  But I'm having trouble understanding what to do where and I may be too focused on what it looks like in PQ for the moment 🙂

ronrsnfld's avatar
ronrsnfld
Super User
2 years ago

Here's an example showing the difference in milliseconds between two values, and also demonstrating that even with the time type, the result is proper. You could also use the duration type, but both columns should be the same data type.

 

let
   Source = #table(type table[Time1=text, Time2=text],{{"19:56:40.0560000", "19:56:40.0920000"}}),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Time1", type time}, {"Time2", type duration}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Milliseconds Difference", 
            each Duration.Seconds([Time2]-[Time1])*1000, type number)
in
    #"Added Custom"

 

If you want to leave the data as text type, then you could use the following to get the difference:

 

let
   Source = #table(type table[Time1=text, Time2=text],{{"19:56:40.0560000", "19:56:40.0920000"}}),
    #"Added Custom" = Table.AddColumn(Source, "Milliseconds difference", 
        each Duration.Seconds(Time.From([Time2])- Time.From([Time1])) *1000, type number)
in
    #"Added Custom"