ChatGPT for Power BI: catch a merge that multiplies your rows

This workflow uses ChatGPT to review a proposed transformation. It does not require a ChatGPT connector inside Power BI. You apply and inspect the accepted query in your own Power BI file; the conversation cannot establish what your actual refresh did.

Put the row meaning beside the sample

In this original fictional fixture, Sales has one row per sale:

sale_id,category_key,amount
S1,A,40
S2,B,60
S3,C,20

The proposed Categories lookup is supposed to have one label per category key:

category_key,label
A,Hardware
A,Hardware old
B,Services

That promise is already broken: A occurs twice. C has no lookup match. The source sales contain three rows totaling 120. Neither a missing label nor an extra lookup label authorizes changing those three sales.

The Power Query NestedJoin reference, checked October 24, 2026, describes matching key values into a nested table. The important review point is the number of nested matches before they become expanded rows.

Ask for the failure trace before a fix

Help review a Power BI Power Query merge. Do not edit or refresh files.

Sales: one row per sale_id; amount is the sale amount.
Categories: intended to contain exactly one label per category_key.
Join: Sales.category_key to Categories.category_key, left outer.
Required output: retain exactly one row for every source sale_id.
Tables: [PASTE PERMITTED SAMPLES AND THEIR DATA TYPES]

Before writing a final query, show:
1. Source row counts and sum of Sales.amount.
2. Lookup key counts, including repeated and missing keys.
3. Match count per sale_id before expansion.
4. Expected rows and amount after the proposed expansion.

Treat a repeated lookup key as an unresolved data rule. Do not choose
the first label, delete a sale, or silently aggregate sales to conceal it.
Keep unmatched sales visible and flag the missing label.
Propose a diagnostic M step and explain where it belongs. Distinguish
an illustrative expectation from a result run in my actual PBIX file.

OpenAI's prompting guidance recommends giving the context and boundaries that change the result. Here the critical boundary is one output row per sale. “Make the merge work” leaves that requirement unstated.

Inspect the nested matches

If Sales and Categories are existing typed query steps, this illustrative M diagnostic keeps the nested table and adds its match count:

let
    Joined = Table.NestedJoin(
        Sales, {"category_key"},
        Categories, {"category_key"},
        "CategoryMatches", JoinKind.LeftOuter
    ),
    WithMatchCount = Table.AddColumn(
        Joined, "match_count",
        each Table.RowCount([CategoryMatches]), Int64.Type
    )
in
    WithMatchCount

This is an authored diagnostic, not an executed Power BI result. It assumes the named tables and columns exist. In a copy of the actual query, inspect the rows with counts above one and counts equal to zero before expanding CategoryMatches.

For the supplied fixture, the expected counts are S1=2, S2=1 and S3=0. Expanding the two matches for S1 would repeat its amount of 40. A left-outer expansion retaining S3 therefore produces four rows totaling 160, rather than three rows totaling 120. These are fixture expectations calculated from the supplied rows.

To inspect the lookup independently, group Categories by category_key and count its rows. Microsoft's Table.Group documentation covers grouping and aggregation. A count of two for A identifies a key-cardinality conflict; it does not tell you which label is correct.

Resolve the lookup rule before accepting a refresh

Ask the lookup owner whether “Hardware old” is obsolete or whether the missing distinction belongs in a larger key, such as category plus effective date. Those lead to different models. Keeping the first encountered label without a rule makes the output depend on source ordering.

For a simple corrected lookup containing A=Hardware and B=Services only, the expected left-outer result retains three sale IDs and total 120, with C's label unresolved. Check both unique sale IDs and total rows: matching totals alone can hide offsetting mistakes.

Run the accepted steps in the actual Power BI copy and record the observed row counts, unmatched keys and amount total. If those differ from the fixture expectations, inspect the native steps and actual data types before adjusting the report visuals. The Power BI percentage guide addresses the separate DAX filter-context problem after the underlying rows are correct.