Introduction
At Versich, we spend a lot of our time inside Power Query, whether we are building a financial reporting model on top of NetSuite data, blending Shopify transactions with operational metrics, or preparing a clean data model for a client's executive dashboard. One of the most common questions our analytics team gets asked is how to bring two or more tables together correctly. The answer almost always comes down to choosing the right merge type.
Power Query offers six distinct join kinds, and each one produces a different result set depending on how you want unmatched rows handled. Picking the wrong one is one of the most frequent causes of inflated row counts, missing records, or numbers that simply do not tie back to source systems. We have seen this trip up teams who are otherwise very capable with DAX and visuals, because the merge step happens quietly in the background before the model is even built.
This matters more than it might seem at first glance. A Power BI report can look polished, with clean visuals, well-organized slicers, and confident-looking KPIs, while the underlying merge logic is silently duplicating rows or dropping records that should have been included. We have walked into client engagements where a finance team had been working off a dashboard for months before realizing that a Full Outer join, rather than a Left Outer join, had inflated their revenue totals by a noticeable margin. The fix took minutes once identified, but the months of decisions made on faulty numbers were harder to undo.
In this blog, we walk through each of the six merge types available in Power Query, explain when we use each one in real client projects, and share some practical tips we rely on to keep our Power BI models accurate and performant. Whether you are merging NetSuite saved searches, QuickBooks exports, or e-commerce platforms like Shopify, the principles below will help you choose the right join every time.
What Merging Means in Power Query
Merging in Power Query is the process of combining two queries based on one or more matching columns, similar to a SQL join. Unlike appending, which stacks rows from tables with the same structure, merging brings columns from a second table into the first based on a shared key, such as a customer ID, an order number, or a SKU.
When we set up a merge in Power Query, we are asked to choose a join kind. This choice determines which rows survive the merge and which ones get dropped or padded with null values. Getting this right at the source query level saves significant rework later in the data model and in DAX measures, because it means the grain of the table is correct before a single visual is built.
It is worth pointing out that Power Query refers to the two tables involved in a merge as the primary and secondary table. The primary table is whichever query you select first in the merge dialog, and the secondary table is the one you select to merge against it. This distinction is easy to overlook, but it directly affects the outcome of joins like Left Outer and Right Outer, since those two join kinds behave as mirror images of one another depending on which table holds the primary position.
The Six Join Kinds in Power Query
Power Query labels its primary table as the first table selected in the merge dialog, and its secondary table as the second. Every join kind behaves differently depending on which table is primary, so the order in which you select your tables matters just as much as the join kind itself.
Each join kind is visualized in the merge dialog with a small diagram showing two overlapping circles, similar to a Venn diagram, with shading to indicate which portion of the data is retained. This visual cue is genuinely useful once you understand what each shape represents, and we encourage anyone newer to Power Query to pay close attention to it rather than relying purely on the join name.
Here is a quick reference table we often share with clients during NetSuite and Power BI training sessions:
Merge Type | What It Keeps | Best Used For |
Left Outer | All rows from the primary table, matched rows from the secondary table | Enriching a primary dataset such as sales orders with lookup details |
Right Outer | All rows from the secondary table, matched rows from the primary table | Flipping the join direction without rebuilding the query |
Full Outer | All rows from both tables, matched or not | Data quality checks and identifying gaps between two systems |
Inner | Only rows that match in both tables | Reconciliation reports where only confirmed matches matter |
Left Anti | Only rows from the primary table with no match in the secondary table | Finding orders without invoices or customers without recent activity |
Right Anti | Only rows from the secondary table with no match in the primary table | Finding reference records that are never actually used |
We will go through each of these in more depth below, along with the situations where we typically reach for them in our own consulting and implementation work.
Left Outer Join: The Default and Most Common Choice
A Left Outer join keeps every row from the primary table and adds matching columns from the secondary table wherever a match exists. Rows in the primary table with no match simply get null values in the new columns rather than being dropped.
This is the join kind we use most often, and it is also the default selection in the Power Query merge dialog. A typical example from our NetSuite consulting work is merging a transaction line table with a customer master table to pull in fields like region, sales rep, or customer tier. We want every transaction line to remain in the result, even if a particular customer record happens to be missing or inactive.
Left Outer joins are the safest starting point whenever you need to enrich a fact table with attributes from a dimension table, and the fact table is the one whose row count must not change. We also use this join kind heavily when integrating Power BI with QuickBooks, where transaction exports often reference customer or vendor records that get archived or renamed over time. A Left Outer join ensures the transaction history stays complete even when the lookup table has gaps.
One nuance worth highlighting is that a Left Outer join can still inflate row counts if the secondary table has duplicate keys. If a customer ID appears twice in the lookup table, every matching transaction line will be duplicated in the result. We always check for duplicate keys in the secondary table before finalizing a merge, since this is one of the most common causes of unexpectedly high row counts in an otherwise correct query.
Right Outer Join: The Mirror Image
A Right Outer join is functionally the reverse of a Left Outer join. It keeps every row from the secondary table and brings in matched columns from the primary table, again padding unmatched rows with nulls.
In practice, we rarely build a Right Outer join directly. Instead, we usually achieve the same result by swapping which table is selected first and using a Left Outer join instead, since it keeps the logic easier to read for anyone reviewing the query later. That said, Right Outer is useful when you already have a query built around a particular table order and do not want to restructure it just to flip the join direction.
Full Outer Join: Capturing Everything from Both Sides
A Full Outer join keeps every row from both the primary and secondary tables, matched or not. Where there is no match, the missing columns are filled with nulls on whichever side is incomplete.
We reach for this join kind during data quality and migration work, particularly during NetSuite implementation projects where we need to compare records between a legacy system and NetSuite. For example, when reconciling a vendor list between QuickBooks and NetSuite during a migration, a Full Outer join lets us see vendors that exist in both systems, vendors that only exist in QuickBooks, and vendors that only exist in NetSuite, all in a single result set.
Because Full Outer joins can significantly increase row counts when keys do not align well, we always recommend reviewing the result carefully before loading it into a production data model. We typically add a calculated column immediately after a Full Outer join that flags whether a row matched on both sides, matched only on the left, or matched only on the right. This single column turns a wide, hard-to-read result set into something a finance or operations team can scan in seconds to spot exactly where the discrepancies sit.
Inner Join: Only the Matches
An Inner join keeps only the rows where a match exists in both tables. Anything that does not have a corresponding match on either side is excluded entirely.
This join kind is ideal for reconciliation scenarios where we only care about confirmed matches. For instance, when validating that every invoice in NetSuite has a corresponding payment record in a banking export, an Inner join quickly isolates the transactions that are fully accounted for, leaving the exceptions to be investigated separately using an Anti join.
Inner joins are also useful for performance reasons. Because the result set is typically smaller than the source tables, they can reduce the volume of data loaded into the Power BI model, which is particularly helpful in larger NetSuite and Power BI implementations where transaction volumes run into the millions of rows. On several of our larger managed services engagements, switching an unnecessary Left Outer join to an Inner join, once we confirmed the business question only cared about matched records, noticeably improved refresh times in the published dataset.
Left Anti Join: Finding What Is Missing on One Side
A Left Anti join keeps only the rows from the primary table that have no match in the secondary table. None of the columns from the secondary table are returned, since by definition there is nothing to bring across.
We use this join constantly during exception reporting. A good example is identifying NetSuite sales orders that do not yet have a corresponding fulfillment record, or customers in a CRM export who have no matching transactions in NetSuite over the past twelve months. It is one of the fastest ways to surface gaps that need attention from a finance or operations team.
On Shopify analytics projects in particular, Left Anti joins are useful for spotting orders that exist in Shopify but have not yet synced through to NetSuite, which is often the very first signal that an integration has stalled or a connector has hit an error. Building this check directly into a Power BI refresh means the gap gets noticed within a day rather than at month end when the books are being closed.
Right Anti Join: The Same Idea, Reversed
A Right Anti join is the mirror of a Left Anti join. It keeps only the rows from the secondary table that have no match in the primary table.
This join kind tends to surface during data governance work, such as identifying reference or lookup table entries, like tax codes or item categories, that are never actually referenced by any transaction. Cleaning these out keeps dimension tables lean and avoids confusion for end users browsing slicers in a Power BI report. As with Right Outer, we often find it simpler to swap the table order and use a Left Anti join instead, purely to keep the query steps easier to read for the next person who opens the file.
Practical Guidance We Share with Our Clients
Beyond knowing what each join kind does on paper, there are a few practical habits that consistently help when merging data for Power BI models:
- Always confirm the grain of each table before merging. Merging a one-to-many relationship without first aggregating one side will silently inflate row counts and overstate totals.
- Check data types on the key columns. A text-formatted ID in one table and a numeric ID in another will fail to match even when the values look identical.
- Use Inner and Anti joins together as a pair during reconciliation work. The Inner join shows what matches, and the Anti join shows what is missing, giving a complete picture in two simple steps.
- Expand only the columns you need after a merge. Power Query allows you to select specific columns from the secondary table, which keeps the resulting table lean and the model easier to maintain.
- Rename merged columns clearly. A second table's ID or Name column can easily get confused with the primary table's own fields if left with the default names.
These habits come up repeatedly across the NetSuite, Power BI, and QuickBooks integration projects we deliver, and they apply just as well whether the source is an ERP export, a CSV from a bank, or a connector pulling live data from Shopify.
Choosing the Right Merge for Your Power BI Model
There is rarely a single correct join for an entire data model. Most Power BI solutions we build use a mix of join types across different queries, depending on the question each table is answering. A sales dashboard might use Left Outer joins to enrich transactions with customer and item attributes, while a separate data quality query in the same model uses Full Outer or Anti joins to flag discrepancies for the finance team to review.
The key is to think about the business question first, then choose the join that protects the row count and the answer that question needs. If the goal is completeness of one table, Left or Right Outer is usually correct. If the goal is finding exceptions, an Anti join is the right tool. If the goal is validating overlap between two systems, Inner and Full Outer joins do the heavy lifting.
This is also where experience with the underlying source systems matters. Knowing how NetSuite structures its saved searches, how QuickBooks exports its transaction data, or how a Shopify connector shapes order and customer records makes it much easier to predict which join will behave as expected and which one needs a closer look before it goes into a production model.
Conclusion
Merging is one of the most foundational steps in building a reliable Power BI model, and the six join kinds available in Power Query give you the flexibility to handle almost any data combination scenario, from simple lookups to full reconciliation reports. Understanding what each join keeps, what it discards, and where it tends to be useful is what separates a model that holds up under scrutiny from one that quietly produces the wrong numbers.
We have found that the teams who get the most value out of Power BI are the ones who treat the Power Query stage with the same care they give their DAX measures and visuals. A well-chosen merge means fewer surprises during a board meeting, faster troubleshooting when numbers look off, and a data model that scales cleanly as new sources get added over time.
At Versich, we help clients design Power BI data models that are accurate from the ground up, whether that means structuring Power Query merges around NetSuite data, integrating QuickBooks exports, or connecting platforms like Shopify into a unified reporting layer. If you would like our team to review your current Power BI data model or help you build a new one from scratch, we would be glad to help.
Get in touch with our team on our Contact Us page to start the conversation.
