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
  • 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?