Power Query in Microsoft Excel is a highly efficient tool for combining multiple files from a directory, offering a streamlined approach to data integration. This is particularly useful when dealing with data spread across multiple files with a consistent structure, such as monthly reports or sales data from different regions. Power Query automates the process of importing, transforming, and consolidating this data into a single workbook. This means you can save a considerable amount of time and reduce errors compared to manual methods of data combination. The ability to refresh data automatically when files are updated or new files are added to the directory ensures that your analysis is always based on the latest data without additional effort.

Power Query can also help when your rows exceed Excel limits. For example, you might have an export of sales data that consists of 1.2 million rows (100K for each month). Since Excel cannot handle this, it is useful to have a separate export for each month and to store each monthly file in the same folder. You can then use the multiple files approach to consolidate your files for analysis without exceeding any Excel limits.

While Microsoft makes it 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 wonderful 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.

There are two ways to get a link to the file from SharePoint or OneDrive, either from your directory or from the Excel file.

Step 1: Click the ellipsis button next to the file you want and then select Details (do not use copy link, it will not work).

Step 2: In the details pane, scroll all the way to the bottom and select the copy icon next to the path button.

From Excel, navigate to File > Info and click the Copy path button.

Now that 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.

Next, you'll need to sign in. Choose Organization Account and 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!

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.

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 on your site. We're going to use a text filter to filter 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!

The process for getting files from a OneDrive folder is the same as getting it from a SharePoint folder except for 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).

Now that you know how to get files and folders into Power Query and Power BI it's time to start analyzing your imports.

🚀 Stream your NetSuite saved searches with ExtendInsights.

🚀 Need to connect to data sources beyond NetSuite? Check out ExtendInsights and automate your data exports from your selected data source to Microsoft Excel.