Power Query is a lifesaver when you’re working with tons of Excel files — like monthly sales reports or regional exports — especially when each file follows the same format. Instead of copy-pasting across files, Power Query lets you combine everything from a folder into one dynamic table. Better yet, it updates automatically as new files are added. 🚀

📉 Got more than 1 million rows? No problem. Store each month in a separate file and let Power Query stitch them together — no Excel meltdown required.

But here’s the catch: Microsoft makes it easy to pull from your desktop… not so much from OneDrive or SharePoint, especially on a Mac where there’s no “File > Info > Copy Path” option.

This guide shows you how to get the real file path — not those tokenized sharing links — so you can unlock the full power of Power Query from OneDrive or SharePoint, even on a Mac.

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 multiple ways to get a link to your file. The method you choose will depend on your operating system (it's easy for Windows users). If you are on a Mac or need to get your file location from online Excel it's a little more difficuly but we've gor you covered.

This is the easiest and fastest way to get your file location but it only works on Excel for Windows. It will not work on Mac or online Excel.
​

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

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.

While you can't get a direct link from OneDrive, OneDrive (for Business) files are stored in SharePoint behind the scenes. The steps below will help you get to the SharePoint view of your OneDrive files.

Step 1: Open OneDrive and right click on the file you need to get the path for. Select Open File Location. This will open your OneDrive in SharePoint view.
​

Step 2: Now that you are in SharePoint view, click the ellipses button next to the file you want and then select Details (do not use copy link, it will not work).
​

Step 3: In the details pane, scroll all the way to the bottom and select the copy icon next to the 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 automate the process. Learn how ExtendInsights can automate your reporting.