Power Query: It’s Good to Share(Point) Part 3

9 October 2024

Welcome to our Power Query blog.  This week, I successfully extract a CSV file from a SharePoint site to Power Query.

In this series, I am considering how to access data on SharePoint.  SharePoint is used by many companies to store organised data which may then be accessed and shared throughout the organisation.  I will be starting with a single file and then move on to consider combining data in a SharePoint folder. 

The data for this series will come from the following SharePoint folder:

Regular readers will be familiar with the expense data in the form of CSV files, I’m sure!

In Part 1, I established that it wasn’t sufficient to use the mapped drive on my PC that points to the SharePoint Site:

I would like to create a solution that can be shared with needing to change the path in File.Contents().  I need to use the ‘From Web’ option in the ‘From Other Sources’ section on the ‘Get Data’ dropdown:

This prompts me for the URL for a webpage, which in my case will be the CSV I wish to access:

Last time, I copied the URL from the SharePoint file:

I used this information in the ‘From Web’ dialog in my blank Excel workbook, using the shortcut CTRL + V:

After choosing the access method, I am prompted to ‘Sign in’ to SharePoint. 

Having signed in, I click Connect.

I receive an error telling me to modify the URL, which is currently in the form:

https://sumproduct0.sharepoint.com/sites/SumProductTeam/Shared%20Documents/General/Kathryn/PQ_StandardExpenses/PQ_StandardExpense_CSV_1.csv?web=1

I choose to Edit, and remove the string ‘?web-1’ as requested:

I may now click ‘OK’ to import the CSV:

Note that I am not presented with the web Navigator dialog: this is the same dialog that I would see when importing a CSV file from my computer.  I choose to transform the data:

This time, the Source step is extracting the data from the web, which means that another user in my organisation may access these queries.  However, since I am using my credentials, and they are saved to allow me to access the same data without entering the credentials again, I may wish to remove my saved credentials.

I may access the credentials from the File tab, where I find ‘Data Source Settings’ on the ‘Options and Settings’ tab.

This allows me to view any saved settings for data sources I have used.

I am only interested in the ‘Data sources in current workbook’.  On the ‘Clear Permissions’ dropdown I have two options:    

If I had other sources in the workbook that required credentials, I could choose to ‘Clear All Permissions’ to clear permissions from all sources.  I choose to ‘Clear Permissions’.

I receive a warning, to make sure I am aware that this is irreversible.  I choose to Delete and close the dialog.  When I refresh, I see the same prompt that a user sharing this workbook would see:

When I choose to ‘Edit Credentials’ I see the same dialog that appeared when I imported the CSV:

If I had chosen to find the URL using a copied link, I would have had confusing results:

This creates a link to the CSV that may be shared:

However, if I enter this into the dialog in my new workbook, I have an issue:

This is not actually a problem with the credentials; it’s a problem with the URL.

Now I have successfully linked to a single CSV file, next time I will use the ‘From SharePoint Folder’ option to combine all the CSVs in the folder.

Come back next time for more ways to use Power Query!