DataFormat.Error: We couldn't parse the input provided as a Date value. Details:

[JRParker]

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. 

[danextian · Accepted solution]

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]

It is possible that the date starts at 15 and not at 16 or any other number.

[danextian · Accepted solution]

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]

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]

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]

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?

[Ritaf1983]

Hi @JRParker 

To make it easier to understand and work with, please share your table.

[JRParker]

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.

[danextian]

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.

[JRParker]

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. 

[danextian · Accepted solution]

That's just foolproofing which files to get. I or someone else might erroneously save a non-relevant file in those folders.