Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
ed7
Employee
Employee

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

6 REPLIES 6
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

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.

@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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.

Top Solution Authors
Top Kudoed Authors