Forum Discussion

JRParker's avatar
JRParker
Helper III
3 years ago
Solved

DataFormat.Error: We couldn't parse the input provided as a Date value. Details:

In PQ, initiated a New Source from a folder with a host of Excel files with the MMDDYYYY embeded in their file names.   After combining and transforming, the Source.Name field includes the MMDDYYYY...
Need Help
  • danextian's avatar
    danextian
    3 years ago

    Just tested and that mmddyyyy text string couldn't be parse as a date even if I added a culture. Try this custom column

    let 
dt = Text.BetweenDelimiters([Column1], " ", ".x"),
yr = Number.From(Text.End(dt, 4)),
mo = Number.From(Text.Start(dt, 2)),
dy = Number.From(Text.Range(dt,2,2))
in #date(yr, mo, dy)

     

  • danextian's avatar
    danextian
    3 years ago

    That's just foolproofing which files to get. I or someone else might erroneously save a non-relevant file in those folders.

danextian's avatar
danextian
Super User
3 years ago

It is possible that the date starts at 15 and not at 16 or any other number.

JRParker's avatar
JRParker
Helper III
3 years ago

Thanks Danextian, have confirmed the date starts at 16, and the result of the custom field is for example '01312023', extracted from the value 'GL_TrialBalance 01312023.xls' from Source.Name column. 

 

Perhaps it would be best to create a Date custom field in one fell swoop; extract and format at the same time, rather than creating the custom field of characters and then trying to format?