Issue adding CSV file using a folder as source

[agagnon]

Hello,

I have been having an issue trying to add a new source of data to my model using a folder as the source.  When I try to add this file that has about 96,000 lines I keep getting the same error not matter what I try.  Through some investigation I have tried a few things which only baffle me further.

The image below is a sampling of the errors that I am getting.  To debug I went to line 29846 in the source file and I did not see anything wrong with it.  I then tried making it an Excel document and formatting it as a table which reveled something that was odd but not sure if it was the problem.

Snapshot of some errors

In this image you can see nearly every line in the document that is ok is a single line which is not expanded.  The line that is the issue for some reason is expanded and the cell in particular that seems to be causing the issue is selected.  What I found is that when looking at this in CSV format the "January-December 2019" text does not appear to show up unless the cell is expanded.  To be clear the line that starts with date "1/9/2019" is row 29846 that seems to cause the error.

Is there anything obviously wrong with the data on this row or anyway I can get around these errors?  I am frankly having a tough time even figuing out what the issue is never mind the solution!

What makes things more confusing is that if I import only that single line it seems to work.

[agagnon · Accepted solution]

If anyone stumbles upon this I found the answer!  The issue was that the data I was exporting from the accounting system had "carriage returns" in some of the cells which seems to upset PowerBI.  A quick google seach of "Remove Carriage Returns" results in many different ways to approach the issue and once I did that to my data file I was successfully able to use the data.

I hope this helps someone down the line!

[parry2k]

@agagnon can you share CSV file causing the error wiht pbix file, may be change sensitive information to dummy data. would like to look at M script to understand where things are falling apart.

[agagnon]

I have created a new PBIX file and deleted alot of the data out of the CSV file and I am still getting the error.

What is the easiest way for me to share this with you?

[parry2k]

@agagnon share thru google drive or one drive, or send directly by email. My email is in my signature.