While Microsoft makes it real easy to get data into Power Query or Power BI from a file on your local desktop (Get Data > Excel workbook) they don't do a great job of explaining how to get that file if it's stored on OneDrive for Business or SharePoint. This article is meant to demystify the process!
There are two parts to this article, getting a single file or getting multiple files and combining them (a great use case would be consolidating monthly sales data where each file had a particular month). Use the links below to get right to your use case.
Getting Data from a Single File on SharePoint or OneDrive
There are two ways to get a link to the file from SharePoint or OneDrive, either from your directory or from with the Excel file.
Get a link directly from SharePoint or OneDrive
Click the ellipsis button next to the file you want and then select Details (do not use copy link, it will not work).
In the details pane, scroll all the way to the bottom and select the copy icon next to the path button.
Get link directly from your Excel file
From Excel, navigate to File > Info and click the 'Copy path' button.
Importing your file into Power Query or Power BI
Now what you have your link it's time to bring in your data. The process is the same for Power Query in Excel and Power BI.
Use Get Data from the Excel file or Power BI file you want to bring the data into. Note, do not choose Excel Workbook (it won't work).
Choose Get Data > Web
Paste in the URL for the file that was captured from the File > Get Info step.
If you used the copy path option to get a direct link to the file from OneDrive/SharePoint you can use the URL as is.
If you copied your URL from directly from Excel then here is the trick - you must delete the ?web=1 from the end of your URL.
Next, you'll need to sign in. Choose Organization Account and the select 'Sign in' and enter your Microsoft 365 credentials. Once entered, click Connect (you're almost done).
On this final screen you'll see all of the tables and worksheets inside your workbook. As a general rule you should import tables, not worksheets. Selecting tables will ensure that your data has headers. Once you've selected your table(s) click 'Transform Data' and get to work!
Getting Data from a Folder (multiple files)
A typical reason for getting data from a folder is to combine data. For example, you may have a separate Excel workbook for each month with general ledger or sales data. This might be due to file size constraints or maybe you're just super organized!
Getting data from a folder is a bit different than getting data from a single file, and it even varies based on where it is, OneDrive or SharePoint. Don't worry though, we've got you covered.
Getting Data from a SharePoint Folder
Microsoft makes getting files from folders a bit easier with their SharePoint Connector. The first thing you'll need to do is to get the URL of your SharePoint Library. Note this will be something like https://yourdomain.sharepoint.com/sites/yourlibrary.
Now, from Power BI (or Power Query in Excel) select Get data and choose SharePoint folder and then click Connect.
Next, paste the URL captured in the first step and click OK. The yellow highlight below is from the (i) icon.
Now you might need to wait a minute. Power Query is now getting every file and folder in your site. We're going to use a text filter to filter to the folder that holds the sales data. To make your job easier move the folder path column over to the left and expand it as far as you can.
The next step is to find the folder that contains the files you want to combine. We'll do this using a text filter. Below you can see that my folder contains "Sales Orders".
So let's add a text filter by selecting the drop down at the top right of the folder path and then choosing text filter. Next, under 'keep rows' choose contains and then enter 'Sales Orders' as the criteria. and press OK.
Now you should see a list of just the files you want to combine (yay for making it this far). Double click on the double arrows at the top of the Content column to get the data from the files in your desired folder.
Finally, choose the table/sheet that you want and when you click OK Power Query will grab that same table/sheet from each file in the folder!
Note that in order to combine files Power Query does require that the table or sheet names be identical in each of your files.. Don't worry if this is not the case for you. There's a way around this (isn't there always) by using the sheet index. Check out this article and video from Reza Rad, a former Microsoft Regional Director.
Getting Data from a OneDrive Folder
The process for getting files from a OneDrive folder is the same as getting it from a SharePoint folder with the exception of the URL.
Navigate to your OneDrive account in your browser and then to the folder you want to import and copy the URL up to (but not including) _layouts.
Now that you have the URL the steps are identical to that of getting data from a SharePoint folder, including Get Data > From SharePoint folder (yup, even when you're using OneDrive).
Put it all together with CloudExtend Excel Analytics for NetSuite
Now that you know how to get files and folders into Power Query and Power BI it's time to start analyzing your NetSuite data. Get started by streaming your saved searches into Excel which, as demonstrated, is a great data source for Power BI or Power Query.