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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors