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

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)

 

JRParker's avatar
JRParker
Helper III
3 years ago

I don't know enough about M language to embed what you have into my existing query.  This is what I have so far... can you help with embedding?

 

let
Source = Folder.Files("C:\FVE TB Folder"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (4)", each #"Transform File (4)"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (4)"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (4)", Table.ColumnNames(#"Transform File (4)"(#"Sample File (4)")))
in
#"Expanded Table Column1"