Power Query .csv Cells Have Multiple Lines Delimits Imported Data Thereby Misaligning Columns

[Judsonteacher]

Hi,

I am using Power Query (from web, Sharepoint link) to import some data from a single .csv which has some cells with multiple lines.  I don't need to manipulate those cells, but the imported data is misaligned after importing.

For example: Here is the data from the .CSV (you can see it's in the notes column)

But after importing the data, what I guess is happening is the multiple lines is being delimited into subsequent columns which makes everything after this entry in the wrong column.  

You can see now the data from "Notes" is under "Staff: Center"

I have not done any transformations.  I have tried re-exporting the raw data multiple times, relinking the file, deleting and building it over again multiple times, all to no avail.

Any suggestions how to fix?

[Judsonteacher · Accepted solution]

I fixed the problem by setting the Query Source option about "Line Breaks" to "ignore quoted line breaks"

[jbwtp]

Hi @Judsonteacher,

Do you mind having a read of this: https://blog.crossjoin.co.uk/2018/03/09/an-in-depth-look-at-the-csv-document-m-function/ ? I think the issue that you have may relate to how the CSV.Document imports your data and Chris in his blog has a realy deep dive into how this function can be configured with various settings.

Hope this will help.

Kind regards,

John