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?

 

[DaveyD]

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:

1. Duplicate the original file
2. Name one of them 1 and the other 2
3. Open file 1 and delete lines 2-6 (i.e., all the lines that have multiline cells)
4. 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]

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]

still don't see double records

 

[sandy666]

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]

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]

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

 

[sandy666]

I got it, let me think a little about it

 

[DaveyD]

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

 

[DaveyD]

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]

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.