Data Format Error: We couldn't parse the input provided as a Date value

%3CLINGO-SUB%20id%3D%22lingo-sub-753731%22%20slang%3D%22en-US%22%3EData%20Format%20Error%3A%20We%20couldn't%20parse%20the%20input%20provided%20as%20a%20Date%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-753731%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I%20have%206%20different%20reports%20that%20I%20receive%20daily.%20I%20am%20trying%20to%20merge%20them%20using%20power%20query%20into%20one%20table%2C%20so%20that%20all%206%20of%20the%20reports%20(only%201%20row%20of%20data%20each)%20are%20combined%20into%20one%20row%20on%20the%20merged%20table.%20I%20keep%20getting%20the%20error%20message%3A%26nbsp%3BData%20%22Format%20Error%3A%20We%20couldn't%20parse%20the%20input%20provided%20as%20a%20Date%20value%22.%20The%20Query%20connections%20seem%20to%20be%20fine.%20Then%20I%20add%20new%20files%20to%20the%20folder%20I%20am%20merging%20from%20and%20then%20I%20get%20that%20message%20when%20I%20go%20to%20refresh%20the%20query.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help!!!%20What%20do%20I%20need%20to%20do%20to%20fix%20this%20error%3F%20What%20am%20I%20doing%20wrong%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-753731%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-753874%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20Format%20Error%3A%20We%20couldn't%20parse%20the%20input%20provided%20as%20a%20Date%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-753874%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F375958%22%20target%3D%22_blank%22%3E%40purpleorchid%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EMost%20probably%20that's%20literally%20what%20the%20message%20says%20-%20in%20one%20of%20your%20files%20there%20is%20the%20value%20which%20is%20expected%20as%20Date%2C%20but%20can't%20be%20converted%20into%20the%20date.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-754121%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20Format%20Error%3A%20We%20couldn't%20parse%20the%20input%20provided%20as%20a%20Date%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-754121%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BI%20double%20and%20triple%20checked%20all%20data%20before%20I%20did%20the%20query%20and%20the%20query%20refresh.%20All%20date%20columns%20were%20already%20set%20up%20as%20dates%20on%20the%20original%20worksheets%2C%20before%20I%20even%20created%20the%20query%20and%20when%20creating%20the%20query%20I%20made%20sure%20to%20set%20that%20column%20as%20a%20date%20as%20well%2C%20so%20there%20was%20nothing%20that%20needed%20to%20be%20'converted'%20to%20a%20date%2C%20as%20they%20already%20were%20dates.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-754898%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20Format%20Error%3A%20We%20couldn't%20parse%20the%20input%20provided%20as%20a%20Date%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-754898%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F375958%22%20target%3D%22_blank%22%3E%40purpleorchid%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECould%20you%20provide%20the%20sample%20keeping%20only%20column(s)%20with%20dates%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1268460%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20Format%20Error%3A%20We%20couldn't%20parse%20the%20input%20provided%20as%20a%20Date%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1268460%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F375958%22%20target%3D%22_blank%22%3E%40purpleorchid%3C%2FA%3E%26nbsp%3BDid%20you%20solve%20this%20problem%3F%20I%20have%20the%20same%20issue%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1269151%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20Format%20Error%3A%20We%20couldn't%20parse%20the%20input%20provided%20as%20a%20Date%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1269151%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F375958%22%20target%3D%22_blank%22%3E%40purpleorchid%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20double%20and%20triple%20checked%20all%20data%20before%20I%20did%20the%20query%20and%20the%20query%20refresh.%20All%20date%20columns%20were%20already%20set%20up%20as%20dates%20on%20the%20original%20worksheets%2C%20......%20there%20was%20nothing%20that%20needed%20to%20be%20'converted'%20to%20a%20date%2C%20as%20they%20already%20were%20dates.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20often%20been%20fooled%20by%20dates%20(in%20downloaded%20XLS%20sheets%20from%20one%20or%20more%20of%20the%20financial%20institutions%20I%20deal%20with)...dates%20that%20look%20like%20dates%20but%20are%20in%20fact%20text%20masquerading%20as%20dates.%20You%20may%20have%20already%20checked%20for%20that%20condition%20among%20your%20double%20and%20triple%20checking%2C%20but%20I%20thought%20I'd%20mention%20that%20for%20the%20sake%20of%20others%20as%20well....%3C%2FP%3E%3CP%3EWhen%20that's%20the%20case%2C%20it%20generally%20takes%20text%20manipulation%20to%20change%20each%20section%20of%20the%20date%20(day%2C%20moth%2C%20year)%20into%20its%20numeric%20value%20and%20then%20use%20the%20DATE%20function%20to%20produce%20the%20actual%20Excel%20date.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mathetes_0-1585684563493.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F181013i3803BA7EE1D251E4%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22mathetes_0-1585684563493.png%22%20alt%3D%22mathetes_0-1585684563493.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi, I have 6 different reports that I receive daily. I am trying to merge them using power query into one table, so that all 6 of the reports (only 1 row of data each) are combined into one row on the merged table. I keep getting the error message: Data "Format Error: We couldn't parse the input provided as a Date value". The Query connections seem to be fine. Then I add new files to the folder I am merging from and then I get that message when I go to refresh the query.

 

