[Data.Format.Error]

Introduction

The title of this page represents an error message I got this morning as a result of an update I had made to a Power Query model I had created. This is what I did to solve the problem.

Receiving the Message

I was innocently working on my model, Unpivoted table of seven columns, 8,700 rows when I received the error message [Data.Format.Error]We couldn't convert to number. Goodness! What did that mean? What couldn't they convert and why?

My Search

I leapt into action and did a web search. At first, I found almost nothing of use until I realised I had a typo in my question! Having corrected that, I found a few apparently useful pages on this error. They offered these suggestions:

• convert your numbers to text
• change the M code
• there are non value entries in your data
• remove rows with errors

Create Another Query

I worked on each those suggestions but still I couldn't resolve the issue. Then I did something that no one else suggested: I created a new Query from the original data and it reported an error, one error out of a total of more than 52,200 cells. Having found the error, I corrected it and I now had my model exactly as I designed it.

Searching for the Error(s)

I just worked on someone else's spreadsheet that is a treasure trove of data AND of mistakes. My solution of creating an alternative Query did not help me this time. What I knew was that I was being given an error message, a single error at that but I had no idea where it was. I knew there was an error since I had tried to unpivot a large table and it gave me the general [Data.Format.Error] message.

So, I rolled back to the Query to BEFORE I unpivoted the table and in the header of one column was the error highlighted. This is what I saw in the column headers ... you see the red bar at the top of the 2019 column ... there are errors in that column, therefore:

Now I knew where the error was. I then scrolled down that column until I found the cell with the word Error in it. Close & Load, go to the input table, correct the error and bingo! Problem solved! One bad cell out of 15,000 cells caused that problem!

There is no spreadsheet file for this page: it is for information.

Duncan Williamson

6th March 2020 Updated 22nd March 2020