cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Syndicate_Admin
Administrator
Administrator

Power Query Editor incorrectly creating new lines from csv source

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)

PowerBI Error.png

 

 

 

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Syndicate_Admin
Administrator
Administrator

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.

View solution in original post

4 REPLIES 4
Syndicate_Admin
Administrator
Administrator

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

Syndicate_Admin
Administrator
Administrator

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:

BA_Pete_0-1642590669522.png

 

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

BA_Pete_1-1642590714169.png

 

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

 

Pete

Syndicate_Admin
Administrator
Administrator

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.

 

 

Syndicate_Admin
Administrator
Administrator

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.

Helpful resources

Announcements
Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on August 17, 2022 at 8am PDT.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Kudoed Authors
Users online (1,847)