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
PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

Top Solution Authors
Top Kudoed Authors
Users online (3,370)