Please help!!! What do I need to do to fix this error? What am I doing wrong?

9 Replies

@purpleorchid 

 

Most probably that's literally what the message says - in one of your files there is the value which is expected as Date, but can't be converted into the date.

@Sergei Baklan I double and triple checked all data before I did the query and the query refresh. All date columns were already set up as dates on the original worksheets, before I even created the query and when creating the query I made sure to set that column as a date as well, so there was nothing that needed to be 'converted' to a date, as they already were dates.

@purpleorchid 

 

Could you provide the sample keeping only column(s) with dates?

@purpleorchid Did you solve this problem? I have the same issue

@purpleorchid 

I double and triple checked all data before I did the query and the query refresh. All date columns were already set up as dates on the original worksheets, ...... there was nothing that needed to be 'converted' to a date, as they already were dates.

 

I've often been fooled by dates (in downloaded XLS sheets from one or more of the financial institutions I deal with)...dates that look like dates but are in fact text masquerading as dates. You may have already checked for that condition among your double and triple checking, but I thought I'd mention that for the sake of others as well....

When that's the case, it generally takes text manipulation to change each section of the date (day, moth, year) into its numeric value and then use the DATE function to produce the actual Excel date.

 

mathetes_0-1585684563493.png

 

@mathetes Hi.. I am having the same issue. The data is correct, there are a few fields per file so is not possible that I have miss something in the previous check but I still getting the same error when I try to merge two tables.. 

@davidvar 

Perhaps you may share sample file?

@purpleorchid @davidvar

 

I had the same problem when I used the "Group By" feature in Power Query. I then paid attention to the error which mentions below that "Data Format Error: We couldn't parse the input provided as a Date value" sentence which states "Error: "-"", that  made me realize certain cells had dashes ("-") instead of zeros or nulls. Once I replaced those dashes to zeros or nulls, it worked fine. 

 

Hope that helps you and anyone in this thread facing this problem! 

@Mohammed_Al-Shaikhly 

En power query tenía el siguiente caso:

Me conecté a una carpeta que tenia varios archivos.

cada archivo tiene sus propios encabezados

Al promover primera fila como encabezados, quedaron varios de los titulos en el proceso

Lo que hice fue ir a la columna de fecha, y eliminar filas con errores.

Asunto solucionado.

 

In power query I had the following case:
I connected to a folder that had several files.
each file has its own headers
By promoting first row as headlines, several of the titles were left in the process
What I did was go to the date column, and remove rows with errors.
Issue resolved!!