CSV Carriage Returns Splitting Data to New Rows in Power Query

[CWPRenewables]

I am trying to transfer an old Excel query into Power BI, but have encountered an issue with carriage returns in my CSV source data.

It seems that when a csv dataset has carriage returns within cells Power BI automatically assumes this is a new row of data.

I can't work out what is happening, because the original Excel report has connected to the data using the Excel.CurrentWorkbook() connector, so I can only see that the data is being handled differently in the Excel file than it is in Power BI.

I can't apply any formulas in Power BI to remove the carriage returns because it is doing the split to a new row in the Source step. I'd prefer not to have to manipulate the data in excel prior to adding to Power BI.  I'm not lazy, i just want to be efficient.

Is there a setting I can adjust to avoid this row split?

[ckalbfleisch]

Extremely helpful. This solved my issue. But in order to better understand it I also found this article to explain the various Encoding Tpes: TextEncoding.Type - PowerQuery M | Microsoft Learn

[JasonC_XBI · Accepted solution]

The issue is to do with the QuoteStyle being used in the query.

For more info, see the Official Documentation:

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

To resolve it, edit your query as follows :

Csv.Document(file, [Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.Csv])

That is, make sure to have ".Csv" following QuoteStyle, instead of ".None"