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.

Answer 1

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"