Forum Discussion

asjones's avatar
asjones
Icon for Helper V rankHelper V
2 years ago
Solved

Dataflows Gen2 – Import – Mashup - DataFormat Error

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 a...
Dataflows
  • asjones's avatar
    asjones
    2 years ago

    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's avatar
frithjof_v
Icon for Community Champion rankCommunity Champion
2 years ago

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.