Power Query

Question

I am trying to use a sharepoint document that I upload from excel into a folder. I will need to download and upload a new one everyday because there will be updates. I want to use excel power query to receive the new data from the newly updated excel sharepoint and add the new data. Additionally, I want to remove specific columns from the uploaded sharepoint, and also add my own so I can manually enter data and have that loaded into the overall power query. I want this all on one sheet. Is this possible? And how can I do it? Thank you!

Answer 1

Hi @Lenahan, Lauren,

Thank you for posting your question in the Microsoft Q&A forum.

According to your concern, this is possible in Excel with Power Query. Based on your information, Power Query can help you:

• Connect to SharePoint folder and pick up new/updated files on refresh
• Combine data from multiple daily files into one table
• Remove unwanted columns from the source files and load the final output into one sheet as a table.

However, there is one important note if you want to manually add your own columns. You can’t keep an Excel manual column in sync with a Power Query output since Power Query reloads/replaces the table and your manual edits will be overwritten and get lost on refresh. If you want to add your own columns, you need to keep your manual inputs in a separate Excel table and use Merge to bring them into Power Query.

The previous reply of Q&A Assist does provide a step-by-step guidance to connect SharePoint folder to Excel Power Query so I suggest you can try following these steps. About the issue that Combine disappear, it seems like you have multiple folders on your SharePoint site containing different types of data. Therefore, you first need to select Transform data (which you have done correctly) > in the Power Query editor, scroll down to find the files you want to keep and delete other unnecessary files > now select Combine Files from the Home ribbon to combine the data from all of the remaining files.

If you want more details explanation with more demonstration pictures to follow easier, you can refer to

• Power Query SharePoint folder connector - Power Query | Microsoft Learn
• Combine files overview - Power Query | Microsoft Learn

If you have any questions or need further assistance, please feel free to share them in the comments on this post so I can continue to support you.  

Thank you for your patience and understanding. Looking forward to your response.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it.      

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread. 

Answer 2

When I upload the folder into the power query, I have to press transform data first in order to find the folder snice there are many and it is not listed in the initial pop up screen. Once I do that, the combine option disappears

Answer 3

Yes, this is possible with Power Query using a SharePoint folder connection, plus some simple query design in Excel.

Below is a practical way to set it up.

1. Store the daily file in a dedicated SharePoint folder

• Create a SharePoint document library folder that will hold only these daily Excel files.
• Each daily file should have the same column structure (same column names and types). Power Query matches columns by name, not by order.

2. Connect Excel Power Query to the SharePoint folder In Excel (desktop):
3. Select Data > Get Data > From File > From SharePoint Folder.
4. In the SharePoint Folder dialog, enter the SharePoint site URL (root site URL, not the library URL), then navigate to the library/folder that contains the daily files.
5. Authenticate if prompted.
6. In the Navigator, you’ll see a table listing all files in that folder (and subfolders).
7. Select Combine & Transform Data (recommended) so the data is loaded into Power Query Editor for editing.

This creates a query that automatically combines all files in that SharePoint folder into one table. When a new file is uploaded with the same schema, a refresh will pull it in.

3. Remove unwanted columns in Power Query In the Power Query Editor on the combined table:
4. Select the columns to keep, then on Home select the arrow next to Remove Columns > Remove Other Columns; or
5. Select the columns to remove and choose Remove Columns.

These steps become part of the query and will apply automatically every time new files are added and the query is refreshed.

4. Load the combined data to a worksheet
5. In Power Query Editor, select Home > Close & Load To….
6. Choose Table and select the worksheet where the combined data should appear.

This sheet will always be query-controlled: when refreshed, it will overwrite the query output area with updated data from SharePoint.

5. Add manual-entry columns alongside query data Power Query output itself cannot be manually edited (it is regenerated on refresh), but there are two common patterns to keep everything “on one sheet” from a user perspective:

Option A – Manual columns outside the query table

• Load the query to a table that starts at, for example, column A.
• Use columns to the right (for example, starting at column M) for manual entry.
• Use formulas (e.g., VLOOKUP, XLOOKUP, INDEX/MATCH) or structured references to relate manual data to the query table (e.g., by an ID or date).
• Keep in mind: if the query table grows/shrinks, Excel will adjust the table size, but your manual columns must remain outside the query table boundaries.

Option B – Separate manual table + another query to merge

1. Create a second table on the same sheet (or another sheet) for manual data (e.g., columns: Key, Comment, Status, etc.).
2. Use Data > Get Data > From Table/Range to turn that manual table into a Power Query query.
3. In Power Query, create a new query that merges the combined SharePoint query with the manual-data query on a key column.
4. Load this merged query as the final table to the sheet.

This way, the final table (on one sheet) contains both:

• All combined data from the SharePoint files (with unwanted columns removed), and
• The manual columns from the separate manual table.

To update:

• Upload the new daily Excel file into the same SharePoint folder.
• Update or add rows in the manual table as needed.
• In Excel, select Data > Refresh All to pull in the new file and re-merge with manual data.

6. If the SharePoint folder URL changes If the SharePoint location ever changes but the schema stays the same, a parameter can be used to manage the folder/site URL and avoid refresh errors. A parameter can be created and then used as the data source value, so only the parameter needs to be updated when the location changes.

---

References:

• Import data from a folder with multiple files (Power Query)
• Import data from data sources (Power Query)
• SharePoint folder
• SharePoint and OneDrive for work or school files import
• Create a parameter query (Power Query)