Forum Discussion
Disappearing rows in Power Query
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
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
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