Power Query Editor incorrectly creating new lines from csv source

[Tasberywood]

Hi All,

I have an issue that has be stumped.

We have a data export in a csv format from our till system that contains a lot of information on it, we import this csv into PowerBI and then start to extract out Management Information for analysis.

Up to now there has never appear to be an issue with getting the data into PowerBI for me to start to manipulate however yesterday I was doing some checking on the Power Query editor and I noticed that there were some "null" fields in a column that should have any "null" fields.

I opened the source document in Excel to ensure that there wasn't an error in the source data and on the row in question it had the data.

This was repeated on several lines in the Power Query editor and the only common thread I could see was they were all fairly recent dates. To try to explain the issue I have tried to explain it below:

The errors were grouped in two lines

The first line in each error was correct until column 17 and the rest of the table was blank (it should have lots of data)

The second line started as an error (it should have had an account reference but started with a comma and then a string of numbers seperated by a comma - screenshop attached)

After further analysis (and frustration) I found that the second line is the continuation of the first line so in the screenshot the 

the SRI230812 in Row 1 should have the data from Row 2 in columns 18-54.

Can anyone help with this, I have tried creating a new Power BI report and importing the same data and the same thing happens, my next step is to re run the data export but that is taking some time and after I have inspected the CSV in excel I can't see any reason why it would differ on these lines as the match the previous lines that have no errors.

Thanks

Tim

[Snowman4LFE]

Clicking on the gear icon beside "Source" in the Applied Steps should give you options for Common-Seperated Values.   You should select to "Ignore quoted line breaks" under "Line breaks".

[Tasberywood · Accepted solution]

Thank you everyone for your suggestions, my solution was to change the data source to an excel file, the issue appears to be down to the volume of data in the dataset which was causing issues.

[v-stephen-msft]

Hi @Tasberywood ,

Could you tell me if your problem has been solved?

You can click Error to go in and see what the error message is.

Here are some ways to troubleshoot:
1. Delete the data source in the data source settings and reconnect.
2. Upgrade Power BI Desktop to the latest version.
3. Check whether the data type is wrong.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BA_Pete]

Hi @Tasberywood ,

One possible issue is the fact that csv files in Power Query are imported using the same fixed number of columns that were present when you first imported them.

This means that, if you add columns to your source csv, PQ won't 'see' the new columns, because it's not looking for them.

In Power Query, go to the Source step of your csv import query and you will probably see something like this:

You can completely delete this column value argument, so you end up with something that looks like this:

PQ will now pick up any-and-all columns present in the csv, even if you add new ones in future.

Pete

[Michael19842]

@Tasberywood : there might be a line seperator (CR CRLF or LF) in your data. Can you attach the csv file?