Merging two data sources in Power BI
Learn how to merge two data sources in Power BI with Power Query, pick join types, and connect tables through relationships in the data model.
Contents
- What merging two data sources does
- Set up both queries before the merge
- Run a merge in Power Query Editor
- Choose the right join type
- Check the merge for mismatched keys
- Merge queries or build a relationship
- Keep the merged model readable
- Questions about merging data sources in Power BI
- Can I combine multiple data sources in Power BI at once?
- What is the difference between merging queries and creating relationships?
- Which join type should I use for a merge?
- Why did rows disappear after I merged two sources?
- Do I need Power Query Editor to merge data in Power BI?
Merging two data sources in Power BI means combining their columns into one table inside Power Query Editor, matched on a shared key such as an ID or a name. You do this before the data reaches the data model. Relationships are a different step: they connect whole tables and keep them side by side.
- Merge queries when you need columns from a second source in the same table.
- Use a relationship when you only need to filter or slice one table by another.
- Check that the join key has the same data type on both sides before merging.
- Expand only the columns you need, so the merged table stays readable.
- Review the row count after a merge to catch keys that failed to match.
What merging two data sources does
Merging two data sources in Power BI adds the columns of a second query to a first query, row by row, based on a matching key. The result is one wider table that carries information from both sources together. It happens in Power Query Editor, the transformation layer that loads data into the model.
The two sources do not need to share a format or a system. One could be a spreadsheet export and the other a database table, a web feed or a folder of files. Power BI reads each one as a query, and the merge works on the query output rather than on the original system.
A merge is not the same as appending. Appending stacks rows from sources with the same structure, while merging widens rows by matching a key. Decide which one you need before you open the merge dialog, because the two produce different tables.
Set up both queries before the merge
Both queries must exist in Power Query Editor before you merge them, and each should be cleaned first. Remove unneeded columns, set data types, and trim spaces from text keys. Cleaning early keeps the merged table narrow and the match reliable.
The key column must hold the same kind of value on both sides. A text code and a whole number code look alike on screen but will not match. Set the data type explicitly on each key column with the type icon in the column header.
Give each query a clear name so the merge dialog and later steps stay readable. Rename queries in the Queries pane before merging, rather than after. A merge stores the source query names in its step, so a clear name now saves confusion later.
Run a merge in Power Query Editor
Power Query Editor performs the merge from the Home tab with the Merge Queries command. Select the first query, open the command, and choose the second query in the dialog. Then click the key column in each preview so Power BI knows which values to match.
The dialog also asks for a join type, which decides how unmatched rows are treated. Pick the join kind before you confirm, because changing it later means editing the step. The join type section below explains each option.
After you confirm, Power BI adds a column holding a nested table for each row. Expand that column with the expand icon and tick only the fields you want to bring across. You can also clear the option to use the original column name as a prefix, which keeps headers readable.
- Open the Home tab and choose Merge Queries.
- Select the second query from the drop-down list.
- Click the matching key column in each preview table.
- Choose a join kind and confirm the merge.
- Expand the new column and select the fields to keep.
- Rename the merged query and check the row count.
Choose the right join type
A join type controls which rows survive the merge, and the choice changes your numbers. An inner join keeps only rows whose key appears in both queries. A left outer join keeps every row of the first query and fills missing second-query fields with null.
A right outer join keeps every row of the second query instead. A full outer join keeps rows from both sides, matched where possible. Anti joins keep only the rows with no match, which helps you find keys that exist in one source but not the other.
Pick the join that matches the question you are answering. Use a left outer join when the first source is your full list and the second adds detail. Use an inner join when a row is only useful if both sources describe it.
| Join type | Rows kept | Typical use |
|---|---|---|
| Inner | Keys in both queries | Keep only matched records |
| Left outer | All rows of the first query | Add detail to a full list |
| Right outer | All rows of the second query | Add detail from the other side |
| Full outer | All rows of both queries | Audit both sources together |
| Anti | Unmatched rows only | Find missing or orphan keys |
Check the merge for mismatched keys
A merge can quietly drop rows when keys fail to match, so compare row counts before and after. Note the count of the first query, then count the merged output. A large gap under an inner join points to a key problem.
Text keys often differ by case, leading spaces or trailing spaces. Use Trim and Clean on both key columns, and consider a lowercase step when case should not matter. Numbers stored as text will also fail to match numeric keys.
Dates are another source of silent mismatches, because a date and a date-time value are different types. Set both key columns to the same date type before merging. Power Query shows a warning when a step depends on a missing column, so read those messages rather than dismissing them.
Merge queries or build a relationship
A relationship connects two tables in the data model and leaves each table intact, while a merge produces one table. Relationships suit a star shape, where a fact table links to lookup tables on shared keys. The model then filters and slices across tables without extra columns.
Merging suits a flat table that a report needs as a single row per record. It also suits a source that has no clean key for a model relationship. Duplicating a large table through merges can inflate the model, so weigh that cost.
Both approaches can live in one report. Merge where a column is needed in the same row, and relate where filtering across tables is enough. Name and document each choice so the next person can follow the data model.
Keep the merged model readable
Documentation makes a merged model easier to maintain, because a merge step hides its logic inside a dialog. Rename the merge step and add a short note in the query name. Anyone reopening the file can then see which sources feed which output.
Refresh behavior depends on the sources, and a merge does not change that. Each source refreshes on its own credentials and privacy settings. Merging two sources can trigger privacy level checks, so set levels that reflect how sensitive each source is.
Where a merge needs complex matching or repeated reshaping, a data engineer or BI specialist can help design it. Simple key matches are safe to build yourself. A wrong join type can distort totals in a report, so test the output against a known figure before publishing.
Questions about merging data sources in Power BI
Can I combine multiple data sources in Power BI at once?
Yes, Power BI can combine multiple data sources by chaining merges in Power Query Editor. Merge the first two queries, then merge the result with a third using the same dialog. Keep each step named so the chain stays readable.
What is the difference between merging queries and creating relationships?
Merging queries builds one wider table in Power Query, while a relationship links two separate tables in the data model. Merging adds columns to a row, and a relationship filters across tables. Use a merge when the report needs the fields side by side.
Which join type should I use for a merge?
Choose an inner join when only matched rows matter and a left outer join when your first query is the full list. A full outer join helps you audit both sources. The join type decides which unmatched rows appear, so test the row count after the merge.
Why did rows disappear after I merged two sources?
Rows disappear because their key values did not match between the two queries. Trim spaces, fix data types and check for case differences on both key columns. An inner join also removes every row with no match by design.
Do I need Power Query Editor to merge data in Power BI?
Yes, merging data in Power BI happens in Power Query Editor, not on the report canvas. The editor records each merge as a step you can edit later. Relationships, by contrast, are set in the model view.