Forum Discussion
DataFormat.Error: We couldn't parse the input provided as a Date value. Details:
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.
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.
Interesting.... I've never thought of importing files from a folder with various files, file types; I always set up a folder/subfolder exclusively for the imported files.
That's just foolproofing which files to get. I or someone else might erroneously save a non-relevant file in those folders.