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

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