cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ed7
Microsoft
Microsoft

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

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. 

 

1 ACCEPTED SOLUTION
edhans
Super User
Super User

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])

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

5 REPLIES 5
edhans
Super User
Super User

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])

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

@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 😝

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}}),

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

@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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Top Solution Authors