Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
CWPRenewables
Frequent Visitor

CSV Carriage Returns Splitting Data to New Rows in Power Query

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?

1 ACCEPTED SOLUTION
JasonC_XBI
Advocate II
Advocate II

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"

View solution in original post

2 REPLIES 2
ckalbfleisch
Regular Visitor

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
Advocate II
Advocate II

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"

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors