Merging Queries to Enrich Data

This episode focuses on a practical PL-300 objective: Merging Queries to Enrich Data. Merging queries joins columns from related tables, so key selection, match behavior, and row counts must be checked after the merge. The discussion keeps the work grounded in the data-preparati…

A sales report has the right total revenue, but each transaction contains only a customer identifier. The business wants customer region and account category beside every sale. You merge the sales query with the customer query, refresh the preview, and see those customer attributes in the result. Is that enough evidence that the merge is correct?

Not yet. A merge can produce a table that looks convincing while quietly losing sales, repeating them, or failing to match valid customers. The real test isn't whether Power BI accepted the configuration. You need to know which values were matched, what the chosen join was supposed to preserve, whether either key contained duplicates, and how the row population changed. Those checks distinguish a correct data transformation from one that merely completed without an obvious error.

The PL-300 course blueprint includes the combined skill of merging and appending queries. This episode concentrates on merging related tables. Merging combines tables horizontally.

It matches values from selected key columns, applies the chosen join semantics, and brings related information together across columns. Appending, which belongs to the next part of this skill, addresses a different shape of combination. For now, keep your attention on related records that need to be joined side by side.

Power Query is Power BI’s data acquisition and transformation environment, so this decision belongs first in the query layer. The requested outcome is a prepared sales result containing attributes obtained from related customer data. It isn't initially a request for a DAX calculation, a visual interaction, or a service permission. Those other layers can affect what a consumer eventually sees, but they don't change the fact that the requested operation is a query transformation based on matching keys.

Suppose the sales query has one row per transaction. Each row carries a Customer ID. The customer query is intended to have one row per customer, using the same kind of identifier.

The analyst wants to retain the required sales population and attach the corresponding region and account category where a valid match exists. That description contains the important decisions. It identifies the starting grain, the matching key, the information being added, and the business expectation for sales that don't find a customer.

Before merging, the analyst profiles both key columns. Do the Customer ID values have the expected data quality? Is the customer key actually unique? How many sales keys are likely to match?

Are unmatched values expected, or would they indicate a problem? These aren't optional questions added after the transformation. They establish what a correct result should look like.

If each sales row has one Customer ID and each identifier appears once in the customer query, a matched sale can receive one customer record’s attributes without changing the number of sales rows. If the customer query unexpectedly contains the same identifier several times, one sale may match several customer records. The merge can then multiply that sale when the related results are incorporated. The values may look valid individually, yet the resulting table no longer has one row per transaction.

That is why key uniqueness is a business and technical condition, not a cosmetic preference. A customer query might contain repeated identifiers because it includes several versions of a customer, several operational records, or an unresolved data-quality issue. The evidence packet for this lesson doesn't establish which explanation applies in any particular organization. The analyst must investigate rather than assume that one duplicate can safely be selected or discarded.

The join choice matters just as much. Different requirements call for different treatment of matched and unmatched records. If the business needs every transaction preserved, then a join that removes sales without customer matches conflicts with the requirement.

If the task is specifically to analyze only confirmed matches, retaining every unmatched row could be inappropriate. There isn't one universally correct merge setting. The correct semantics come from the required population and the meaning of an unmatched key.

Now consider the nearest alternative. The analyst could leave the sales and customer tables separate and connect them in the semantic model. That would be a model-layer design rather than the same query-layer transformation. A model relationship can support behavior between separate model tables, but it doesn't mean Power Query has horizontally combined their query outputs. If the requirement explicitly calls for a prepared query containing customer attributes beside each sale, changing a relationship or writing a DAX expression addresses a different layer.

That distinction also guides troubleshooting. If the query output already contains too many rows immediately after the merge, changing a report visual won't repair the transformation. If the query output is correct but a report responds unexpectedly to filters, the investigation may need to continue into the semantic model, DAX context, or report interaction. Start with the layer where the symptom first appears. Don't make unrelated changes just because they’re familiar.

Imagine that the original sales query contains ten thousand transaction rows. That count is only an illustrative baseline, not a universal acceptable number. After customer information is merged, the analyst expects every transaction to remain represented once. The resulting query instead contains ten thousand two hundred rows. The extra two hundred rows are observable evidence that the grain may have changed.

