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

[Lenihan]

Hi - hopefully someone can help me.

It seems with the April 2017 release, when we add a csv dataset that has carriage returns within cells, Power BI for Desktop automatically assumes this is a new row of data. This wasn't happening prior to us upgrading to April 2017 Release. Is this something I can turn off? I can't apply any formulas in power bi to remove the carriage returns because it is doing the split to a new row right away.  I'd prefer not to have to manipulate the data in excel prior to adding to Power BI.  

The data I'm importing has some of the same data that we were importing prior, and it did not split the cell contents to new rows of data. This is something new happening.

[MFelix]

Hi @Lenihan,

In the April update it was added the split column by delimeter into row this option can be change in the query editor check you query and follow the steps in the blog to check if in your file it's assuming the row instead of column and change accordingly to stop that.

https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-april-feature-summary/

Regards,
MFelix

[Lenihan]

Hi @MFelix

Thank you for the reply. I don't know if this is the same, as this is something that is happening automatically as soon as I import the data. And it isn't column based - it is taking a row of data and splitting it into another row of data if a new line exists within the cell.  After I import the file, under applied steps, I do not see a split happening.  Only 3 applied steps happening 1. Source, 2. Promoted Headers, and 3. Changed Type.  I have nothing I can undo with respect to split column for my newly imported data.

[MFelix · Accepted solution]

When you have a csv file it automaticly makes the split to column so your problem should be in the Source option.

[Lenihan]

Thanks @MFelix

Maybe they changed the default setting in the source settings. I checked my older files, and they were set to Ignore line breaks. The new file added was set differently.

Thanks for your help!

[obera]

Hello, I know this is many years later, but can you explain what you mean by your files being "set to Ignore line breaks"? I am having this issue and I can't tell by the marked solution or your response how to fix it . . .

[otravers]

I'm resurrecting this old thread because it's a top search result for this issue. To resolve it, make sure to have the following QuoteStyle in bold below, instead of "None":

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

Per the 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."

[Nawaaz]

This worked for me, thanks very much. This should be the accepted solution!