DataFormat.Error: We couldn't convert to Number.

[bobbiclancyABB]

Hello Everyone, I'm trying to pivot on a table that I'm editing in the PowerQuery but I keep getting a DataFormat Error and the pivot isn't working. 

[Protype83]

Hey Everyone. 

I had this kind of issue many times and now I figured it out. 

In query it didn't give any specific row/column error. 

There was nothing making any sense. 

So I  drive vba macro to get report from SAP(old version) and save it to txt file.

First I look if problem seems on SAP but now. then I check txt file and boom. Last rows were SAP summarize information and every row and column had some different character. Deleted those rows in txt file and query worked just fine. 

Hope someone else find solution here.

[Charlieburden8]

I am also having the name issue. My cells contain both numbers and letters and whenever I try to 'change type' nothing seems to remove the error. 

[LienekeP]

I too have this problem. but nothing in these comments seems to help. Did you find a solution jet?

[niting]

Hello - I am also facing a similar issue. I m trying to pivot an appended table. please refer to the below screenshots. I want to pivot cost category and the values are in Value column. I get an error saying "Couldnt convert to numbers .....Details: Connectivity". Value column is in fixed decimal number format.

[v-yingjl · Accepted solution]

Hi @bobbiclancyABB ,

Based on the error message, the "%" cannot be converted to NUMBER type. What is the data type of the column which includes '%'?

If the column is a number type but with '%' or other chararcters in it, it should be a text column.

Please check the column formats next to the column. On the applied steps, locate "Changed Type", change the format of the column to text, and click replace current step when asked. 

Best Regards,

Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[camargos88]

@bobbiclancyABB ,

You can replace the values before/after the unpivot or replace the error with some value or nothing...

Or even change the date format before the operation.