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

Welcome to our Power Query blog.  This week, I look at how to find the URL I need to access a CSV file on SharePoint.

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!

Last time, I worked out how to access this data from Power Query.  I realised that if I used the drive mapped to the SharePoint site, and then shared this workbook with another user in my company, they would need to change the path:

To create a solution that can be shared more easily, I need to use the ‘From Web’ option in the ‘From Other Sources’ section of 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:

This week, I will begin by showing a way that will successfully find this.  Then, I will show what happens if I try a different way.  Let’s go back to SharePoint, and open the CSV file in Excel Online:

When I access the File tab, the Info allows me to ‘Open in Desktop App’:

A confirmation dialog is displayed:

In the Desktop App, I may now access the File tab.  In the Info section, I see the path, and I may use the ‘Copy Path’ option to save this information.

I may now use this information in the dialog in my blank Excel workbook, using the shortcut CTRL + V:

Since this is the first time I am accessing the SharePoint website, I am prompted with further dialogs:

There are a variety of ways to access the website to import data.  Typically in these blogs, I use websites that may be accessed by any user.  Therefore, I have used Anonymous credentials.  Since this example uses SharePoint data, I will be using my ‘Organizational account’.   Let’s first look at the other options:

Some webpages require Windows credentials to access them.   If the website requires this authentication, then the ‘Select which level to apply these settings to’ will determine at which level to apply the check.  On some sites, subfolders may have different access credentials.  The ‘Use my current credentials’ and ‘User alternate credentials’ will allow the use of datasets accessible to different users in the same workbook. 

If a webpage needs a username and password to access it, data may be imported using the Basic credentials.

Some web resources require an API (Application Programming Interface) key to be entered, which may be entered on the ‘Web API’ tab.

Finally, the information we need for this example will be entered on the ‘Organizational account’ tab.  The user is prompted to ‘Sign in’ to SharePoint.  

Next time, I will sign in and try the URL that I have copied.

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