When Power BI Logic Belongs in SQL

Draft. An outline of something I worked out in practice and intend to write up properly.

What this will cover

Transformation logic tends to live where it was first needed. In Power BI that means Power Query, because that is where the person building the report was standing. It works, until the volume grows and the refresh starts failing on memory or the capacity bill arrives.

The intended shape of the article:

• Symptoms that point at the layer rather than the code: refresh duration that scales with history rather than with new data, memory failures, query folding that breaks partway down a chain, gateway timeouts, resource governance rejections
• How to trace what a model actually depends on — measures, calculated columns, queries and source tables — before moving anything
• The decision rule: does this transformation need the semantic model’s context, or is it just reshaping rows? Reshaping belongs upstream.
• Calculated columns versus measures versus a source column, and the storage cost of choosing wrong
• What legitimately stays in the model, so this does not become “move everything to SQL”
• Incremental refresh as a consequence of getting the layer right, not a substitute for it

Why it matters beyond speed

Logic in the semantic layer is hard to test, hard to reuse, and invisible to anything that is not Power BI. Moving it into SQL or Spark makes it something the rest of the platform can use — and something you can put a test around.