Power BI: tag promotion adds 25s CPU to a 0.08s scan (Shared.Dataset Costs query) #2336

⚠️ Problem

src/power-bi/kql/Shared.Dataset/definition/tables/Costs.tmdl (L1939-1952) resolves each promoted tag with two array_index_of scans over a per-row normalised key array:

| extend keys = bag_keys(Tags)
| extend cleankeys = parse_json(replace_string(tolower(keys), ' ', ''))
| extend tag_Env = iff(array_index_of(cleankeys, tolower('Env')) < 0, '',
                       tostring(Tags[tostring(keys[toint(array_index_of(cleankeys, tolower('Env')))])]))

With the 11 default tags that is 22 array scans per row, on top of a serialise → lowercase → replace → parse_json round trip per row. Handle_mixed_case_tags and Trim_spaces_from_tags both default to true, so every deployment pays this on every query.

Measured on Costs_v1_2 (~28M rows, 9-month slice), ADX, each query returning an identical 106 rows:

The two first rows differ only by the tag block, so it isolates cleanly: tag promotion adds ~25.8 s of CPU to a 0.08 s scan (332x), and costs 37x more than a direct bag lookup producing the same result.

In Power BI this is paid per visual, per grain. On one report page, 34 of 74 DirectQuery calls returned ≤10 rows yet cost more than a second each — 47 s of the page's 67 s of ADX time — with DataReadDuration at 0-3 ms throughout.

🛠️ Solution

Resolve tags with a bag lookup instead of repeated array scans. Same output, same column names, no config change:

| mv-apply k = bag_keys(Tags) to typeof(string) on (
      summarize cleanTags = make_bag(pack(replace_string(tolower(k), ' ', ''), Tags[k]))
  )
| extend tag_Env = tostring(cleanTags['env']), ...
| project-away cleanTags

O(keys) per row instead of O(tags × keys).

If that doesn't hold up under measurement, there is a smaller guaranteed win in the current expression: array_index_of is called twice per tag (once in the iff test, once to index) where once would do.

I'm validating the first option on the dataset above and am happy to send a PR.

ℹ️ Additional context

• Equivalence checked: current vs proposed return identical groups and totals (106 groups, totals equal to the cent).
• Alternative considered: promote tags at ingestion via an update policy so query time pays nothing. Strictly better, but it changes the hub's data model and storage, so proposing the in-query fix first.
• Duplicate normalised keys (Env and env on the same resource) resolve to "first match" today and to "whichever make_bag keeps" under the proposal. Both unspecified — worth confirming that is acceptable.

🙋‍♀️ Ask for the community

1. Please vote this issue up (👍) if your tag queries are slow.
2. If you can share CPU numbers for the three queries above from your own hub, that would help size the impact across deployments.