Forum Discussion
Need help combining 4 worksheets into one
I'm new to Power Query. Is it possible to combine 4 worksheets into one based on a single key such as IP Address?
Hi Rnaval ,
You can certainly automate this process to run daily. Since your merges are already functioning correctly, you are well positioned for automation. Power Query is designed to repeat all your steps, such as removing duplicates and merging, each time the query refreshes. Therefore, automation mainly involves setting up a consistent refresh schedule rather than changing your existing setup.
If you are using Microsoft Excel, you can configure the query to refresh automatically when the file is opened, ensuring your master file always reflects the latest data. This solution works well if someone opens the file daily. If you require the process to run independently, you may need to use an external tool, such as a scheduled task or a cloud-based flow, to open and refresh the file at regular intervals.
For a fully automated, hands-off process, Microsoft Power BI is often preferable. Once your query is built, you can publish it and schedule daily refreshes, providing a reliable solution for ongoing operations.
Regardless of the method chosen, ensure that your source files remain consistent in terms of file names, locations, column structures, and the IP Address field format. Your duplicate handling logic in Power Query will continue to operate automatically with each refresh. At this stage, you do not need to modify your query logic simply determine the most suitable way to trigger the daily refresh based on your automation needs.
Thank you.
10 Replies
Rnaval sure you can!
Yes, absolutely — it's not only possible, it's one of the most common and powerful things Power Query does very well.
Since you're new to Power Query, I'll explain the two main ways to "combine" 4 worksheets, and which one you probably want when you mention "based on a single key such as IP Address":
Goal Use this Power Query feature What it does When to choose itStack rows on top of each other (same columns, more records) Append Puts Sheet2 under Sheet1, Sheet3 under that, etc. You just want a big long list of all records Join side-by-side (add columns from other sheets) using IP Address as the matching key Merge Like VLOOKUP / XLOOKUP but for whole tables — brings matching data from other sheets into the main one You want to enrich one main sheet with info from the others using IP Address Most people who say "combine based on a key like IP Address" actually mean Merge (joining horizontally by matching IP).
Quick Recommendation (most likely what you need)
Use Merge if the 4 sheets have different columns but share IP Address as the common identifier.
Example:
- Sheet1 = Main list of IPs + basic info
- Sheet2 = Usage stats by IP
- Sheet3 = Location / Owner by IP
- Sheet4 = Last scan date by IP
→ You want one big table with all columns, matched by IP Address.
Step-by-Step: Merge 4 Sheets by IP Address (Beginner-friendly)
Open Excel → go to the Data tab → Get Data → From Other Sources → Blank Query (or From File → From Workbook if you want to start from one sheet)
In Power Query Editor, create 4 separate queries — one for each sheet:
- Home → New Source → Excel Workbook (or just From File if already open)
- Browse/select your file → choose Sheet1 → Transform Data
- Rename this query to something clear like Sheet1_IPs
- Repeat for Sheet2, Sheet3, Sheet4 → name them Sheet2_Usage, etc.
Tip: Make sure IP Address column is the same data type in all sheets (usually Text). Right-click the column → Change Type → Text.
Decide your main / left table (usually the most complete one — let's say Sheet1_IPs).
With Sheet1_IPs selected, go to Home tab → Merge Queries → Merge Queries as New (safer for beginners)
In the merge dialog:
- Top box = your current query (Sheet1_IPs)
- Bottom box = choose the next sheet (e.g. Sheet2_Usage)
- Click the IP Address column in both tables (they light up green)
- Join Kind = Left Outer (keeps all rows from Sheet1, even if no match in Sheet2)
- OK
A new column appears (usually called Sheet2_Usage) → click the expand icon (two arrows)
- Select only the columns you want from Sheet2
- Uncheck "Use original column name as prefix" (cleaner)
- OK
Repeat steps 4–6 for the other two sheets:
- Merge the result with Sheet3_...
- Then merge again with Sheet4_...
When finished → Home → Close & Load → choose To new worksheet
You now have one combined table where every IP from the main sheet has extra columns pulled from the other sheets (null if no match).
If you actually want to stack them vertically (Append)
Only do this if all 4 sheets have almost identical columns (same names, same order is ideal).
- Load all 4 sheets as separate queries
- Select the first one → Home → Append Queries → Append Queries as New
- Choose the other 3 one by one (or select all three at once)
- Close & Load
But since you mentioned "based on a single key such as IP Address", Merge is almost certainly what you're after.
I hope this helps. if so please mark it as a solution. kudos are welcome!Thank you so much, you have explained this so well! And yes it's the Merge option that I'm going to use. I'll try it this weekend and let you know how it goes.
I have 4 files - Master, Operating System, Scan Date and Logon ID. The master is my main file then I want to bring in a single column from each of the other files. I should end up with three columns added to the end of my Master file. I followed the steps above. However, I ended up with only a single additional column from the last sheet that I merged. The first two sheets' columns got overwritten I think. Somehow there is a step after merging the first sheet that I need in order to end up with 3 new columns.
Actually, I do see the third Merge query and I was able to remove duplicates from the 3 files that I merged prior to the close and load in order to end up with the correct total count. Question now is that is there a way to automate this power query so it happens daily?
Hi Rnaval ,
You can certainly automate this process to run daily. Since your merges are already functioning correctly, you are well positioned for automation. Power Query is designed to repeat all your steps, such as removing duplicates and merging, each time the query refreshes. Therefore, automation mainly involves setting up a consistent refresh schedule rather than changing your existing setup.
If you are using Microsoft Excel, you can configure the query to refresh automatically when the file is opened, ensuring your master file always reflects the latest data. This solution works well if someone opens the file daily. If you require the process to run independently, you may need to use an external tool, such as a scheduled task or a cloud-based flow, to open and refresh the file at regular intervals.
For a fully automated, hands-off process, Microsoft Power BI is often preferable. Once your query is built, you can publish it and schedule daily refreshes, providing a reliable solution for ongoing operations.
Regardless of the method chosen, ensure that your source files remain consistent in terms of file names, locations, column structures, and the IP Address field format. Your duplicate handling logic in Power Query will continue to operate automatically with each refresh. At this stage, you do not need to modify your query logic simply determine the most suitable way to trigger the daily refresh based on your automation needs.
Thank you.
Step 1) Merge the queries
In Power Query:
-
Select your main query
-
Choose Home → Merge Queries
-
Select the second worksheet
-
Click IP Address in both tables
-
Choose the join type
Usually you want:
-
Left Outer → keep all rows from the first sheet, bring matching data from the second
Repeat for the 3rd and 4th worksheets.
Step 2) Expand the merged tables
After each merge, Power Query will create a nested table column.
Click the expand icon and select the columns you want to bring in.
Step 3) Load the final result
Once all merges are done, click Close & Apply (Power BI) or Close & Load (Excel).
-
