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.

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"

  • JRParker's avatar
    JRParker
    Helper III
    3 years ago

    thank you! ... finally took the time to figure the M code in the context of my existing query....here is the tail end of the query:

     

    #"Added Custom" = Table.AddColumn(#"Expanded Table Column1", "dt", each Text.BetweenDelimiters([Source.Name], " ", ".x")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "yr", each Number.From(Text.End([dt],4))),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "mo", each Number.From(Text.Start([dt], 2))),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "dy", each Number.From(Text.Range([dt],2,2))),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Date", each #date([yr],[mo],[dy])),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom4",{{"Date", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"dt", "yr", "mo", "dy"})
    in
    #"Removed Columns"

     

    ....data exported from table visual:

     

    Date
    3/21/2022
    3/31/2022
    4/30/2022
    5/31/2022
    6/30/2022
    7/31/2022
    8/31/2022
    9/30/2022
    10/31/2022
    11/30/2022
    12/31/2022
    1/31/2023
    2/28/2023
    3/31/2023
    4/30/2023
    5/31/2023
    6/30/2023
    7/31/2023