How do I fix a Power Bi Input Data Millisecond Error?

Question

I'm importing a csv data file into power bi power query. One of the columns has dates. 90% of the records are formatted as mm/dd/yyyy hh:mm:ss pm/am (12/7/2022 12:30:01 PM). The other 10% of the records are formatted as mm/dd/yyyy hh:mm:ss:mmm (12/7/2022 12:30:01:443). Therefore, the records with the milliseconds show as an error.

I tried to split the records, I tried to change the Locale and i tried to change the data type. The millisecond records are still showing as errors. I want the millisecond records to show the entire value so that I can split all records and then receive just the date.

horseyride · Accepted Answer · 2024-01-17 17:50:39Z

0

Add column .. custom column ... with

= try DateTime.FromText([Column1], [Format="MM/dd/yyyy hh:mm:ss tt"]) otherwise DateTime.FromText([Column1], [Format="MM/dd/yyyy HH:mm:ss:fff"])

use the real name of your column in place of [Column1]

replace the last ) at the end of the formula with ,type datetime)

sample:

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "Custom", each try DateTime.FromText([Column1], [Format="MM/dd/yyyy hh:mm:ss tt"]) otherwise DateTime.FromText([Column1], [Format="MM/dd/yyyy HH:mm:ss:fff"]), type datetime)
in #"Added Custom"

answered Jan 17, 2024 at 17:50

horseyride

21.8k3 gold badges15 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How do I fix a Power Bi Input Data Millisecond Error?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related