Analistable

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

Power Query operations
OperationWhat it doesSQL equivalent
Merge QueriesAdds columns from another table, matched on a keyJOIN
Append QueriesAdds rows from another table, matched by column nameUNION ALL
Combine Files (From Folder)Appends every file in a folderUNION ALL over files
Group BySummarises rowsGROUP 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?

Choosing a tool for merging data
SituationGood choice
Recurring Excel reports from several filesPower Query in Excel
Dashboards shared across a company on Microsoft 365Power BI
Visual analysis with many data sourcesTableau
Quick answers from a few sheets, no model to buildAnalistable

Worked example: one question, three tools

Question: total sales by product category, where Sales has ProductID and Amount, and Products has ProductID and Category.

How each tool answers it
ToolApproachSteps
Excel Power QueryMerge Sales with Products, expand Category, then pivotMerge Queries → expand → Close & Load to PivotTable
Power BIRelationship Sales[ProductID] → Products[ProductID]Model view drag, then a visual with Category and Sum of Amount
TableauRelationship between the two logical tablesDrag 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.

  1. 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.
  2. 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.
  3. 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.
  4. 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.
  5. 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.
  6. Choose Close & Load (Excel) or Close & Apply (Power BI).

The six join kinds

Join kinds in the Merge dialog
Join kindKeepsTypical use
Left outerAll rows of the first table, matches from the secondAdd product details to every sale
Right outerAll rows of the second table, matches from the firstRarely needed — swap the tables
Full outerAll rows from bothSee everything, matched where possible
InnerOnly rows that match in bothSales for known products only
Left antiRows of the first table with no matchSales with an unknown product code
Right antiRows of the second table with no matchProducts 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
    Expanded

An 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.

Sales
SaleIDProductIDAmount
S1P-10120
S2P-1180
S3P-1045
S4P-12200
S5P-9930
Products
ProductIDCategory
P-10Seating
P-11Desks
P-12Lighting
Sales by category after a left outer merge
CategoryAmount
Seating165
Desks80
Lighting200
null30
Total475

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

  1. 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.
  2. 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.
  3. 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.
  4. 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.

Orders joined to OrderLines
OrderIDLineShipping
501110
501210
501310
50216

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

Problems when merging or relating data
SymptomLikely causeFix
Merge dialog reports few or no matching rowsKey types, spaces or case differSet both keys to the same type; Trim and Upper them
Row count grows after a mergeDuplicate keys in the second tableRemove Duplicates on the key, then merge
Power BI won't create a one-to-many relationshipThe dimension key isn't uniqueRemove duplicates, or deliberately choose many-to-many
A (Blank) row appears in Power BI visualsFact rows whose key isn't in the dimensionList them with a left anti merge and fix the codes
A relationship line is dashedIt is inactive because another path existsUse USERELATIONSHIP in a measure, or remodel
Tableau totals are too highA physical-layer join repeats valuesUse a relationship, or an LOD expression
An asterisk (*) appears in a blended viewThe secondary source has several values for that markLink on more fields, or aggregate the secondary field
Refresh fails after files movedThe query still points at the old pathUpdate 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.

  1. Before appending, open each query and rename columns so every table uses the same headers (double-click the header, or use Transform → Rename).
  2. Choose Home → Append Queries (or Append Queries as New), select Three or more tables if needed, and add each table.
  3. 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.
  4. 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

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.