CSV data source missing columns depending on how it is imported

[PowerAutomater]

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.

[PowerAutomater · Accepted solution]

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

[danextian]

Hi @PowerAutomater 

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

[PowerAutomater]

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:

[danextian]

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

[PowerAutomater]

The formula for me looks like this:

= SharePoint.Files("https://sharepoint.folder.path/", [ApiVersion = 15])

There isn't a column parameter. And if I click on the choose columns button, there is the same limited number of columns listed, it's like it's not getting fully imported or something.

[danextian]

That is a SharePoint folder connection and doesn't directly connect to a file stored in sharepoint. Use the full path to the file and the web connector instead of that.

Use the path only up to the extension in case there is an extra text string.