The Magic of Merging: Getting Friendly with Power Query Joins in Power BI by Cristian Angyal at the M365 Con

At conferences, the best sessions are often the ones that make something technical feel suddenly approachable. That is exactly the energy behind Cristian Angyal’s presentation on Power Query joins: a topic that can sound database-heavy at first, but becomes practical very quickly once you see it through the lens of real reporting problems. In his session for M365Con, Angyal framed joins not as abstract theory, but as the everyday tool behind better data modeling, cleaner reporting, and fewer “why doesn’t this match?” moments.

His central message is refreshingly simple. Whether you work in Excel, Power BI, or dataflows, Power Query gives you a consistent way to combine data based on common keys. The interface may vary slightly from one Microsoft product to another, but the underlying logic is the same: select two tables, choose the matching columns, decide what kind of relationship you want returned, and let the merge do the heavy lifting. Microsoft’s current documentation still describes merge operations in exactly those terms, with six join kinds exposed in the UI and additional join kinds available through M code.

What makes this especially important in Power BI is that merging is not only about bringing in extra columns. A join can increase the number of rows, reduce the number of rows, reveal missing records, and expose hidden duplication. Used well, it becomes a diagnostic tool as much as a transformation tool. Used carelessly, it can quietly distort the dataset you thought you understood.

That is why Angyal’s session works so well as an introduction. Rather than starting with syntax, he starts with questions analysts actually ask: Who is registered? Who is missing? Which records are valid? Which entries look similar but do not match exactly? Those are business questions first, and Power Query joins are what turn them into reliable answers.

Why Merge Matters More Than Most People Think

Power Query’s merge feature sits at the center of modern self-service data preparation. Microsoft describes Power Query as a data connectivity and preparation technology used across products such as Excel and Power BI, which is why the same join concepts matter in both places. When you understand joins once, you can reuse that knowledge almost everywhere Power Query appears.

Angyal makes a useful comparison for Excel users: joins are often where people mentally map from VLOOKUP or XLOOKUP into Power Query. That comparison helps at first, but it is only partly true. A lookup function usually feels like “bring me one value from another table,” while a Power Query merge is broader and more structural. It can return one row, many rows, unmatched rows, or all rows from both sources depending on the join kind you choose. Microsoft’s merge overview emphasizes exactly that difference by defining join kinds as distinct behaviors for how two tables are combined.

This is also where Power Query begins to feel more like data modeling than spreadsheet patchwork. A merge is based on key columns, and those key columns need to be chosen with care. If the key is not unique, duplicates can multiply. If the data types do not line up, valid relationships can fail to match. If the text is inconsistent, records that should join might remain apart. None of that is cosmetic; it changes the output of the query.

Angyal highlights another practical reason to care: performance. Query folding allows some Power Query transformations to be translated back to the source system, which can reduce the work done locally. Microsoft explains query folding as the process by which M logic is interpreted and, when possible, turned into source requests rather than fully processed in the client. That does not happen in every source or every scenario, but when it does, a well-structured merge can be significantly more efficient than a messy chain of downstream fixes.

Getting Comfortable with the Merge Interface

One of the most helpful parts of the session is Angyal’s effort to demystify the Merge dialog itself. New users often get stuck before the logic even begins, simply because the interface labels are described differently in different tutorials. He resolves that confusion by using the most intuitive language possible: the table at the top is the left table, and the table below it is the right table. Once that mental model clicks, the rest becomes easier.

Microsoft’s merge documentation reinforces that framing. In the Merge dialog, you select matching columns from the left and right tables, and the join kind determines whether the result keeps all rows from one side, only matched rows, or rows unique to a side. The UI exposes six standard choices: left outer, right outer, full outer, inner, left anti, and right anti.

A subtle but important point from the session is the difference between merging into an existing query and creating a new merged query. Angyal prefers to reference the original table first and then merge from that reference. That pattern keeps the source query cleaner and often makes the logic easier to maintain later. It is less about correctness than about readability, but readability matters when a model grows from a two-table demo into a production report with many dependencies.

Another detail worth noting is what happens after the merge. Power Query does not immediately flatten the second table into the first. Instead, it adds a nested table column that you can expand. That design is useful because it lets you preview the matched rows before deciding which fields to bring in. It also makes it obvious when no match exists, because the expanded values come back as null. For troubleshooting joins, that nested-table step is not an inconvenience; it is a diagnostic window.

The Six Core Join Types Every Analyst Should Know

The first join most users learn is the inner join, and for good reason. It returns only rows that match in both tables. In Angyal’s attendee-and-workshop example, that means keeping only people who both exist in the attendee list and have a workshop registration. Microsoft’s documentation defines inner join in exactly this way: only matching rows from both tables are included.

The left outer join is the default in the Power Query interface, and it is often the most useful starting point for analysis. It keeps all rows from the left table and brings in matching values from the right table where available. This is especially helpful when you want a complete master list and also want to see who is missing related information. In practice, that often means nulls become a feature, not a flaw, because they reveal who has not completed a registration, who lacks a category, or which transactions are missing enrichment.

