Excel power query [DataFormat.Error] We couldn't parse the input provided as a Date value.

[ajm]

folks, importing multiple template files into one combined file using power query. getting [DataFormat.Error] We couldn't parse the input provided as a Date value. I have been through each of the import files and the date format in each is correctly set to locale (the first choice with the asterisk). I can import each file, one at a time without issue. the error arises when i am attempting to filter the date column for the most recent date only. I have tried changeing type with locale and still get the same error. what else can i detail to aid the solution?

 

[ajm]

Without having the above information and based on your screenshot, try
in "Transform Sample File"
(create a backup of your file first)

• Remove the Promote heading step
• add the step to Remove Rows > Remove Top Rows > enter 1 row (or as many rows as you need to put the real headings on row 1)
• now promote the real headings

In the Main Subs 20211101 query, see how it looks at the step "Expanded Table".
If it looks ok that point, I suspect you will need to delete the steps after that and recreate those.

Background Info only for others:
It looks to me that you went Get Data > From File > From Folder and then Combine & Transform Data

Morning Alex, thank you for your patience and help so far. I believe i have found the problem. Several sites (each file is a seperate site) had submitted dates in an incorrect format. one had left out the / (1/112021), another had used full stops (1.11.21), and a couple of others had non date entries. When importing individually, the different styles of date would not (and did not) cause any issues. It was when combining the files that the issue arose.

what helped was figuring out to which file the error was attributed. i had been opening all the files one by one and checking the fields for dates. Obviously, i missed a few lines here and there so the issue persisted. this morning, i stepped back through the steps in the query until i got the list of files in the folder, and took a screen shot. This would tell me the order of import for the files.

From above:

The error in the shot above is in the file after the one displayed in the first column. I opened a second instance of excel, went to the file, found the date formatted incorrectly, fixed it, and saved the file.

then went back to the power query, removed the filter, then reapplied it. the query ran until it found the next error. repeated the process above.

did this until it was all fixed.

the key for me was figuring out the next file in the import. was like a light bulb moment (i'm easily pleased these days!)

 

[Alex Blakenburg]

Let me know if you are all good now or if there is still something I can help you with.

Using an index column is often helpful in identifying the order and the row number of the issue.
If your date errors have a pattern then before applying the Changed Type, use PQ functions to convert the text to a proper date format eg replace "." with "/". This is preferably to manually going into multiple source files and fixing the errors.

 

[ajm]

all good now alex. thanks for stepping up to help.

 

[Alex Blakenburg]

Thanks for letting me know. Happy to help.