Help me interpret error message: We couldn't convert to Number. <ccon>1:47</ccon>

[etane]

Hello.

My Dataflow wouldn't load this morning because one of my tables failed to refresh due to this message:

Error: DataFormat.Error: We couldn't convert to Number. <ccon>1:47</ccon>. RootActivityId = 4fdf0f6c-42c0-48a9-b9e0-35a09ac22eb6.Param1 = DataFormat.Error: We couldn't convert to Number. <ccon>1:47</ccon> Request ID: 0d57d5aa-6f62-b5f5-53e6-72ad4512718e.

When I open up the table, the table loads fine.  There's no error message or anything.  But, the Dataflow just wouldn't refresh.  

So, I am trying to isolate which Power Query step within the table is causing the error.  However, I can't find it based on the error message.  What does <ccon>1:47</ccon> mean?  In the past, I could locate where the error occurs for example:

We couldn't convert to Number. <ccon>>10</ccon> tells me the 10th step of format conversion is generating an error.  But, what/where is 1:47?  

Thanks.

[BA_Pete · Accepted solution]

Hi @etane ,

If the query loads ok but you get an error on refresh, it usually means you have a single cell that is resolving to an error. From the error provided, I would guess that you ingest manually-entered data (Excel file etc.) and, during your transformations, you change the data type of one of your columns to Number type.

If that sounds correct, then select any columns that you change to number type, go to the Home tab > Keep Rows (dropdown) > Keep Errors.

Hopefully, one of these columns will show a row with an error value in it - this will be the cell that someone has entered 1:47 into (probably instead of 1.47).

Pete

[etane]

@BA_Pete   Thanks!  Now I know how to isolate an error using keep errors.  Error was caused by a calculated field in SFDC.  Now I can throw it at the SFDC admin.

[BA_Pete]

No problem, happy to help.
Don't forget to give a thumbs-up on any posts that have helped you ‌‌👍

Pete