Power Query not able to add columns when refreshing CSV import

Question

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)

Modify the CSV:

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

Refresh the query:

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?

Can you not add a header row in the CSV file or add commas at the end of the first two rows? Your modified CSV isn't in a very standard format. — Alexis Olson, Apr 12 '19 at 20:08
@AlexisOlson I agree the format is non-standard; and I have no control over how it is generated. — Ron Rosenfeld, Apr 12 '19 at 20:11
I think this is a bug since I encountered this problem with a "normally" formatted CSV file where all lines have the same number of columns. If I call a "refresh" on my existing query, the newly added columns of my CSV are not picked up. — Patrick, Oct 05 '20 at 08:52
My bad, If I delete the "Columns=XX" part of the query then it works fine in my case! I should have read your question thoroughly. — Patrick, Oct 05 '20 at 09:00

score 1 · Accepted Answer · answered Apr 12 '19 at 20:21

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"

Thanks for that. Simple and effective. – Ron Rosenfeld Apr 12 '19 at 20:46 — Ron Rosenfeld, Apr 12 '19 at 20:46

Power Query not able to add columns when refreshing CSV import

1 Answers1