Power BI dataflow and milliseconds

[alexbjorlig]

I have a Power BI dataflow, that fetches data from an OData source.

The column in question is of type Date, and clearly includes milliseconds:

However, when I connect to the data source from Power BI Desktop, the milliseconds are evaporated. This is very problematic for some detailed time-analysis within the second that I'm doing in Power BI:

Does Power BI not support milliseconds on date-times, or what is going on here?

[v-henryk-mstf]

Hi @alexbjorlig ,

Displaying milliseconds on powerbidesktop requires some conversion operations. It is recommended to convert the time to exact millions of seconds and then connect the hours, minutes, seconds and millions of seconds in TEXT format.

Also, if the actual time is used in the metric/calculation column, the actual time is still correct, so you can click on the Table View and check the original data.

You can refer to the following blog content with detailed steps.

Milliseconds Duration - Microsoft Power BI Community

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[alexbjorlig]

Wait? I'm describing a really simple issue. What exactly do you mean when writing "Displaying milliseconds on powerbidesktop requires some conversion operations".

Can Power BI desktop not show milliseconds by default?

[lbendlin · Accepted solution]

That is correct. 

[lbendlin]

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to work with. Please show the expected outcome.

[spencerhoyle]

Hi - I am running into a similar issue. I am imported data from SQL Database into Power BI. One of the column fields is a datetime type in SQL and shows milliseconds. However, when it is imported into Power BI, the milliseconds go away as Power BI does not support milliseconds in datetime. Is there any solution to extracting those milliseconds in Power BI?

[lbendlin]

Carry the values over in epoch format (or another suitable numerical format). If this is important to you please consider raising it at https://ideas.powerbi.com

[spencerhoyle]

When you say "carry the values over", do you mean add new column in SQL that is in milliseconds, or is there a way to get datetime in Power BI to numeric format with milliseconds?

[spencerhoyle]

Epoch Time Format seems to be in seconds, not in milliseconds

[lbendlin]

As a general concept, yes.  But you can add fractional parts to the epoch numbers.

Let me see if I can setup a similar scenario on my SQL Server

[spencerhoyle]

Did you find any workaround in Power BI side? 

[lbendlin]

let
    Source = #table({"datetest"},{{"2022-05-25 15:30:23.1234"}}),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"datetest", type datetimezone}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [datetest]- DateTimeZone.From(Date.From([datetest])))
in
    #"Added Custom"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".