Disappearing rows in Power Query

[Mubarakbabs]

I have some csv data. There are no missing values on my first column (ID column). However, when I load the data into Power Query, some of the values on the ID column disappear, and all the other columns on those rows are offset. So those values are now in columns with a different data type and causes a Type error. I'll attach a few screenshots to show the problem.

These are the rows with issues:

As you can see here, the columns are all displaced by one step, and the data from id column is completely missing. I've not performed any transformation on the data:

I went back to the csv to check out a few rows that are displaying error here. You can see that the id columns are there in the original data, and the names don't even match the names showing up in Power Query. 

This error doesn't happen when I convert the data to xlsx before uploading to Power Query. However, the Greek characters show up as gibberish (but they show up correctly when I upload the csv). 

What could be causing the error?

[Mubarakbabs]

Thank you for the response.

I think opening the csv in Excel messes it up somehow. I created a copy and imported it to Power Query directly, without opening it in Excel first, and the characters showed up correctly.

However, the disappearing rows issue persists.

I couldn't find a way to attach the csv here so I uploaded it to Google Drive.

Thanks

[foodd · Accepted solution]

So, the bottom line is that when you use  a csv from this source, the file will need to be cleaned up.  This is not unexpected.   The file you shared started with 9705 rows, 32 rows removed (carrage return or similar character), and 9673 rows remaining.   It was in Column #2 that caused you issues, and where I deleted the character, and made a single line.    I used power query to import to both Excel and to Power BI without issue.  The following image gives you an idea what a bad row looks like:

Copy this m-code into a blank query, update the source, and it should import without issue.  The fixed CSV is linked 

for download as well:

let
Source = Csv.Document(File.Contents("C:\Users\user\Downloads\latest(fixed).csv"),[Delimiter=",", Columns=18, Encoding=65001, QuoteStyle=QuoteStyle.None]),
#"Use First Row as Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Change Type" = Table.TransformColumnTypes(#"Use First Row as Headers",{{"id", Int64.Type}, {"name", type text}, {"host_id", Int64.Type}, {"host_name", type text}, {"neighbourhood_group", type text}, {"neighbourhood", type text}, {"latitude", type number}, {"longitude", type text}, {"room_type", type text}, {"price", Int64.Type}, {"minimum_nights", Int64.Type}, {"number_of_reviews", type text}, {"last_review", type text}, {"reviews_per_month", type number}, {"calculated_host_listings_count", Int64.Type}, {"availability_365", Int64.Type}, {"number_of_reviews_ltm", Int64.Type}, {"license", type text}})
in
#"Change Type"

Link to download fixed CSV file:  https://gofile.io/d/Vl9AyQ

[foodd]

There appear to be one or more issues to resolve with the CSV, and it would be helpful if you add the CSV to the post.

One workaround would be to update the file encoding for handling in English under Windows.

First, make a copy of the file.  Next, try the following, and then attempt to import 

with Power Query again.

1. Open the CSV file using Notepad.
2. Click "File > Save As".
3. In the dialog window that appears - select "ANSI" from the "Encoding" field. Then click "Save".
4. Open this new CSV file using Excel, your non-English characters should be displayed properly.