Forum Discussion
Skip rows in CSV before it is parsed by Power BI
Hi Tom, Lydia,
Unfortunately neither of your suggestions fixed the problem I was experiencing but I have found a solution.
The .csv files I had been working with are generated as output from a portal and I had been trying to load them directly into PBI.
It seems that if I open the .csv in Excel and save it (without making any changes) I can then load it into PBI with no problem...
Not sure why this works, but it does...
Thanks for your suggestions,
Regards,
Tony
This is an old question but I never found a satisfactory answer elsewhere, so I'm sharing my solution to hopefully ease the pain for someone else.
The way I found to skip n number of rows is to use the advanced editor. This avoids having to mess about with any manual steps when oyu want to update the source csv:
let
Source = Lines.FromBinary(File.Contents("C:/path_to_file.csv")),
SkipN = List.Skip(Source,1), // Replace 1 with the number of rows you want to skip
ConvertToTable = Table.FromList(SkipN, Splitter.SplitTextByDelimiter(","), null, null, ExtraValues.Error), //Assumes the delimiter is a comma
#"Promoted Headers" = Table.PromoteHeaders(ConvertToTable, [PromoteAllScalars=true])
in
#"Promoted Headers"Then select all your columns and run "detect data type" to simulate how PowerBI would detect data columns during the usual data connection process.
Advocate IThis is almost 100%, but you need to add QuoteStyle.Csv to the Lines.FromBinary, so quoted line breaks are treated as part of the data, not as the end of the current row:
let
Source = Lines.FromBinary(File.Contents("C:/path_to_file.csv"), QuoteStyle.Csv),
SkipN = List.Skip(Source,1), // Replace 1 with the number of rows you want to skip
ConvertToTable = Table.FromList(SkipN, Splitter.SplitTextByDelimiter(","), null, null, ExtraValues.Error), //Assumes the delimiter is a comma
#"Promoted Headers" = Table.PromoteHeaders(ConvertToTable, [PromoteAllScalars=true])
in
#"Promoted Headers"