Right outer join is the mirror image: keep everything from the right table and match what you can from the left. Angyal uses this to uncover “mystery” attendee IDs that appear in workshop registrations but not in the official attendee table. That is a classic data-quality move. Instead of assuming the registration table is fine, the join exposes orphaned records that deserve follow-up.

Then there is full outer join, the most complete reconciliation view of the visible join types. It returns all rows from both tables, whether matched or unmatched. Used carefully, it gives a 360-degree picture of the relationship between two sources. Microsoft’s merge overview describes full outer join simply as all rows from both tables, but in day-to-day reporting that translates into something more valuable: a compact way to spot valid matches, missing lookups, and suspicious records in one place.

The Hidden Superpowers: Semi Joins and Anti Joins

This is where the session becomes especially valuable for people who think they already know Power Query merges. The standard interface shows six join kinds, but the M function Table.Join supports eight: the six visible options plus JoinKind.LeftSemi and JoinKind.RightSemi. Microsoft’s M documentation lists both semi joins explicitly, even though they are not surfaced in the main merge UI.

A semi join is perfect when you want filtering, not enrichment. Left semi keeps only rows from the left table that have a match on the right, but it does not return columns from the right table. That makes it ideal for questions like “Which invited speakers are presenting?” or “Which attendees selected a workshop?” In other words, semi join answers the existence question without dragging extra detail into the result.

Anti joins do the inverse job. The UI already exposes left anti and right anti, and Microsoft defines them as keeping only rows unique to one side. In business terms, these are your gap-finders. They answer questions such as “Who has not registered?”, “Which products have no orders?”, or “Which IDs exist in one system but not the other?” They are among the fastest ways to audit process failure, incomplete syncs, or broken assumptions in source data.

Angyal’s practical advice here is excellent: if your question begins with “who has?” think semi; if it begins with “who hasn’t?” think anti. That framing removes much of the intimidation around these join types. It also helps analysts avoid overusing inner joins when what they really need is a yes-or-no filter.

Beyond Exact Matches: Multi-Column, Fuzzy, and Conditional Joins

Exact single-column joins are only the beginning. Real data is messier. Sometimes one field is not enough to uniquely identify a match, which is why multi-column joins matter. Power Query supports selecting multiple keys during a merge, and Microsoft’s Table.Join function likewise allows key columns to be defined for each table. In practice, that means you can join on combinations such as room, date, and time slot instead of relying on room name alone.

That multi-column pattern is essential whenever a single identifier is ambiguous. Angyal demonstrates this with room bookings and equipment setups, where matching only on room would produce incorrect duplicates. Matching on room, date, and time slot produces the intended result. The broader lesson is clear: if a single key is not truly unique for the business process, the merge result will not be trustworthy, no matter how polished the report looks afterward.

Fuzzy matching handles a different problem: values that should match conceptually but differ textually. Microsoft documents fuzzy merge as a feature that compares text values using similarity logic rather than exact equality, and it is enabled directly in the Merge dialog through the fuzzy matching option. It is supported only for text columns, and Power Query uses the Jaccard similarity algorithm for the comparison. Microsoft also notes that the default similarity threshold is 0.8, while lower thresholds allow looser matching and the transformation table can standardize values before the fuzzy logic runs.

Angyal’s caution on fuzzy matching is just as important as his demonstration of it. It is powerful, but it is not magic. Lowering the threshold can surface more potential matches, yet it can also introduce questionable ones. For small exploratory tasks, fuzzy merge can rescue ugly name data quickly. For larger production datasets, it should be used deliberately, tested carefully, and preferably backed by explicit transformations wherever possible. Microsoft’s documentation similarly frames transformation tables as a way to map values before or during fuzzy matching, which is often safer than trusting approximate similarity alone.

Why This Session Matters in 2026

One of the charming themes in the talk is the contrast between old habits and modern tooling. The nostalgic references to obsolete devices and the “aha” moment of mastering VLOOKUP are more than jokes; they underline a shift in mindset. Many analysts still approach table combination as a lookup problem first, when Power Query invites them to think in joins, relationships, and data quality patterns instead.

That shift is increasingly relevant because Power Query now spans a broad Microsoft ecosystem. As Microsoft’s documentation shows, Power Query connectors and capabilities reach across Excel, Power BI, and multiple other services, so the value of understanding joins compounds over time. The same mental model you learn for a demo in Excel can support a far more serious workflow in Power BI or Fabric later.

What Angyal ultimately offers is not just a feature walkthrough, but a decision framework. Use inner join when you need only confirmed matches. Use outer joins when you want a complete view with missing data exposed. Use semi joins when you need existence checks. Use anti joins when you need exceptions. Use multiple keys when one key is not enough. Use fuzzy matching only when exact text agreement is unrealistic and the tradeoff is acceptable. That is the kind of framework that makes Power Query feel less like a menu of options and more like a language you can actually think in.

For anyone building reports, cleaning exports, reconciling business systems, or preparing a model for analysis, that is the real magic of merging. Power Query joins are not just there to combine tables. They help you ask better questions of your data and notice problems before those problems end up on a dashboard. Once that clicks, joins stop feeling technical and start feeling indispensable.