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
– Alexis Olson, Commented Apr 12, 2019 at 20:08
@AlexisOlson I agree the format is non-standard; and I have no control over how it is generated. — Ron Rosenfeld
– Ron Rosenfeld, Commented Apr 12, 2019 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
– Patrick, Commented Oct 5, 2020 at 8: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
– Patrick, Commented Oct 5, 2020 at 9:00

Alexis Olson · Accepted Answer · 2019-04-12 20:21:23Z

1

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"

answered Apr 12, 2019 at 20:21

Alexis Olson

40.4k8 gold badges51 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ron Rosenfeld Over a year ago

Thanks for that. Simple and effective.

Collectives™ on Stack Overflow

Power Query not able to add columns when refreshing CSV import

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related