Real-world reports pull data from SQL databases, Excel files, CSVs, and more — all at once. Learn how to connect Power BI to multiple sources and use Power Query's Append and Merge operations to combine them correctly, with the right mental model for knowing which approach to use when.

Real-world data never lives in one place. Your sales figures are in a SQL database, your targets are in an Excel spreadsheet maintained by the finance team, your customer demographics are in a CSV export from your CRM, and your regional territory mappings are sitting in a SharePoint list. If you've tried to build a meaningful Power BI report and hit the wall of "I need data from three different places and I don't know how to combine it," this lesson is exactly where you need to be.
By the end of this lesson, you'll know how to connect Power BI to multiple data sources in a single report, understand the fundamental difference between Append and Merge operations in Power Query, and know when to use each one. More importantly, you'll understand why each approach works the way it does — so you're not guessing when you encounter a new scenario in the wild. This isn't about clicking buttons; it's about developing the mental model that makes you a more capable data professional.
What you'll learn:
You should already be comfortable opening Power BI Desktop and navigating to Power Query Editor. If you've built at least one basic report and loaded data from a single source, you're ready. If you need a primer on connecting to individual sources like Excel, CSV, or SQL Server, start with Connecting to Data Sources in Power BI Desktop: Excel, CSV, SQL, and Web — A Complete Beginner's Guide before continuing here.
It's also helpful to have some familiarity with Power Query's transformation interface. If you've never used it, Importing and Transforming Your First Dataset in Power Query: A Step-by-Step Beginner Walkthrough will get you up to speed quickly.
Let's ground this in something real. You're a data analyst at a mid-sized consumer goods company. You need to build a sales performance dashboard that answers these questions:
Here's your data reality:
| Source | Format | Contents |
|---|---|---|
| SQL Server | Database table | Actual sales transactions (Jan–Mar) |
| Excel file | .xlsx |
Sales targets by rep and region |
| CSV export | .csv |
Sales transactions from a legacy system (Q4 of last year, for YoY comparison) |
| Another CSV | .csv |
Territory-to-region mapping (rep → territory → region) |
This is a classic multi-source scenario. The transactions need to be combined across time periods (Append), and the territory mapping needs to be joined to the transactions (Merge). Let's build it.
Power BI Desktop lets you connect to as many sources as you need. Each connection creates a separate query in Power Query Editor. There's no practical limit on the number of sources in a single .pbix file, though performance is worth thinking about once you get into the dozens.
Open Power BI Desktop and click Get Data from the Home ribbon. Choose SQL Server, enter your server and database name, and select the sales transactions table. For this lesson, let's call it Sales_Q1_2024.
Once it loads in Power Query Editor, you'll see columns like:
TransactionID | SalesRepID | Date | ProductCategory | Region | Amount
Don't close Power Query yet. We'll add more sources from within the editor.
Here's something many beginners miss: you don't have to go back to Power BI Desktop's main view to add new data sources. Inside Power Query Editor, go to Home → New Source in the ribbon. This lets you add additional queries without leaving the editor.
Add your Excel file (the sales targets) by selecting Excel Workbook, navigating to the file, and selecting the appropriate sheet or table. Let's call this query Sales_Targets. Its columns might look like:
SalesRepID | RepName | Region | Q1_Target
Add your first CSV (legacy system Q4 data) via New Source → Text/CSV. Name this Sales_Q4_2023. Its columns:
TransactionID | RepID | TransactionDate | Category | Territory | SaleAmount
And the territory mapping CSV, named Territory_Map:
Territory | Region | RegionManager
Note
Each query you add appears in the Queries pane on the left side of Power Query Editor. At this point you have four separate queries, none of them connected to each other. That's perfectly fine — this is the starting state before you combine them.
Before touching any buttons, you need to understand what these two operations actually do. Getting this wrong is the single most common mistake practitioners make when combining data in Power Query.
Append stacks rows vertically. If you have 1,000 rows of Q1 sales and 800 rows of Q4 sales, Append produces a single table with (up to) 1,800 rows. Think of it like UNION in SQL — you're adding more rows of the same type of data.
Merge joins tables horizontally. If you have a sales table with a Territory column and a separate lookup table that maps territories to regions, Merge brings in the region information by matching on that shared key. Think of it like JOIN in SQL — you're adding more columns of related data.
A simple test: ask yourself, "Am I combining two tables that have the same shape (same kinds of columns)?" If yes, Append. "Am I looking up information from one table to enrich another?" If yes, Merge.
Key insight
The confusion between Append and Merge almost always comes from not asking this question first. Take 10 seconds before you proceed and explicitly answer: same shape (Append) or lookup enrichment (Merge)?
You have Sales_Q1_2024 from SQL Server and Sales_Q4_2023 from a CSV export. You want a single transactions table covering both periods.
The problem? Their column names don't match. This is extremely common with legacy systems.
Sales_Q1_2024: TransactionID | SalesRepID | Date | ProductCategory | Region | Amount
Sales_Q4_2023: TransactionID | RepID | TransactionDate | Category | Territory | SaleAmount
Before appending, rename columns in Sales_Q4_2023 so they match Sales_Q1_2024. In Power Query Editor, select the Sales_Q4_2023 query in the left pane.
Rename each column by double-clicking its header:
RepID → SalesRepIDTransactionDate → DateCategory → ProductCategorySaleAmount → AmountYou'll also notice the Q4 data has Territory instead of Region. Leave this for now — we'll resolve it after the Merge step later. For consistency, rename Territory → Region so the Append works cleanly, even if the values in that column are territory codes rather than region names. You'll fix the actual values later.
Warning
If you Append before standardizing column names, Power Query will create separate columns for each distinct name rather than combining the values. You'll end up with Amount and SaleAmount as separate columns, with nulls filling in wherever a row didn't have that column. This is a subtle bug that's easy to miss until you're wondering why your totals are half what they should be.
Check that the Date column is typed as Date in both queries (not Text or DateTime), that Amount is Decimal Number in both, and that SalesRepID is a consistent type (probably Whole Number or Text).
To change a data type, click the type icon to the left of the column name in the header row and select the correct type.
Now select the Sales_Q1_2024 query in the left pane. This will be your primary query — the one that grows when you add rows.
Go to Home → Append Queries in the ribbon. You'll see two options:
For this scenario, choose Append Queries as New. This is safer during development because it preserves your original queries and lets you debug issues without having to untangle a modified query.
In the dialog that appears:
Sales_Q1_2024Sales_Q4_2023Click OK. A new query appears, which you can name All_Sales_Transactions. Preview the data — you should see rows from both time periods combined into one table.
Tip
If you need to Append more than two tables — say, monthly CSV files from twelve months of data — select Three or more tables in the Append dialog. You can add as many tables as you need. Even better, consider using the Folder connector to load and append all files from a directory automatically, which scales much better than manually adding each file.
Check the row count in the status bar at the bottom of Power Query Editor. It should approximately equal the sum of the two source tables' row counts. If it's significantly lower, you may have accidentally filtered rows somewhere. If it's higher than expected, you may have accidentally introduced duplicates.
Add a quick check: use Transform → Statistics → Count Rows to confirm, or simply look at the status bar which shows row count when you have no columns selected.
Now you have All_Sales_Transactions with a Region column that actually contains territory codes (like NW-01, SE-07) rather than proper region names. Your Territory_Map table has the lookup you need:
Territory_Map:
Territory | Region | RegionManager
NW-01 | Northwest | Sarah Chen
SE-07 | Southeast | Marcus Webb
...
This is a textbook Merge scenario. You're enriching your transactions with regional labels.
Before merging, you need to choose a join type. Power Query supports six, mirroring standard SQL join types:
| Join Type | What it does |
|---|---|
| Left Outer | All rows from the left table; matching rows from right |
| Right Outer | All rows from the right table; matching rows from left |
| Full Outer | All rows from both tables |
| Inner | Only rows that match in both tables |
| Left Anti | Only rows from left that do NOT match anything in right |
| Right Anti | Only rows from right that do NOT match anything in left |
For a territory lookup, you almost always want Left Outer. You want to keep all your transactions (the left table) and bring in territory information where it exists. If some transactions have a territory code that doesn't appear in your mapping table, they'll get null values for the extra columns — which is usually the right behavior because it lets you spot data quality issues without silently dropping rows.
Warning
Using an Inner join for a lookup is dangerous if your mapping table is incomplete. You'll silently lose transactions whose territory codes aren't in the mapping, which will cause your totals to be wrong. Always use Left Outer first, then investigate any nulls in the joined columns to find gaps in your mapping data.
Select the All_Sales_Transactions query. Go to Home → Merge Queries as New (again, using "as New" is safer during development).
In the Merge dialog:
All_Sales_TransactionsRegion column header in the top table preview to select it as the join key — it will highlight in greenTerritory_MapTerritory column header in the lower table preview — it also highlightsYou'll see a new query with a column at the far right called Territory_Map — it looks like a column containing the word "Table" in every row. This is Power Query's way of representing the matched data before you expand it.
Click the expansion icon (two arrows pointing outward) in the Territory_Map column header. A dropdown appears listing all columns from the mapped table: Territory, Region, RegionManager.
Since you already have Region in your transactions table (with the territory codes), you don't want to bring in Territory again — it's redundant. And the values in the transactions Region column are what you're replacing conceptually with the proper region names.
Uncheck Territory (redundant), keep Region and RegionManager. Also uncheck Use original column name as prefix — otherwise your new columns will be named Territory_Map.Region and Territory_Map.RegionManager, which is ugly.
Click OK. You now have proper region names and manager information joined to every transaction.
Your transactions table now has two Region-related columns: the original one with territory codes (now confusingly named Region) and the newly expanded one (also named Region). Rename the original to TerritoryCode and the new one to Region. Then decide whether you want to keep TerritoryCode at all — often you don't, and you can remove it.
Let's also bring in the Sales_Targets table. This one is different from the transactions — it's not being appended or merged into your main transactions table. It's a separate table that will sit alongside All_Sales_Transactions in your data model, related by SalesRepID.
You don't need to Append or Merge it into your main transactions table. Instead, let it be its own query that loads as its own table in the model. Then, back in Power BI's Report view, you'll create a relationship between the two tables on SalesRepID.
This is an important design principle that many practitioners miss: not all data combination happens in Power Query. Some combination happens through relationships in the data model. Use Power Query's Append/Merge when you need to physically combine data into a single table. Use data model relationships when two tables are genuinely separate entities that relate to each other.
If you want to go deeper on building those relationships correctly, Power BI Data Modeling: Master Star Schema Design and Relationship Optimization covers exactly that.
Key insight
The cleaner pattern is: use Append to unify data that represents the same kind of thing (transactions from different periods), use Merge to enrich a table with lookup data, and use model relationships to connect distinct entity tables (facts to dimensions). Trying to collapse everything into one giant table with Merge is a common trap — it bloats your dataset and makes your model harder to maintain.
Every action you take in Power Query's visual interface generates M code. Understanding it helps you debug problems and make changes more efficiently. Click Advanced Editor in the Home ribbon to see the full M script for any query.
Here's what a typical Append looks like in M:
let
Source = Table.Combine({Sales_Q1_2024, Sales_Q4_2023})
in
Source
Table.Combine is the function behind Append. It takes a list of tables and stacks them. If column names don't match across tables, it creates separate columns for each distinct name — which is why you must standardize names first.
A Merge (Left Outer join) looks like this:
let
Source = Table.NestedJoin(
All_Sales_Transactions,
{"Region"},
Territory_Map,
{"Territory"},
"Territory_Map",
JoinKind.LeftOuter
),
#"Expanded Territory_Map" = Table.ExpandTableColumn(
Source,
"Territory_Map",
{"Region", "RegionManager"},
{"Region", "RegionManager"}
)
in
#"Expanded Territory_Map"
Table.NestedJoin performs the join and creates the nested table column. Table.ExpandTableColumn then flattens it into regular columns. The fourth argument to Table.ExpandTableColumn lets you rename the expanded columns inline — handy for avoiding name conflicts.
Tip
If you're merging on multiple columns (a composite key), pass them as a list in the second and fourth arguments: {"SalesRepID", "ProductCategory"} for both tables. Power Query will match rows where both columns agree simultaneously.
Append and Merge operations in Power Query run during data refresh. For Import mode (the default), this happens once when you refresh, and the results are stored in the model. For DirectQuery mode, it's more complex. If you're using DirectQuery or Live Connection, Power Query transformations may be folded back to the source (if the source supports it) or executed in-memory, which has performance implications.
For large datasets:
Query folding is your best friend. When you Merge two SQL Server tables, Power Query can often fold the join back to SQL, running it natively on the server as a single efficient query. Check whether folding is happening by right-clicking a step in the Applied Steps pane — if "View Native Query" is available and not greyed out, the step is folding.
Avoid Merging across different source types where possible. Merging a SQL Server table with a CSV file cannot fold to either source — Power Query has to load both into memory and join them locally. This is fine at small scale but can be slow with millions of rows.
Filter before you Merge or Append. If you only need Q1 data for your analysis, filter the date range before appending, not after. This reduces the volume of data being processed.
For a deeper look at how to squeeze performance out of your queries, Mastering Power Query in Power BI: Transforming, Cleaning, and Shaping Data Before It Hits Your Model has an extensive section on folding and optimization.
Build the following in a fresh Power BI Desktop file. Download or create sample files if you don't have your own data:
Setup: Create three CSV files manually:
q1_sales.csv:
TransactionID,SalesRepID,Date,ProductCategory,Territory,Amount
1001,201,2024-01-05,Electronics,NW-01,4500
1002,202,2024-01-07,Appliances,SE-07,2300
1003,201,2024-02-14,Electronics,NW-01,6100
1004,203,2024-03-22,Furniture,MW-03,1800
q2_sales.csv:
TransactionID,RepID,SaleDate,Category,TerritoryCode,SaleAmount
2001,201,2024-04-03,Electronics,NW-01,5200
2002,202,2024-04-19,Appliances,SE-07,3100
2003,204,2024-05-08,Furniture,MW-03,2200
2004,201,2024-06-30,Electronics,NW-01,4900
territory_map.csv:
Territory,Region,RegionManager
NW-01,Northwest,Sarah Chen
SE-07,Southeast,Marcus Webb
MW-03,Midwest,James Okafor
Your tasks:
Load all three CSV files into Power Query Editor as separate queries. Name them Q1_Sales, Q2_Sales, and Territory_Map.
In Q2_Sales, rename columns to match Q1_Sales: RepID → SalesRepID, SaleDate → Date, Category → ProductCategory, TerritoryCode → Territory, SaleAmount → Amount.
Ensure Date columns in both sales tables are typed as Date and Amount columns are typed as Decimal Number.
Use Append Queries as New to combine Q1_Sales and Q2_Sales into a new query called All_Sales.
Use Merge Queries as New on All_Sales, joining to Territory_Map on the Territory column using a Left Outer join. Expand the merged columns to bring in Region and RegionManager. Rename this query Sales_Enriched.
Remove the Territory column from Sales_Enriched (you now have the proper Region name).
Load Sales_Enriched and Territory_Map to the Power BI model. Close and Apply.
In the Report view, create a simple bar chart showing total Amount by Region to verify your data combined correctly.
Expected result: 8 rows total in Sales_Enriched, with Northwest showing 3 transactions, Southeast showing 2, and Midwest showing 2 (plus one row for rep 204 in Midwest).
Mistake: Appending creates extra columns with nulls
Cause: Column names don't match between the two tables being appended. Power Query treats Amount and SaleAmount as different columns.
Fix: Standardize column names in each source query before performing the Append. Check names carefully — even a trailing space will cause a mismatch.
Mistake: Merge returns all nulls in the joined columns
Cause: The join key values don't actually match. This is often a data type mismatch (one table has Territory as Text, the other has it as a number) or a whitespace issue (extra spaces in one source).
Fix: First, verify data types are identical on both join key columns. Then use Text.Trim() on both sides to remove leading/trailing spaces. Add a step like:
#"Trimmed Territory" = Table.TransformColumns(
PreviousStep,
{{"Territory", Text.Trim, type text}}
)
Apply the same transformation to Territory_Map before merging.
Mistake: Using Inner join and silently losing rows
Cause: Choosing Inner join when the mapping table is incomplete. Transactions with unmapped territories are dropped.
Fix: Always start with Left Outer join. After expanding, filter for nulls in the joined column to identify what's missing, then fix the mapping table.
Mistake: Merging instead of using model relationships
Cause: Wanting to see RepName alongside transactions, so you Merge the targets table into transactions just to get the name.
Fix: Keep them as separate tables. Create a relationship in the model on SalesRepID. Power BI will handle the lookup at query time. This keeps your model normalized and your dataset smaller. See Understanding Power BI Relationships: How to Connect Tables, Set Cardinality, and Avoid Common Modeling Mistakes for the right approach.
Mistake: Query reference chains getting out of control
Cause: Creating Append and Merge queries that reference other Append and Merge queries in a long chain, making it impossible to debug where something went wrong.
Fix: Keep your transformation logic in the source queries (renaming, typing, filtering), and keep your Append/Merge queries as clean and simple as possible. Each query should do one clear thing.
Mistake: Duplicate rows after Append
Cause: The source data already contains overlapping records (e.g., some transactions exist in both the SQL database and the CSV export).
Fix: After appending, use Remove Duplicates on the TransactionID column (or whatever your unique identifier is) to deduplicate. Be aware of which record "wins" — Power Query keeps the first occurrence, which will be from whichever source appears first in the Append.
Append and Merge in Power Query are powerful, but they're not always the best solution:
If you're combining dozens of files from a folder, use the Folder connector instead of manually appending each file. It automatically combines all files matching a pattern.
If your combination logic is complex (multi-step transformations, conditional joins, running totals), consider whether a SQL view or stored procedure in your database would be more appropriate. Bring already-combined, pre-processed data into Power BI rather than replicating complex logic in M.
If you're working at enterprise scale and need reusable, centralized transformation logic, consider Mastering Power BI Dataflows: Building Reusable ETL Pipelines in Power BI Service. Dataflows let you define your Append/Merge logic once and reuse the resulting tables across multiple reports.
If your data sources change frequently, parameterize your source connections using Power Query parameters rather than hardcoding file paths or server names. This makes your queries portable and easier to maintain.
You now have the full picture of how to combine data from multiple sources in Power Query. The key principles to carry forward:
Connect first, combine second. Add all your sources as separate queries before you start combining them. Keep the source queries clean and simple.
Standardize before Append. Column names and data types must match across all tables you're stacking. Do this in the source queries, not in the Append query itself.
Choose your join type deliberately. Default to Left Outer for lookups, understand what you're giving up with Inner, and use Anti joins when you're specifically looking for unmatched records.
Know when to use model relationships instead. Not every data connection needs to happen in Power Query. Dimension tables that relate to fact tables usually belong in the model, not merged into a single flat table.
Watch for query folding. When both sources are from the same SQL database, let the join happen in the database — it's always faster than in-memory processing.
With your data properly combined and shaped, the next natural step is to build your data model correctly so that the relationships between your tables work the way you expect. If you haven't yet, Understanding Power BI Relationships: How to Connect Tables, Set Cardinality, and Avoid Common Modeling Mistakes will walk you through exactly that.
Once your model is solid, you're ready to start writing DAX measures to calculate things like variance to target, year-over-year comparisons, and running totals. Introduction to DAX: Writing Your First Calculated Columns and Measures in Power BI is your next stop on the learning path.
Getting Started with Power BI