Forum Discussion
Microsoft EmployeeImport all (including recently added columns) from CSV file at refresh
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])
Community ChampionThe 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])
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.
ed7Thank 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.
- cabc_xlorla
Advocate IV
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 😝
Hello I have the same problem in a file stored in a sharepoint site:
= Csv.Document(Web.Contents("https://sonepar.sharepoint.com/sites/O365SoneparglobalITteam-NEWLicenseControlInformation/Documents%20partages/NEW%20License%20Control%20Information/Assigned%20licenses/AzureAD_Users.csv"),[Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.None])
and I get this error:
DataSource.Error: Web.Contents failed to get contents from 'https://sonepar.sharepoint.com/sites/O365SoneparglobalITteam-NEWLicenseControlInformation/_api/web/getfilebyserverrelativeurl('/sites/O365SoneparglobalITteam-NEWLicenseControlInformation/Documents%20partages/NEW%20License%20Control%20Information/Assigned%20licenses/AzureAD_Users.csv')/$value' (404): Not Found
Details:
DataSourceKind=Web
DataSourcePath=https://sonepar.sharepoint.com/sites/O365SoneparglobalITteam-NEWLicenseControlInformation/Documents%20partages/NEW%20License%20Control%20Information/Assigned%20licenses/AzureAD_Users.csv
Url=https://sonepar.sharepoint.com/sites/O365SoneparglobalITteam-NEWLicenseControlInformation/_api/web/getfilebyserverrelativeurl('/sites/O365SoneparglobalITteam-NEWLicenseControlInformation/Documents%20partages/NEW%20License%20Control%20Information/Assigned%20licenses/AzureAD_Users.csv')/$value
