Forum Discussion
Skip first row of CSV file before processing
Hi GW999
Try this on the AddFileContentsAsColumn step
AddFileContentsAsColumn = Table.AddColumn(#"folder1", "Custom", each Table.PromoteHeaders(Table.Skip(Csv.Document([Content],[Delimiter=",", Encoding=1252]),1)))Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Try specifying the number of columns in the second argument of Csv.Document(). Instead of the current
[Delimiter=",", Encoding=1252]
try
[Delimiter=",", Columns = 2, Encoding=1252]
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
GW999
The code also works with my test files, just recheck the csv files make sure there is no error or format problem.
Best regards
Paul
Thanks for the help everyone. I played around with the data files and I think I have worked out what is going on. I was careful to only use Notepad to open and edit files rather than Excel which will ofteen change the data. But basically, the first row in each of my data files does not have a comma at the end:
----------------------------------------------------------
01 - Teams on XXXXXXX Gateway [XXXXXXXXX]
Date,Values
14/12/2020 05:30,0
14/12/2020 05:25,0
14/12/2020 05:20,0
14/12/2020 05:15,0
14/12/2020 05:10,0
14/12/2020 05:05,0
----------------------------------------------------------
If I open the files in Notepad and add a comma manually to the end of that first row as a test then our modified Power Query works fine using the Table.Skip function. I tried putting it back in and then removing it again just to be sure . It's interesting, because I would have thought that a row is terminated by a CR/LF character rather than comma, which should only act as the field delimiter in our CSV?:
let
Source = AzureStorage.Blobs("mydata"),
#"folder1" = Source{[Name="folder"]}[Data],
AddFileContentsAsColumn = Table.AddColumn(#"folder1", "Custom", each Table.PromoteHeaders(Csv.Document([Content],[Delimiter=",", Encoding=1252]))),
#"Expanded Custom" = Table.ExpandTableColumn(AddFileContentsAsColumn, "Custom", {"Date", "Values"}, {"Date", "Values"}),
Is there any way we can remove the first row without requiring a comma delimiter at the end of the first row?