Error DataFormat.Error: We couldn't convert to Number trying to add a data flow.

[nrowey]

Trying to create a new data flow keep getting this error: 

"DataFormat.Error: We couldn't convert to Number. 24200C. RootActivityId = 0be9b5d6-87b4-42e6-a4d2-30d2dd930d48.Param1 = DataFormat.Error: We couldn't convert to Number. 24200C Request ID: 3ffa7555-ce42-da36-73c8-f01616a25627."

I am connecting to Gen2 file Blob Azure where data is loaded as all Text , Date and Decimal format.

It seems like the Data is getting reformated by the DataFlow.

I have tried transforming after same result.

Is there a way to find the cause and fix this?

Thanks   

[aybarsacar]

Hi all,

Running into same error. How were you able to debug which column is creating the error.

My data source has around 100 attributes and I wasn't able to get a descriptive error log from Power BI that would direct me to the source.

How would I know which column creates the type casting error from the error log?

Cheers, 

[nrowey]

problem jumped to date columns , I switched everythig to text as you sugested and it looks like it will get me there.

I did not know you could change the import conversion that way.  Thanks for the help.    

[Tutu_in_YYC]

Good to hear. Just be aware, if Postingamount column has to be aggregated in your report, it has to have decimal number as data type. Text column cannot be aggregated using SUM function in DAX.

[Tutu_in_YYC]

Sounds like the auto conversion is trying to convert text to number. Open the dataflow and modify the data type change step for the column with issue, to type text. 

[nrowey]

I changed below excluding the dates and one Numeric still same error..

Table.TransformColumnTypes(#"Expanded table column", {{"Source.Name", type text}, {"companyid", type text}, {"accountingdate", type datetime}, {"accountnumber", type text}, {"control", type text}, {"control2", type text}, {"controltype", type text}, {"detaildescription", type text}, {"journalid", type text}, {"postingamount", type number}, {"postingsequence", type text}, {"refer", type text}, {"schedulenumber", type text}, {"statcount", Int64.Type}, {"rowlastupdated", type datetime}, {"cora_acct_code", type text}})

[Tutu_in_YYC · Accepted solution]

Looks like the only column left that is being converted to number is Postingamount. For testing, try converting Postingamount to Text too. If the dataflow runs, then my guess is a text has accidentally ended up in the Postingamount column.