"We couldn't parse the input provided as a Date value.." Can anyone help this issue I'm facing?

[Anonymous]

Please can someone help with the issue I'm facing, detailed below:

I have taken the original data structure of:

------         01/01/2017          Feb 2017          22/03/17
Team              Min                                              Max

and cleaned it a little to now show:

------         01/01/2017        01/02/2017        01/03/2017
Team              Min                      Zero                  Max

I then pivot the date columns (however they are actually a 'text' datatype) so the data structure is now:
Team         01/01/2017          Min

Team         01/02/2017          Zero

Team         01/03/2017          Max

All good so far.

When I select 'Apply', from within Power Query I get the following message Just after it loading to the data model:

"OLE DB or ODBC error:[DataFormat.Error] We couldn't parse the input provided as a Date value.."

This error message only appears after the pivoting step and not before. All columns at the 'text' datatype.

Please can someone tell me what the problem is?

Thanks.

[v-jiascu-msft]

@Anonymous

Hi,

It seems that the “Min” is in your date column. You can reference the picture below. Does your model connect to the database? When “Apply”, PBI will fetch data from database where may be contain incorrect data.

Best Regards!

Dale

[Anonymous]

Thanks Dale.

The error you have shown is the exact same error message I receive.

The puzzling thing is, the data is being pulled from a .xlsx file - in the following format:

Team               May 16     June 16     Jul 16     Aug 16     Sep 16

Operations     Min                            Max       Min       

Production     Max           Min                                         Min

I then manuipulate the above to be:

Team              Attribute           Value

Operations     01/05/2016      Min

Operations     01/06/2016      Zero

Operations     01/07/2016      Max

Operations     01/08/2016      Min

Operations     01/09/2016      Zero

Production     01/05/2016      Max

Production     01/06/2016      Min
Production     01/07/2016      Zero

Production     01/08/2016      Zero

Production     01/09/2016      Min

I first manually amended the date names (i.e. 'May 16' to be '01/05/2016' for all the date names).

I then replace all null values with 'Zero'.

I then pivot the Attribute date names so to list vertically (thus becoming the Attribute column, with the automatic addition of the Value column too).

I then click 'Apply' and that's when I get the error. I get the error even when I try the following:
Attribute is a text column, Value is a text column.

Attribute is a date column, Value is a text column.

Removing error rows on both the Attribute and Value columns.

Replacing errors with '31/12/9999' for the Attribute column, and 'ZZZZ' for the Value column.

None of the above resolve it.

The puzzling thing is, the error 'Min' value can only be present after my pivoting.

I'm lost. Are there any tricks I can do to pinpoint the error?  and then to remove/delete it?

Thanks.

[Anonymous · Accepted solution]

The issue has been resolved.

The solution: a simple delete of the table and then reconnect to the source file using a fresh 'Get Data' option.

Doing the pivoting now works without issue.

Strange how I was receiving the error in the first place only for a fresh connection to resolve it. The content of the datasource was unchanged.

Thanks to all who contributed.

[v-jiascu-msft]

@Anonymous

Hi,

My pleasure. Could you please post some snapshot? Snapshot 1: the data in excel file. Snapshot 2: the status of after unpivoting. If you can post the PBIX, that would be great. You can find the error as picture showed.

Best Regards!

Dale

[vanessafvg]

@Anonymous  sounds like you have invalid dates?

in power query it only profiles the first 200 rows when you apply and load you are applying to all your data, you might have to do some profiling of your data

[Anonymous]

Thanks for your time Vanessa.

For the columns with dates for the heading (i.e. 01/01/2017) I did the following steps:
1) Manually enter the columns to be dd/mm/yyyy format. I did this for all date columns (01/01/2017 through to 01/12/2020). So I know the column headings are correct.

2) I then set columns from 01/01/2017 through to 01/12/2020 as 'text' datatype.

When pivoting them I have confidence the Attribute column (i.e. containing the dates (i.e. 01/01/2017)) have correct dates and the Value column is set to 'text' too. So the two columns that have been affected by the Pivoting are correct.

So if I have manually cleaned the data and can see the column's values are correct then what is happening to warrant this error message?

[vanessafvg]

@Anonymous its really hard to know off the top of my head without seeing the data

is there any chance you can share your pbix?

[Anonymous]

Not really due to the sensitve data.

I did try and replicate the error through creating a a spreadsheet of the data matching that like the original, and reproducing the same steps in a new .PBIX file (i.e. setting the columns to 'text', manually renaming the columns to 01/01/2017 etc.., and then pivoting them. All worked fine. So its not the steps but the data.

In the error version of the .PBIX file I removed the 'Applied Steps' and then reapplied them but in a different order. When attempting to 'Apply' the changes within Power BI I get the same error. The only difference I can see as my 208th, and last, row that each column entry for that row has Error in yellow appearing there.

When clicking on the Error in each of the columns I get the error "DataFormat.Error: We couldn't parse the input provided as a Date value. Details: Min".

So clearly there is an error present. Something to do with 'Min'.

I really do not know what's going on?

[vanessafvg]

@Anonymous

can you put a screen shot, what i interpret that as is having a text value of 'min' in your date column which will spit out an error, do a replace on it, with a dummy date or with nothing and see what happens

[Anonymous]

This is a strange one.
The source data is not tidy. What I am attempting to do now is get the end-user beyond their sticking point but at some point they will need to clean their data which may break what I'm producing for them. I will go back to the end-user and request they clean up their data. Once they do this, if the error is gone then the lesson learned is cleaner data, if it remains then I can removed alot of the applied steps, used to clean up their data, and play with the vanila source to seek a resolution to this error.

Thanks for your advice on this.

[vanessafvg]

@Anonymous Is always best to push cleaning back to the source as much as possible but as you know power query is a great data cleaner too!  Good luck