How to merge data in Power BI, Power Query and Tableau
By the Analistable team · Updated · 3 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.
Every guide in this topic
- 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 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 tables with Power Query Append Queries in Excel or Power BI: two or more tables, Append vs Append as New, different columns and adding a source column.
- 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 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.
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.