CSV Carriage Returns Splitting Data to New Rows in Power Query

Question

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?

jasonc_xbi · Accepted Answer

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"

Forum Discussion

CSV Carriage Returns Splitting Data to New Rows in Power Query

2 Replies

Related content

Insert Line Break (carriage return?) in Text.

Replace carriage return in dataset

Check for existence of line feeds / carriage returns

Split cell into rows base on line feed carriage return is not working

Carriage Returns Splitting Data to a new row since April Release.