How to merge data in Power BI, Power Query and Tableau
By the Analistable team · Updated · 12 min read
In Power Query (Excel and Power BI), Merge Queries joins two tables on a key and Append Queries stacks them. In Power BI, you can also leave tables separate and connect them with relationships in the model view. In Tableau, use relationships (the default), joins inside a logical table, or blends for separate data sources.
Power Query: merge vs append
| Operation | What it does | SQL equivalent |
|---|---|---|
| Merge Queries | Adds columns from another table, matched on a key | JOIN |
| Append Queries | Adds rows from another table, matched by column name | UNION ALL |
| Combine Files (From Folder) | Appends every file in a folder | UNION ALL over files |
| Group By | Summarises rows | GROUP BY |
Guides: Merge Queries and its six join kinds, Append Queries step by step, combining files from a folder and fuzzy merge.
Power BI: merge in Power Query or relate in the model?
Merging produces one wide table. Relationships keep tables separate (for example a Sales fact table and a Customers dimension) and let visuals filter across them. Relationships usually give a smaller, faster model and are the recommended default for star schemas. Merge when you need the combined columns as one table, for example to clean or export it. See merging tables in Power BI.
Tableau: relationships, joins and blends
- Relationships (the noodles between logical tables) are the default since Tableau 2020.2. Tableau chooses the join type per visualisation, so measures aren't duplicated.
- Joins happen inside a logical table (double-click it to open the physical layer). They create one flat table and can duplicate rows when keys repeat.
- Blends combine separate data sources at the level of a worksheet, linked on common dimensions. Use them when data can't be joined at the source.
- Unions stack tables with the same structure, like Append in Power Query.
Which tool should you use?
| Situation | Good choice |
|---|---|
| Recurring Excel reports from several files | Power Query in Excel |
| Dashboards shared across a company on Microsoft 365 | Power BI |
| Visual analysis with many data sources | Tableau |
| Quick answers from a few sheets, no model to build | Analistable |
Worked example: one question, three tools
Question: total sales by product category, where Sales has ProductID and Amount, and Products has ProductID and Category.
| Tool | Approach | Steps |
|---|---|---|
| Excel Power Query | Merge Sales with Products, expand Category, then pivot | Merge Queries → expand → Close & Load to PivotTable |
| Power BI | Relationship Sales[ProductID] → Products[ProductID] | Model view drag, then a visual with Category and Sum of Amount |
| Tableau | Relationship between the two logical tables | Drag Products onto the canvas next to Sales, then build the view |
All three return the same totals. The difference is where the combination lives: a merged table (Power Query), a model relationship (Power BI), or a relationship evaluated per view (Tableau).
Common pitfalls
- Duplicate keys in the lookup table multiply rows in a merge and break one-to-many relationships. Remove duplicates in the dimension table first.
- Different data types on the two keys (text “1042” vs number 1042) produce no matches. Set both to the same type in Power Query.
- Case differences — Power Query's exact merge is case-sensitive. Normalise with Text.Upper or use fuzzy matching with Ignore case.
- Joins in Tableau's physical layer can duplicate measures; prefer relationships unless you need a flat table.
Refreshing merged data
Merges in Power Query re-run on every refresh, so new rows in the sources flow through automatically. In Power BI Service, a scheduled refresh needs a gateway for files on your own network; files in SharePoint or OneDrive refresh without one. Tableau extracts refresh on a schedule in Tableau Server or Cloud, while live connections query the source each time.
Merge Queries step by step
The dialog is the same in Excel and Power BI Desktop; only the way in differs.
- Excel: click in each table and choose Data → From Table/Range, then in the editor choose Close & Load To → Only Create Connection, so both tables exist as queries. Power BI: load both tables, then choose Home → Transform data.
- Select the main query — the one whose rows you want to keep — and choose Home → Merge Queries. Use Merge Queries as New if you want to keep the original query unchanged.
- Pick the second table from the drop-down, then click the key column in each table. For a key made of several columns, Ctrl-click them in the same order in both tables.
- Choose the Join Kind. The dialog reports how many rows of the first table found a match, which is your first check that the keys line up.
- Click OK. A new column of nested tables appears; click the expand icon in its header, tick the columns you need, and untick Use original column name as prefix for tidier names.
- Choose Close & Load (Excel) or Close & Apply (Power BI).
The six join kinds
| Join kind | Keeps | Typical use |
|---|---|---|
| Left outer | All rows of the first table, matches from the second | Add product details to every sale |
| Right outer | All rows of the second table, matches from the first | Rarely needed — swap the tables |
| Full outer | All rows from both | See everything, matched where possible |
| Inner | Only rows that match in both | Sales for known products only |
| Left anti | Rows of the first table with no match | Sales with an unknown product code |
| Right anti | Rows of the second table with no match | Products that never sold |
Fuzzy matching is an option inside the dialog rather than a seventh join kind; it tolerates spelling differences and can ignore case, with a similarity threshold you set. Use it for names typed by people, not for IDs.
What the M code looks like
Every click in the editor writes a step in Power Query's M language, visible in the formula bar or the Advanced Editor. A left-outer merge and expand looks like this:
let
Merged = Table.NestedJoin(Sales, {"ProductID"}, Products, {"ProductID"}, "Products", JoinKind.LeftOuter),
Expanded = Table.ExpandTableColumn(Merged, "Products", {"Category"}, {"Category"})
in
ExpandedAn append is a single function, Table.Combine({Jan, Feb, Mar}), which matches columns by name and fills columns missing from a table with null. Changing JoinKind.LeftOuter to JoinKind.LeftAnti in the formula bar is a quick way to list the unmatched rows without rebuilding the merge.
Worked example: an unmatched product code
Sales has five rows; Products lists three products. Sale S5 uses product code P-99, which isn't in Products.
| SaleID | ProductID | Amount |
|---|---|---|
| S1 | P-10 | 120 |
| S2 | P-11 | 80 |
| S3 | P-10 | 45 |
| S4 | P-12 | 200 |
| S5 | P-99 | 30 |
| ProductID | Category |
|---|---|
| P-10 | Seating |
| P-11 | Desks |
| P-12 | Lighting |
| Category | Amount |
|---|---|
| Seating | 165 |
| Desks | 80 |
| Lighting | 200 |
| null | 30 |
| Total | 475 |
The left outer merge keeps all 475 of sales, with S5's 30 under a null category. An inner merge would report 445 and lose S5 without a trace; a left anti merge lists S5 on its own so you can fix the code. In Power BI, the same data with a relationship instead of a merge shows the 30 against a (Blank) category in visuals, which is Power BI's way of flagging fact rows with no matching dimension row.
Power BI relationships in more detail
- Creating one: in Model view, drag the key from the fact table onto the key in the dimension table, or use Manage relationships. Power BI may also detect relationships when tables load.
- Cardinality: one-to-many (or many-to-one) is the normal case. One-to-one suits two tables that share a unique key; many-to-many is possible but makes totals harder to reason about, so remove duplicates from the dimension first where you can.
- Cross-filter direction: single (the dimension filters the fact table) is the safe default. Both directions can make slicers on one fact table filter another, but can create ambiguous paths in larger models.
- Active and inactive: only one relationship between two tables can be active. A second one — order date and ship date both pointing at a Date table — is inactive and is used in a measure with
USERELATIONSHIP. - Bringing a column across: if you need a dimension column inside the fact table, a calculated column with
RELATED(Products[Category])does it without a merge.
A practical rule: merge in Power Query when the second table only adds a few attributes and won't be used on its own; relate when the second table is a dimension that several fact tables or visuals will share, such as Date, Customer or Product.
Tableau: setting up relationships, joins, blends and unions
- Relationship: connect to the data, drag the first table onto the canvas, then drag the second next to it. Tableau proposes matching fields; check them in the panel below, and add more pairs for a multi-field key. Performance options let you state the cardinality and whether all records match, which Tableau uses to write more efficient queries.
- Join: double-click a logical table to open the physical layer, drag the second table in, click the join icon and choose inner, left, right or full outer. Join calculations let you transform a key (for example TRIM or a type change) inside the join clause.
- Blend: add a second data source in the Data pane. On a worksheet, the source of the first field you use becomes the primary; a link icon on a shared dimension in the secondary source shows the link, and Data → Edit Blend Relationships controls which fields are linked.
- Union: drag New Union onto the physical layer, or use a wildcard union to pick up every matching sheet or file in a folder, as Power Query's From Folder does.
Joins across different connections — an Excel sheet and a CSV file, say — are allowed in Tableau, though they can be slower than joins within one database. More detail: joining data sources in Tableau.
Worked example: why a join can inflate a total
Orders holds one shipping charge per order; OrderLines holds the products. Order 501 has shipping 10 and three lines; order 502 has shipping 6 and one line. True shipping is 10 + 6 = 16.
| OrderID | Line | Shipping |
|---|---|---|
| 501 | 1 | 10 |
| 501 | 2 | 10 |
| 501 | 3 | 10 |
| 502 | 1 | 6 |
After the join, the shipping charge repeats on every line, so SUM(Shipping) returns 10 × 3 + 6 = 36. The same thing happens when you expand order lines in a Power Query merge and then total a column from the order table. Three fixes, in order of preference: keep the tables related rather than joined (a Tableau relationship or a Power BI relationship sums shipping at the order level and returns 16); aggregate in a level-of-detail expression such as SUM({FIXED [OrderID] : MIN([Shipping])}); or merge in the opposite direction, bringing line totals up to the order table rather than shipping down to the lines.
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
| Merge dialog reports few or no matching rows | Key types, spaces or case differ | Set both keys to the same type; Trim and Upper them |
| Row count grows after a merge | Duplicate keys in the second table | Remove Duplicates on the key, then merge |
| Power BI won't create a one-to-many relationship | The dimension key isn't unique | Remove duplicates, or deliberately choose many-to-many |
| A (Blank) row appears in Power BI visuals | Fact rows whose key isn't in the dimension | List them with a left anti merge and fix the codes |
| A relationship line is dashed | It is inactive because another path exists | Use USERELATIONSHIP in a measure, or remodel |
| Tableau totals are too high | A physical-layer join repeats values | Use a relationship, or an LOD expression |
| An asterisk (*) appears in a blended view | The secondary source has several values for that mark | Link on more fields, or aggregate the secondary field |
| Refresh fails after files moved | The query still points at the old path | Update it in Data source settings |
Append Queries when headers don't quite match
Append lines columns up by name, so a header that differs by one character becomes a separate column. Stack a January table with Customer, Amount and a February table with Customer, Amount (GBP) and the result has three columns: January's rows have null in Amount (GBP), and February's rows have null in Amount. Totals of either column are then wrong without any error.
- Before appending, open each query and rename columns so every table uses the same headers (double-click the header, or use Transform → Rename).
- Choose Home → Append Queries (or Append Queries as New), select Three or more tables if needed, and add each table.
- After appending, scan each column with View → Column profile or the column quality bar: a column that is mostly empty usually means a header mismatch.
- Add a column that says which table each row came from before appending, for example with Add Column → Custom Column and the text "Jan", so differences can be traced back.
When the files sit in one folder, the same checks apply to combining files from a folder: one renamed header in one file is enough to split a column.
Which approach for a monthly report?
- Same-shaped files every month, Excel output: Power Query From Folder, then Group By or a pivot; next month means dropping a file in the folder and refreshing.
- A fact table plus lookup tables, shared dashboards: Power BI with relationships, refreshed on a schedule.
- Several sources at different levels of detail, visual exploration: Tableau relationships, with blends only for sources that can't be connected together.
- One wide table to export or clean: a Power Query merge, loaded as a table.
Keeping merges fast
- Remove columns you don't need before the merge, not after; the merge then moves less data.
- Filter rows early — last year's history, test orders — so both sides of the merge are smaller.
- With a database source, Power Query tries to send steps back to the database as SQL (query folding). Merges between two tables from the same database usually fold; a merge between a database table and an Excel file has to run locally.
- In Power BI, relationships instead of merges keep the model narrow, which usually means a smaller file and faster visuals.
If the job is a one-off question across a few spreadsheets rather than a model to maintain, Power Query vs Analistable compares the effort involved.
Every guide in this topic
- How to combine datasets in Amazon QuickSight
Join datasets in Amazon QuickSight data prep: add data, choose join type and keys, and avoid duplicate rows and failed cross-source joins.
- How to combine files from a folder with Power Query
Combine every Excel or CSV file in a folder with Power Query: the helper queries it creates, filtering files, adding the file name and fixing refresh errors.
- How to combine files with different sheet names in Power Query
Combine workbooks whose sheet names differ in Power Query: pick the sheet by position or Kind, match names loosely, and leave hidden sheets out.
- How to combine multiple files into one table with Power Query parameters
Make a Power Query folder combine reusable: put the folder path and file filters in parameters or a worksheet cell, and turn the logic into a function.
- How to join data sources in Tableau
Combine data sources in Tableau: relationships (the default), joins and cross-database joins in the physical layer, unions, and blends — when to use each.
- How to merge columns in Power Query (Excel and Power BI)
Join two or more columns into one in Power Query with Merge Columns or a Text.Combine custom column, handling separators, nulls and numbers.
- How to merge columns in Tableau
Merge two columns in Tableau with a combined field or a calculated field using + and STR(), and handle nulls with IFNULL so labels don't go blank.
- How to merge Power BI reports
There is no merge button for .pbix files. Combine Power BI reports by copying visuals, sharing one semantic model, or bundling reports in an app.
- How to merge Tableau workbooks
Merge Tableau workbooks with Import Workbook or by copying sheets, then reconcile duplicate data sources, calculated fields and dashboards.
- How to merge tables in Power BI
Merge two tables in Power BI with Merge Queries in Power Query, or relate them in the model instead. Steps, join kinds, DAX options and how to choose.
- How to use Append Queries in Power Query
Stack two or more tables with Power Query Append in Excel or Power BI. Append vs Append as New, mismatched columns and a source column,, step by step.
- How to use fuzzy merge in Power Query
Match names that are spelled differently with Power Query fuzzy merge: similarity threshold, ignore case, transformation tables and how to check the matches.
- Power BI alternatives for joining spreadsheets
Power BI is built for dashboards and data models. When all you need is to join a few spreadsheets, these simpler options are often the better fit.
- Power Query Combine Files errors and how to fix each one
Fix Power Query Combine Files errors: column not found, DataFormat.Error, key didn't match and silently skipped files, traced to the sample transform.
- Power Query Merge Queries: every join kind explained
Left outer, right outer, full outer, inner, left anti and right anti joins in Power Query Merge Queries, with the same example for each and when to use them.
- Tableau Prep alternatives for small teams
Tableau Prep is a capable data preparation tool. Lighter options for small teams that mostly join and clean spreadsheets, compared fairly.
- Why Power Query merge returns nulls, and how to fix it
Merged columns full of null in Power Query? Check key types, spaces, case, the join kind and duplicate keys, with a worked example and an M fix for each.
- Why Power Query refresh is slow with many files, and how to speed it up
Speed up a slow Power Query folder combine: filter files before opening them, cut repeated steps, switch off background previews and buffer with care.
Frequently asked questions
- What is the difference between merge and append in Power Query?
- Merge joins tables side by side on a key column. Append stacks tables on top of each other, matching columns by name.
- Should I merge tables or create relationships in Power BI?
- Use relationships for a star schema (fact and dimension tables). Merge in Power Query when you need one combined table, for example to clean it before loading.
- What is a blend in Tableau?
- A blend combines a primary and a secondary data source on a worksheet, aggregating the secondary source to the level of the linking fields.
- How do I find rows that didn't match in a Power Query merge?
- Merge the same two queries with the Left anti join kind. The result lists only the rows of the first table whose key has no match in the second.
- What does RELATED do in Power BI?
- RELATED is a DAX function that returns a column from a related table, following an existing relationship. In a calculated column on a fact table it works like a lookup, without merging the tables in Power Query.