Line break causing issues combining .csv file with comma delimiter

[Rosie_222]

Hey All,

Ran into a problem this week and I'm curious if anyone knows why it occurs, plus if there's a simpler way to fix it than what I had in mind. I work in marketing and use excel power query and power pivots to summarise our data (they're amazing for this). I'll frequently use power query to get and combine multiple .csv files in a sharepoint folder, this makes it easy for me to update the data set daily.

Recently I encountered an error where some data was entering as a new row when it should have been part of the previous row and was therefore ending up with data in the wrong columns etc. On investigation I found this wasn't due to a badly placed comma outside of "" but instead due to a cell which contained a line break. This cell contained text as below:

"TEXT | TEXT | TEXT
"

When I use the combine feature I'm determining the delimiter as a comma so I'm not sure why this occurs and would love to understand it better.

I came across a potential solution where you change the delimiter so the data reads into one cell, allowing me to remove the line break in the specific example above before splitting by the delimiter. It's likely that the data with the line break will keep cropping up so ideally I want to build a fix into the steps but I can't help thinking there must be an easier way? If not, does anyone know how to represent a line break in the M code?

Happy to share more details if needed!

Thanks,

 

[Muhammad_Usman]

In Power Query M code, you can represent a line break or newline character using the #lf or #(10) escape sequence. Here's how you can use it:

let
myText = "This is line 1.#lfThis is line 2."
in
myText

In the above code, #lf or #(10) represents a line feed character, which is typically used for line breaks in text. You can use this within your M code to insert line breaks or newlines wherever needed, such as within text strings or comments. When you use this code to load data or create custom columns in Power Query, the line breaks will be respected.