ENGINEERING

LLM enrichment in Power BI refreshes — without Fabric capacity

August 2026 · 4 min read

The Copilot/Fabric price floor

Power BI's built-in AI features — Copilot in Power BI, AI-generated visual summaries, natural-language Q&A — sit behind Microsoft Fabric capacity, and the tiers that unlock Copilot start at F64. That's a meaningful monthly commitment before a single report uses any AI feature at all, and it's shared capacity across everything else Fabric does in your tenant, not a line item you can attribute to "the one report that classifies support tickets."

If what you actually need is narrower — classify a text column, summarize a long free-text field, extract a structured value out of unstructured notes, at refresh time rather than interactively — you don't need Fabric capacity for that. Power Query already has an HTTP client built in, and it's enough to call any OpenAI-compatible endpoint during a normal refresh.

Web.Contents in Power Query

The M function is Web.Contents, wrapped in Json.Document to parse the response:

let
    Source = ExcelOrSqlSource,
    CallModel = (inputText as text) =>
        let
            body = Json.FromValue([
                model = "your-model",
                messages = {[
                    role = "user",
                    content = "Summarize in one sentence: " & inputText
                ]},
                max_tokens = 120
            ]),
            response = Json.Document(Web.Contents(
                "https://gateway.your-company.com/v1/chat/completions",
                [
                    Headers = [
                        #"Authorization" = "Bearer " & GatewayKey,
                        #"Content-Type" = "application/json"
                    ],
                    Content = body
                ]
            ))
        in
            response[choices]{0}[message][content],
    Enriched = Table.AddColumn(Source, "Summary", each CallModel([Notes]))
in
    Enriched

That's the pattern: one function wrapping the call, invoked per row — or better, per group, see below — with Table.AddColumn.

Four caveats that matter more than the code

This runs at refresh time, not query time. The model call happens when the dataset refreshes — scheduled or on-demand — not when someone opens the report or moves a slicer. There's no way to make this respond to a DAX measure or a slicer selection; it's a data-prep step, equivalent to a lookup table you refresh on a schedule, not a live inference call sitting behind a visual.

Don't call the model per row on a large table. A million-row refresh calling the model once per row is a million-token bill and a refresh that times out long before it finishes. Group first — dedupe on the distinct values that actually need enriching, or aggregate to the grain the summary or classification applies to — and call the model once per group instead of once per row. If the table keeps growing, move to incremental refresh so only new or changed rows go through enrichment on each run, not the full history every time.

Privacy levels and the on-premises gateway both have to allow the call. If this table already goes through the on-premises data gateway — typical for anything joining on-prem or VPN-only sources — that gateway needs network access to your endpoint, and the privacy level set for the data source needs to permit web calls. Power Query will silently refuse to combine a "Private" source with a "Public" one in the same query unless privacy levels are set to allow it, or ignored at the workspace level.

Put the key in a parameter, not in the M code. A text parameter (GatewayKey above) that you set per environment keeps the key out of the .pbix file itself and out of whatever gets committed if the report is source-controlled. Hardcoding it into the query text means it ships with every copy of the file, including the ones emailed around.

The concrete case: enrichment, not generation

The reports where this earns its keep are the ones with a free-text column nobody has time to read row by row — support ticket bodies, sales call notes, open-ended survey responses. A classification column added this way turns "1,200 rows of raw notes" into a "Category" field a slicer can filter on, without anyone hand-tagging the source data first. Because the enrichment happens once per refresh and gets cached in the model, downstream visuals, measures, and relationships behave exactly as if the column had always been there — there's no runtime dependency on the gateway once the refresh finishes.

That caching matters for cost control too. If the source table only appends new rows between refreshes, keep a merge step that reuses the enrichment computed on a previous run for rows that haven't changed, and only calls the model for genuinely new or edited rows. Power Query supports this with a straightforward anti-join against the last refresh's output before the enrichment step runs — cutting a scheduled refresh's token spend down to whatever changed since the last run, instead of the whole table every time.

Try it on ours

Our gateway (iSol API) is the Chat Completions endpoint behind that Web.Contents call — an alternative to paying for Fabric capacity just to unlock AI in a couple of columns. US$ 99/month, 14-day trial.

See plans →