cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Lenihan
Helper III
Helper III

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

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.

1 ACCEPTED SOLUTION

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

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

7 REPLIES 7
MFelix
Super User
Super User

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

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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.

 

 

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

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

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
Frequent Visitor

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
Continued Contributor
Continued Contributor

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
Frequent Visitor

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.