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.
