Import all (including recently added columns) from CSV file at refresh

[ed7]

I imported time series CSV file with 64 columns. Every day there is a new column added to the file. But when I refresh it is not imported.. I checked the query and PBI explicitly put 64 columns when I donwloaded the CSV file first time: 

Query: 

Source = Csv.Document(Web.Contents("LINK"),[Delimiter=",", Columns=64, Encoding=65001, QuoteStyle=QuoteStyle.None])

Is there a parameter in Power Query to say not 64 but ALL columns, If not any other workarounds, eg calculate # of columns every time and then use is as a parameter for the query.  How can I do it?

Thank you in advance.

PS: I found a workaround. Set column parameter to 365 to have enough capacity for the year. than after unpivoting timeseries data removed all rows without dates. 

[edhans · Accepted solution]

The Columns parameter is optional. Just get rid of it. Because they are labeled as they are in the SOURCE line, you don't need to leave a space for it with an empty comma.

Source = Csv.Document(Web.Contents("LINK"),[Delimiter=",", Columns=64, Encoding=65001, QuoteStyle=QuoteStyle.None])

becomes

Source = Csv.Document(Web.Contents("LINK"),[Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.None])

[mattygduffy]

Is there another way? I learnt this same technique in a course I did, but when I try it on my client's machine, it just keeps the first column and removes all the others. I'd really appreciate any assistance.

[cabc_xlorla]

@edhans  Thanks! this is what I needed.  Usually it´s all about how you "google" your question, meaning how you pose the question to get the key results from Google! ha 😝

[ed7]

Thank you. How to deal with next line in the query that doesn't see new columns: 

#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text},

....

{"Column60", type text}, {"Column61", type text}, {"Column62", type text}, {"Column63", type text}, {"Column64", type text}}),

[edhans]

I don't know of a dynamic way to format column types as they come in. You could use something like:

Table.TransformColumnTypes(#"Added Custom", {{Table.ColumnNames(#"Added Custom"){3}, type text}, {Table.ColumnNames(#"Added Custom"){4}, type text}})

That will format the 4th and 5th column (Power Query indexes at zero, so {3} is column 4) to text without knowing the column names, and this will bomb if there are only 4 columns, as the {4} references the 5th column.

Instead of doing that, have you considered pivoting your data? Typically identical data in many columns is better in 2 columns - one for the field type and one for the data. You could do that by selecting the first 1-3 columns (or whatever isn't your data) and then "Pivot Other Columns" on the Transform menu. That will always handle new columns coming in.

We'd really need to see more of your data and what you are trying to accomplish to help. But I think the answer I gave above does at least guarantee your data is in your model. If so, please mark it as a solutoin, and consider a new thread to focus on how your model should look to see if having 50+ columns is ok or should you try and normalize the data. See the Normalizing an Example table example here to see what I am referring to.

[edhans]

@ed7 - pure coincidence. This issue hit Twitter today. There is a good article in that rhread that will show you how to use code to set data types on a dynamic number of columns here.