Forum Discussion
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 within the respective Excel file names.
After adding a custom colum to extract the MMDDYYYY (via Text.Middle([Source.Name], 16, 8)), one is left with Custom column Type Any. The error (in the subject line of this correspondence) results when simply trying to transform to Date. First transforming to either Text or Whole number doesn't solve the issue; neither does Trim and/or Clean. Any ideas?
Looked at prior posts related to this subject but they're all slightly different context.
Chat GPT couldn't figure it out. 🙂
Thanks in advance.
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)That's just foolproofing which files to get. I or someone else might erroneously save a non-relevant file in those folders.
10 Replies
When getting data from a folder of Excel workbooks, regardless of the underlying structure of the workbooks within the folder, the very first couple of PQ steps are columns of meta data including a source column of the workbook names. The file name naming convention of the underlying workbooks withing the folder is critical in that a MMDDYYYY is embedded into each file name. For example, "FILE05312023", "FILE06302023", etc. The intent of this thread was to find a way to extract the MMDDYYYY out of each file name and include this Date in each row of the respective workbooks in the folder.
Along with the meta data columns is the Table column which is each workbook to be expanded to include all the underlying fields . Below is a sample where all the meta data columns have been removed other than the Source and table columns:
Source Transform File FILE01312023 Table FILE02282023 Table FILE03312023 Table FILE04302023 Table FILE05312023 Table FILE06302023 Table
The query would look like this after expanding the table:Source Field 1 Field 2 Field 3 FILE01312023 value 1 value 1 value 1 FILE01312023 value 2 value 2 value 2 FILE01312023 value 3 value 3 value 3 FILE02282023 value 4 value 4 value 4 FILE02282023 value 5 value 5 value 5 FILE02282023 value 6 value 6 value 6 FILE03312023 value 7 value 7 value 7 FILE03312023 value 8 value 8 value 8 FILE03312023 value 9 value 9 value 9 FILE03312023 value 10 value 10 value 10 FILE04302023 value 11 value 11 value 11 FILE05312023 value 12 value 12 value 12 FILE05312023 value 13 value 13 value 13 FILE05312023 value 14 value 14 value 14 FILE06302023 value 15 value 15 value 15 FILE06302023 value 16 value 16 value 16 The intent is to add a custom column by extracting the date from the Source column:
Source Field 1 Field 2 Field 3 Date FILE01312023 value 1 value 1 value 1 1/31/2023 FILE01312023 value 2 value 2 value 2 1/31/2023 FILE01312023 value 3 value 3 value 3 1/31/2023 FILE02282023 value 4 value 4 value 4 2/28/2023 FILE02282023 value 5 value 5 value 5 2/28/2023 FILE02282023 value 6 value 6 value 6 2/28/2023 FILE03312023 value 7 value 7 value 7 3/31/2023 FILE03312023 value 8 value 8 value 8 3/31/2023 FILE03312023 value 9 value 9 value 9 3/31/2023 FILE03312023 value 10 value 10 value 10 3/31/2023 FILE04302023 value 11 value 11 value 11 4/30/2023 FILE05312023 value 12 value 12 value 12 5/31/2023 FILE05312023 value 13 value 13 value 13 5/31/2023 FILE05312023 value 14 value 14 value 14 5/31/2023 FILE06302023 value 15 value 15 value 15 6/30/2023 FILE06302023 value 16 value 16 value 16 6/30/2023 ...then remove the Source column and proceed with addtional query steps.... hope this clarifies.
My style, when connecting to a folder, is to make sure that I am getting data from the right files so I apply filters to the file name (contains or starts with a particular string and doesn't contain $ as this is a temporary file) and the extension. I also keep the filename so I know which file my data is from. Very helpful when I expect some data for a column or more with blank rows or when I expect this x count of files to be loaded but some are missing.
It is possible that the date starts at 15 and not at 16 or any other number.
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)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"
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?