A tempting response is to remove the extra rows at the end. That treats the symptom without explaining the cause. The analyst should compare the customer key profile with the merge behavior.

If some customer identifiers occur more than once, sales linked to those identifiers can produce multiple matches. A final deduplication step could also remove transactions that are legitimately distinct, especially when several sales share other visible values. The safer response is to understand why the key isn't unique and decide what record population the requirement actually calls for.

The opposite symptom matters too. Suppose the merged result contains fewer sales than the starting query. The chosen join may be retaining only matched records, or valid sales keys may not be matching as expected. The analyst compares the expected match rate with the actual unmatched population.

If all sales should refer to governed customer records, a large unmatched set signals something to investigate. If anonymous transactions are valid, some unmatched sales may be expected. The count becomes meaningful only when it’s interpreted against the requirement.

This is why successful configuration isn't the finish line. Power Query can carry out the selected behavior exactly as configured while the selection itself is wrong for the business question. The interface doesn't know whether an unmatched customer represents an error, an allowed guest sale, or a record that belongs outside the analysis. The analyst supplies that meaning and chooses the join accordingly.

Apply that discipline from the beginning. First, state the intended output in plain language. For the sales example, the analyst might say, “Preserve each required transaction once, match it to the governed customer identifier, and add region and account category when the corresponding customer record exists.” That sentence gives the transformation something testable. It says which rows must survive, what the key represents, which attributes are needed, and what one output row is supposed to mean.

Next, inspect the starting queries rather than trusting their names. A query called Customers isn't automatically one row per customer. Its key profile must support that interpretation. The sales query also needs an understood grain.

If a row represents a transaction line rather than an entire transaction, repeating the customer attributes across several lines may be perfectly correct. Row repetition isn't itself evidence of a faulty merge. Unexpected multiplication relative to the starting grain is the problem.

Then consider the expected match rate. The analyst doesn't have to predict every result from memory. The goal is to establish a reasonable expectation that can be compared with the output.

If the governed process requires every sales record to contain a valid Customer ID, unmatched rows should be exceptional. If sales can legitimately occur without a registered customer, a nonzero unmatched population may be normal. In either case, unmatched records deserve examination because they reveal what the merge did with the source populations.

After applying the merge, compare the row count before and after. An unchanged count may support the expectation that each starting row matched at most one related record, but the count alone doesn't prove correctness. Some rows could have been removed while others were multiplied, producing the same total by coincidence. The analyst also examines the unmatched population and checks representative keys from normal and edge conditions.

For a normal case, choose a sales key known to have one governed customer record. Confirm that the resulting customer attributes correspond to that identifier and that the sale remains represented once. For an edge case, use a key known to be unmatched or duplicated in the source profile. The output should follow the chosen join semantics and the stated business rule. This comparison verifies behavior rather than merely confirming that the transformation exists.

Suppose the normal customer matches correctly, but one regional office uses identifiers containing values that don't match the governed customer query. The report may appear fine under a filter for the main region and fail when the other region is selected. The filter didn't create the query problem. It exposed a population whose key values weren't producing the expected matches. The analyst reproduces that condition, compares expected and actual records, and investigates the merge keys rather than changing the visual.

After correcting the cause, both conditions need to be retested. The main region should still return the expected customer attributes. The regional office’s records should now behave according to the requirement.

The pre-merge and post-merge counts should be compared again, and the unmatched set should be reviewed again. A correction isn't established just because the previously failing record now looks right. It must preserve the behavior that was already correct.

The downstream consequences can be significant. If one transaction becomes several rows because its customer key matched several customer records, calculations based on the transformed sales rows can reflect that multiplication. A report total may then differ from the original sales population even though every attached region name appears plausible. The fault began in the query output, so later DAX or visual formatting isn't the right place to conceal it.

Data loss has a similar path. If a join excludes required unmatched transactions, those records aren't available to later model calculations or report interactions built from that query result. A consumer may see a polished report with no visible warning that part of the starting population disappeared. This is why validation must occur where the behavior happens. For a merge, the first evidence comes from the query output, its row counts, the key profiles, and the unmatched records.

