NEED HELP - Date/Time formatted items in excel are getting rounded to the nearest hour.

[kvniranjan]

Hi,

I need help with date/time format in Power BI. I have data in excel with time stamp as below. I had formatted based on the requirement as well. However when it is imported onto Power BI the formatted time stamp is getting rounded to the nearest hour. Please help.

Below is the screenshot of the same in Power BI.

Please help

Thanks

Niranjan

[v-stephen-msft]

Hi @kvniranjan ,

Agree with @MahyarTF , I put the attachment for your reference.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDX9fMP0zUyUjAw0TMw1jMx1TMAAwVHX6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ST_LD_ST_TSTAMP = _t]),
    #"Duplicated Column" = Table.DuplicateColumn(Source, "ST_LD_ST_TSTAMP", "ST_LD_ST_TSTAMP - Copy"),
    #"Replaced Value" = Table.ReplaceValue(#"Duplicated Column",".000000","",Replacer.ReplaceText,{"ST_LD_ST_TSTAMP - Copy"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",".",":",Replacer.ReplaceText,{"ST_LD_ST_TSTAMP - Copy"}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Replaced Value1", {{"ST_LD_ST_TSTAMP - Copy", type datetime}}, "en-GB")
in
    #"Changed Type with Locale"

Best Regards,

Stephen Tao

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

[MahyarTF · Accepted solution]

Hi,

I check your sample data and when you put the right format for the time it works.

I put the ":" instead of "." for the time part in excel and loaded it to Power query, now it is shown as below :

Thanks for your Kudos and please mark it as a solution if it helps you.