Import data from OneDrive and SharePoint to Power Query / BI

If you or your company store data in the OneDrive cloud or in a SharePoint company portal, connecting directly to it using Power Query in Excel or from Power BI can be surprisingly challenging.

When I once faced a similar issue, I was surprised to find that there are no “legal” ways to solve it. For some reason, the list of available data sources in Excel and even in Power BI (where the set of connectors is traditionally wider) for some reason does not include the ability to connect to OneDrive files and folders.

So all the options offered below are, to one degree or another, “crutches” that require a small but manual “finishing with a file”. But these crutches have a big plus – they work 🙂

What’s the problem?

A short introduction for those who spent the last 20 years in a coma not in the subject.

OneDrive is a cloud storage service from Microsoft that comes in several flavors:

• OneDrive Personal – for ordinary (non-corporate) users. They give you 5GB for free + extra space for a small monthly fee.
• OneDrive for Business – an option for corporate users and Office 365 subscribers with a much larger available volume (from 1TB or more) and additional features like version storage, etc.

A special case of OneDrive for Business is storing data on a SharePoint corporate portal – in this scenario, OneDrive is, in fact, one of the libraries of SharePoint’a.

Files can be accessed either through the web interface (https://onedrive.live.com site or corporate SharePoint site) or by synchronizing selected folders with your PC:

Usually these folders are stored in the user profile on drive C – the path to them looks something like C: UsersUsernameOneDrive). A special program monitors the relevance of files and synchronization of all changes – АOneDrive gent (blue or gray cloud in the lower right corner of the screen):

And now the main thing.

If we need to load data from OneDrive to Excel (via Power Query) or to Power BI, then of course we can specify local files and folders to be synchronized as a source in the usual way via Get data – From file – From book / From folder (Get Data — From file — From workbook / Folder)But it will not be a direct link to the OneDrive cloud.

That is, in the future, when changing, for example, files in the cloud by other users, we need to sync first (this happens for a long time and is not always convenient) and only then update our query Power Query or Model in Power BI.

Naturally, the question arises: how to import data from OneDrive/SharePoint directly so that the data is loaded directly from the cloud?

Option 1: Connect to a book from OneDrive for Business or SharePoint

1. We open the book in our Excel – a local copy from the synchronized OneDrive folder as a regular file. Or open the site first in Excel Online, and then click on the button Open in Excel (Open in Excel).
2. Go to File – Details (File — Info)
3. Copy the cloud path to the book with the button copy path (Copy Path) in the title:

   

4. In another Excel file or in Power BI, where you want to fill in the data, select the commands Get data – From the Internet (Get Data — From web) and paste the copied path into the address field.
5. Delete at the end of the path ?web=1 and click on OK:

   

6. In the window that appears, select the authorization method Organization account (Organization Account) and click on the button Sign In (Log in):

   

   Enter our working login-password or select a corporate account from the list that appears. If you do everything right, then the inscription Sign In should change to Sign in as a different user (Log in with other user account).
   

7. Click on the button connection (Connect).
   

Then everything is the same as with the usual import of a book – we select the necessary sheets, smart tables for import, etc.

Option 2: Connect to a file from OneDrive Personal

To connect to a book in a personal (non-corporate) OneDrive cloud, the approach will be different:

1. We open the contents of the desired folder on the OneDrive website and find the imported file.
2. Right click on it and select command Introduction (Embed) or select the file and select a similar command in the top menu:

   

3. In the panel that appears on the right, click the button Create and copy the generated code:

   

4.  Paste the copied code into Notepad and “finish with a file”:
   
   • Remove everything except the link in quotes
     
   • Delete the block cid=XXXXXXXXXXXX&
   • Replaceable word embed on download
   As a result, the source code should look like this:

   

5. Then everything is the same as in the previous method. In another Excel file or in Power BI, where you want to fill in the data, select the commands Get data – From the Internet (Get Data — From web), paste the edited path into the address field and click OK.
6. When the authorization window appears, select the option Windows and, if necessary, enter the login password from OneDrive.
   

Option 3: Import the contents of an entire folder from OneDrive for Business

If you need to fill in Power Query or Power BI the contents of not one file, but an entire folder at once (for example, with reports), then the approach will be a little simpler:

1. In Explorer, right-click on the local synchronized folder of interest to us in OneDrive and select View on site (View online).
2. In the address bar of the browser, copy the initial part of the address – up to the word / _layouts:

   

3. In the Excel workbook where you want to load the data or in the Power BI Desktop report, select the commands Get Data – From File – From SharePoint Folder (Get Data — From file — From SharePoint folder):

   

   Then paste the copied path fragment into the address field and click OK:

   

   If an authorization window appears, then select the type Microsoft account (Microsoft Account), click on the button Sign In (Log in), and then, after a successful login, on the button connection (Connect):

   

4. After that, all files from SharePoint are requested and downloaded and a preview window appears, where you can safely click on Convert Data (Transform Data).
   
5. Further editing of the list of all files and their merging takes place already in Power Query or in Power BI in the standard way. To narrow the search circle only to the folder we need, you can use the filter by column Folder Path (1) and then expand the entire contents of the found files using the button in the column Content (2):

   

• Assembling tables from different files using Power Query
• What is Power Query, Power Pivot, Power BI and how they can help you
• Collecting data from all sheets of the book into one table