That doesn't mean downstream testing is unnecessary. Once the query output is validated, the analyst should still check that the semantic model and report consume the prepared data as intended. The point is to avoid inferring query correctness from a report that happens to look reasonable.

Each layer answers a different question. The query check asks whether the intended records were joined correctly. A model or report check asks whether that prepared result produces the intended analytical behavior for the consumer.

Consider a troubleshooting case where the report works during an initial review but appears wrong after a later refresh. The analyst captures the expected and actual results under the failing data condition. Then the investigation returns to the controlling query step and profiles the current keys. Perhaps the refreshed customer population now contains duplicate identifiers, or the unmatched population has changed. The important method is to test the current inputs and outputs rather than assume that yesterday’s successful result guarantees today’s data has the same characteristics.

The analyst compares the sales row count before the merge with the count afterward. Next comes the number and nature of unmatched rows. Then the analyst checks whether repeated customer keys correspond to repeated output rows. Those observations can distinguish accidental multiplication from expected repeated sales at the original grain. They can also show whether a join has excluded records that the requirement said to preserve.

If the customer key remains unique and the sales count remains stable, but a report still shows an unexpected result, the merge is no longer the only reasonable suspect. The analyst can move downstream to inspect the semantic model, DAX result, and report filter state. That sequence prevents random troubleshooting. It uses evidence to decide whether the fault is in data preparation or somewhere that consumes the prepared data.

A familiar feature choice can still be wrong when it operates at the wrong layer. Writing a calculation to look up a customer attribute doesn't turn the underlying query into a verified merged table. Adjusting a relationship doesn't repair duplicated keys that already multiplied rows during query preparation. Changing a visual filter doesn't restore records that the chosen join removed from the query output. Restate the requirement, locate the first layer that owns the behavior, and test there.

You should also resist treating every duplicate or unmatched row as automatically invalid. The source profiles tell you what exists, not what the business meaning must be. Several rows per Customer ID might violate the intended customer grain, or they might represent a legitimate lower-level grain that needs a different key. An unmatched sale might expose bad data, or it might be an allowed transaction type. Correctness depends on reconciling the observed data with the declared business outcome.

This leads to a practical acceptance standard. The merge should use keys that represent the intended records. The join semantics should preserve and exclude rows according to the requirement. The resulting row count should be explainable from the starting grain and the key matches.

Unmatched records should be visible and understood. Representative normal and edge cases should behave as predicted. Only then does the configuration become supported by evidence.

Return to the original sales report. Seeing region and account category beside a transaction proves only that at least one match produced visible values. It doesn't prove that every required sale survived, that each sale appears once, or that unmatched records were handled correctly. Those claims require the pre-merge and post-merge comparison, the uniqueness profile of the keys, the observed match population, and the behavior of deliberately selected edge cases.

The result is a prepared query whose behavior the analyst can explain. Customer attributes are present because key values were matched in Power Query. The record population reflects a join chosen for a stated business reason.

Changes in row count are either absent or accounted for. Unmatched records aren't hidden behind a successful configuration message. That gives the semantic model and report a trustworthy prepared result to consume.

A reliable merge starts with a clear statement of what each output row should represent. From there, the keys and join semantics determine which records match, which records remain, and whether one starting row becomes several. That’s why visible customer attributes aren't enough to prove success. You need to profile the keys, compare the row population before and after the merge, and inspect unmatched and edge cases.

When those observations agree with the business requirement, you can explain both the transformation and its consequences. When they don’t, investigate the query output before trying to compensate in the model, a calculation, or a report. The essential habit is simple: choose the merge for a stated reason, then prove that it preserved the records and grain you intended.

Predict the row count and grain before merging two queries. What unmatched or duplicated rows would tell you that the join keys or join type are wrong? The matching episode reference provides the technical inventory and a concise lab checklist for this lesson. This lesson was produced by Bare Metal Cyber dot com.

Merging Queries to Enrich Data

headphones Listen Anywhere

More Options »
Broadcast by