# Power Query - Handling multi line cells



## DaveyD (Jan 5, 2020)

I receive a folder of csv files containing addresses.
Many of these addresses have 2 lines for the addressee (e.g. with a c/o).
I am trying to import all the files into power query so I can make some transformations (including removing duplicates) 
However, the cells that have 2 lines are being broken into a different record.
Is there a way I can handle this in power query?


----------



## sandy666 (Jan 5, 2020)

any csv file as an example?


----------



## DaveyD (Jan 6, 2020)

@sandy666 - Thanks.
Please download from here


----------



## sandy666 (Jan 6, 2020)

where are different records or two lines?


----------



## DaveyD (Jan 6, 2020)

If you enable "Wrap Text" you will see many of them in the *EnvelopeName *column
The first line is regular, but lines 2-6 are multiline
Line 7-8 are regular


----------



## DaveyD (Jan 6, 2020)

I just realized that when importing the files through power query one at a time, it handles the multi-line fine. 
It's only when importing multiple from a folder where the problem begins.
Please duplicate that file and then import from the folder - then you will see the problem.


----------



## sandy666 (Jan 6, 2020)

so in Power Query Editor you've



and you want



if so select this column and use
= Table.ReplaceValue(_Previous_Step_,"#(lf)"," ",Replacer.ReplaceText,{"EnvelopeName"})


----------



## sandy666 (Jan 6, 2020)

DaveyD said:


> duplicate that file and then import from the folder


sure


----------



## sandy666 (Jan 6, 2020)

you see duplicates but I don't see any problem  

after use
= Table.ReplaceValue(_Previous_Step_,"#(lf)"," ",Replacer.ReplaceText,{"EnvelopeName"})
I got


----------



## sandy666 (Jan 6, 2020)

I'd like to see this:


DaveyD said:


> the cells that have 2 lines are being broken into a different record


----------



## DaveyD (Jan 5, 2020)

I receive a folder of csv files containing addresses.
Many of these addresses have 2 lines for the addressee (e.g. with a c/o).
I am trying to import all the files into power query so I can make some transformations (including removing duplicates) 
However, the cells that have 2 lines are being broken into a different record.
Is there a way I can handle this in power query?


----------



## DaveyD (Jan 6, 2020)

Ok, I'm sorry but I finally figured out when the problem occurs.
When importing from a folder, power query assesses the first file to decide how to import.
If the first file contains line breaks in cells, then it realizes to import with that in mind.
However, if the first file does not have any multline cells, then it does not import the rest of the files (that do have multiline cells) correctly.

If you dont mind, please follow the following steps to reproduce the problem:

Duplicate the original file
Name one of them 1 and the other 2
Open file 1 and delete lines 2-6 (i.e., all the lines that have multiline cells)
Now import them from the folder - power query will assess the file 1 first and you will then see the problem with file 2.

Please let me know if you are able to duplicate it.


----------



## DaveyD (Jan 6, 2020)

Here is a screenshot of what I am seeing
Realize that in line 5 there is no data after column 2 because it broke into the next record
And now row 6 column 1 contains his business name


----------



## sandy666 (Jan 6, 2020)

still don't see double records


----------



## sandy666 (Jan 6, 2020)

DaveyD said:


> Realize that in line 5 there is no data after column 2 because it broke into the next record
> And now row 6 column 1 contains his business name


so maybe post representative examples


----------



## DaveyD (Jan 6, 2020)

1. Do you see the problem in my screenshot?
2. Did you follow the exact steps that I mentioned?
Start with a fresh query from folder and make sure the file without multiline cells is the one that power query is analyzing first.

Please let me know.


----------



## DaveyD (Jan 6, 2020)

Please test it with these 2 files:
File 1
File 2


----------



## sandy666 (Jan 6, 2020)

I got it, let me think a little about it


----------



## DaveyD (Jan 6, 2020)

Great! Thanks! (Sorry for the hassle...)
Looking forward...


----------



## DaveyD (Jan 6, 2020)

I think I figured it out...
When you import from the folder, power query creates a custom function to read the csv file using the "Csv.Document" function
When it creates it based on a file that has multline cells, it uses QuoteStyle.Csv as the final argument for that Csv function.
However, when it creates the function based on a file without  multiline cells, it uses QuoteStyle.None as the last parameter.
That last argument is the key to this behavior.
If I go to the function, click on Advanced Editor, and change that argument to .Csv, everything works perfect!

Do you see any problem with this approach?
Power Query warned about changing the function directly - I didn't really understand the warning.

Please let me know what you think
Thanks,
David


----------



## sandy666 (Jan 6, 2020)

if it works for you that's ok so you solved the problem by yourself 

Theory:
_QuoteStyle.Csv
About
Quote characters indicate the start of a quoted string. Nested quotes are indicated by two quote characters.

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._


----------

