Dataflows Gen2 – Import – Mashup - DataFormat Error

[asjones]

I am using Dataflows Gen2 to import a series of CSV files and put them in a Lakehouse. Unfortunately, I keep getting issues saying items are numbers instead of text. However, the columns the values are in are already set to number. The only way I was able to prevent the related errors was to set all columns to Text.  Otherwise, when I publish the file, I get errors  (full error below). I have a few fields that really are number (whole or decimal) … yes the entire column.

I also checked and tested the “Data destination” section to “Lakehouse”. In the Choose destination settings I tried “Use automatic settings” both on and off. When off I have set update method to replace, and schema to dynamic. The column matching sees to all match and be appropriate.

What am I missing?

thanks

Alan

GL_Export_System_WriteToDataDestination: Mashup Exception Data Format Error Couldn't refresh the entity because of an issue with the mashup document MashupException.Error: Error in replacing table's content with new data in a version: #{0}., InnerException: We couldn't convert to Number., Underlying error: We couldn't convert to Number. Details: Reason = DataFormat.Error;Message = We couldn't convert to Number.;Detail = GT5002HIGT00;Message.Format = We couldn't convert to Number.;Microsoft.Data.Mashup.Error.Context = User GatewayObjectId: xxxxxxxxx

GL_Export_System_WriteToDataDestination: Mashup Exception Data Format Error Couldn't refresh the entity because of an issue with the mashup document MashupException.Error: Error in replacing table's content with new data in a version: #{0}., InnerException: We couldn't convert to Number., Underlying error: We couldn't convert to Number. Details: Reason = DataFormat.Error;Message = We couldn't convert to Number.;Detail = MX-56412;Message.Format = We couldn't convert to Number.;Microsoft.Data.Mashup.Error.Context = User GatewayObjectId: xxxxxxxxx

GL_Export_System_WriteToDataDestination: Mashup Exception Data Format Error Couldn't refresh the entity because of an issue with the mashup document MashupException.Error: Error in replacing table's content with new data in a version: #{0}., InnerException: We couldn't convert to Number., Underlying error: We couldn't convert to Number. Details: Reason = DataFormat.Error;Message = We couldn't convert to Number.;Detail = GT1339HIGT00;Message.Format = We couldn't convert to Number.;Microsoft.Data.Mashup.Error.Context = User GatewayObjectId: xxxxxxxxx

[asjones · Accepted solution]

@frithjof_v 

Thankyou for the suggestion of using keep rows -> keep errors.

All my other tests like importing into Excel did it "right". However DataFlows Gen2 had issues and mised up some columns. 
Sharing the information below incase it helps someone else.

I went into the CSV import code

Csv.Document(...) 

I changed the Quote Style to QuoteStyle.None and it fixed my issue
documetation
https://learn.microsoft.com/en-us/powerquery-m/csv-document

• QuoteStyle: Specifies how quoted line breaks are handled.
  • QuoteStyle.Csv (default): Quoted line breaks are treated as part of the data, not as the end of the current row.
  • QuoteStyle.None: All line breaks are treated as the end of the current row, even when they occur inside a quoted value.

[frithjof_v]

Keep the columns as numerical type. Inside the dataflow editor, try selecting all columns and then select keep rows -> keep errors. 

See if any rows are returned. Those rows will contain the error values.

Probably some values in the column which are not formatted properly as number. 

Perhaps it contains a wrong decimal symbol or some non-numeric character.

If the error is due to wrong decimal symbol, you can use 'change type using locale', or just use 'replace value' to replace a decimal symbol with another decimal symbol.

If there is some other reason for the error, handle the erroneous data accordingly.

If there are no error rows, then I don't know.