Power Query - Handling multi line cells

[DaveyD]

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]

any csv file as an example?

 

[DaveyD]

@sandy666 - Thanks.
Please download from here

 

[sandy666]

where are different records or two lines?

 

[DaveyD]

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]

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]

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]

duplicate that file and then import from the folder

sure

 

[sandy666]

you see duplicates but I don't see any problem

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

 

[sandy666]

I'd like to see this:

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