Search code examples
excelcsvoffice365powerquerym

Power Query not able to add columns when refreshing CSV import

If a line is added to a csv file, and that line has more "columns" than the original, Power Query won't add columns for the new line when refreshing an existing query.

If a new query is generated, then the extra column will appear. I'm guessing this is because PQ looks at the first 200 (by default) lines to decide on the format when creating the Query.

Original csv file:

key1:value1,key2:value2
key3:value3,key4:value4

Original M-code

let
    Source = Csv.Document(File.Contents("d:\Users\Ron\Desktop\myCSV.csv"),[Delimiter=",", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None])
in
    Source

It will import and generate: (as expected)

enter image description here

Modify the CSV:

key1:value1,key2:value2
key3:value3,key4:value4
key5:value5,key6:value6,key7:value7

Refresh the query:

enter image description here

Note that the third column from the third line is not imported.

A new query will pick up that third column, but the Columns= parameter gets changed to 3.

I have tried deleting the Columns=2 argument from the Source statement in the M-Code (as suggested in other posts) but that will not create the third column (unless that extra column exists in the first row).

If the Columns=2 argument is changed to Columns=3, then all three columns will be imported as desired, but there is no way of knowing in advance the number of potential columns.

I suppose one work-around would be to specify a Columns= value that will be larger than the largest conceivable number of columns that might be there, and then delete the blank ones, but it seems there should be a better solution.

Any thoughts?

Solution

  • I think the easiest approach is to load it as a single column using the line break as your delimiter and then split the columns in the query editor.

    let
    Source = Csv.Document(File.Contents("d:\Users\Ron\Desktop\myCSV.csv"),[Delimiter="#(lf)", Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3"})
in
    #"Split Column by Delimiter"