I want to automatically import and format CSV files into excel, but when I export the CSV files from another application it adds misc info to the top 14 lines, with line 15 containing the actual headers I want. I wrote the following Power Query M script to format this:
let
Source = Csv.Document(File.Contents(Filepath), [Delimiter = ",", QuoteStyle = QuoteStyle.None]),
#"Removed Top Rows" = Table.Skip(Source, 14),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true])
in
#"Promoted Headers"
However, when I don't define Columns = x in the Csv.Document function, it will only import as many columns as are in the row 1 of the original document, not the number of columns in row 15 and thus cutting off much of the data I need.
Obviously I could just define Columns = x for my query, but I want it to work dynamically depending on the number of columns in row 15, regardless of what happens in row 1. Any help would be appreciated in figuring out a workaround solution for this quirk. Thanks!
This does not sound correct.
I imported a CSV with 14 rows of 7 columns followed by 5 rows of 12 columns and it works perfectly, importing 12 columns
Source = Csv.Document(File.Contents("C:\temp2\Book.csv"),[Delimiter=",",QuoteStyle=QuoteStyle.None])
However you can try
let Source = List.Skip(Lines.FromBinary(File.Contents("C:\temp\book.csv")),14),
Source2 = Csv.Document(Text.Combine(Source, "#(cr)#(lf)"),[Delimiter=",",QuoteStyle=QuoteStyle.None])
in Source2