Number Error help please

[Will_1]

I am hoping someone has an idea, because I am stumped. 

I am receiving the typical: [DataFormat.Error] We couldn't convert to Number.

However, the Number it is trying to convert is this: 10/24/2069 

All of the query data types have been moved to text and the issue persists. 

Moreover,  I cannot find that "date" 10/24/2069 in the excel and CSV source files. And the source files load fine. The error is occuring in a merged query, but it's not anything I created in adding an additional column. 

Thanks in advance for any insights you can provide. 

[amitchandak]

@Will_1 , Seem like one column has shifted position for a row. Change the data type to text and filter and check the value , which rows the value. and then resolve it. Do it in data transformation/edit query mode

[Will_1]

@amitchandak

I tried your suggestion. On two different columns they won't load the filter because they error out with "We couldn't convert to number." 

do you know a way to search for that value in an entire table in edit query

[amitchandak]

@Will_1 , check for data type value.type in power query. Create a new column and check and filter the one with different data type

https://docs.microsoft.com/en-us/powerquery-m/value-type

[Will_1]

@amitchandak 

I am not sure I follow: do you mean create a new column for each current column, but change the data type to any in the newly created column? 

[amitchandak]

@Will_1 , in edit query mode create a new column that will show to data type of each row, see that can help to find the datatype issue

[Will_1]

@amitchandak 

I have done this. All columns show data type Any. I don't see any difference in rows. Even with all columns as data type "Any" the query won't load, receiving the same error "cannot conver to number."