Forum Discussion
CSV data source missing columns depending on how it is imported
When I use the the Text/CSV import option of "Get Data" all of the columns from my local CSV file come through and are avilable to use for visualisations. If I then upload the same CSV file to sharepoint and import this data source via the sharepoint folder import option, only 4 or so of the columns come through and I'm really puzzled as to why. They are a copy of the same file, just being imported via different options. I also use the load option each time.
Is there a setting or something I need to adjust for the online file to import the same as the local one? I need the source to be online so it is accessible to others, as it will be getting replaced every so often.
Worked it out. I needed to choose transform data instead of load, then click on Binary in the first column. This video goes through it: How to connect to a data file in a SharePoint folder in Power BI
6 Replies
When combining multiple CSV files using the folder connection (whether local or sharepoint) and is done using the combine and transform feature of the GUI, Power Query uses a sample file as a basis for transformations to be done as well as the columns to pick so it is possible that the sample file doesn't have all the columns. In the screenshot below, Table.ColumnNames parses the the column names from Sample File as a list and uses this list to pick which columns to expand.
I would remove that applied step and manually expand the tables
Hi danextian , I am actually not combining multiple files I am using a single CSV file.
I couldn't find the steps you meant unfortunately as I only have source when importing:
And the query just looks like this:You are trying to directly connecting to a CSV file in sharepoint? The Columns parameter determines the number of columns the connection is going to try to parse but it is actually optional so you can remove this part: Columns=15,
= Csv.Document(File.Contents("path\file.csv"),[Delimiter=",", Columns=15, Encoding=1252, QuoteStyle=QuoteStyle.Csv])
Worked it out. I needed to choose transform data instead of load, then click on Binary in the first column. This video goes through it: How to connect to a data file in a SharePoint folder in Power BI
