Back to blog

Centralized control of data served to Power BI & Excel

Every BI team knows this one. You ship a clean Power BI report, and a week later operations is back in Excel. They aren't wrong. Excel is where they add up two cells, tack on a helper column, pivot something and get on with their day. The report is where they look. Excel is where they work.

The usual fix is "Export data" or "Analyze in Excel". Both work, and both leave you with no centralized control of what lands in Excel. Column names, column order, which columns at all: decided per user, per export, per mood. Six weeks later there are nine versions of "the tracker", and none of them match.

I went with centralized control of what the end-user gets as a "base" distribution dataset. One place holds the code, one semantic model holds the data, and there are two front doors: Power BI for reading, Excel for working. Both open on the same default view, and I control it.

The shape of it

The data on top, the recipe underneathDWH — PostgresSemantic model — FabricTwo front doors1 · read the DAX2 · run itAnalytical SQL viewson top of the DWHsql_source tableone row per page: SQLFlat tablesthe dataDAX_SOURCEone cell: raw DAXPower BI reportthe reading roomExcel · Power Querythe workshopcomputed where the data lives+ one more row: the DAXwritten in DBeaver, kept in gitpartitions fetch SQL from sql_sourcethe model carries its own recipe:names · column set · orderfilters · visuals · drill-throughno DAX and no column listin the workbook: a thin client

Three layers, one rule: the logic lives upstream, and everything else reads it.

1. SQL lives in a table, not in the model

Same pattern as in the Postgres + Power BI workflow: the analytical queries sit on top of the DWH, and their text lives in a plain table, sql_source, one row per report page, SQL in the _code column. The semantic model reads its partitions from there. It doesn't carry the SQL; it fetches it.

I write every query in DBeaver, wrap it in a session variable, run the file. The .sql files are in git, so every column change has a diff, an author and a date. Boring in the best way.

2. One extra row: the DAX that defines "the Excel view"

This is the part that makes the rest work. Next to the SQL rows, sql_source holds one more row whose _code isn't SQL at all. It's a raw DAX query:

EVALUATE
SELECTCOLUMNS (
    '_TRACKER_VIEW_'
    ,"PO #",            '_TRACKER_VIEW_'[_po_no]
    ,"Supplier",        '_TRACKER_VIEW_'[_supplier_name]
    ,"ETD",             '_TRACKER_VIEW_'[_ptd]
    ,"ETA",             '_TRACKER_VIEW_'[_pta]
    ,"Status",          '_TRACKER_VIEW_'[_status]
)
ORDER BY
    [PO #]
    ,[ETD]

It's saved exactly like the SQL: same file, same two blocks. Dollar quoting means no escaping, and the file stays pasteable:

set dev.tracker_dax =
$code$
 
    -- the DAX above, untouched
 
$code$;
 
update sql_source
set  _code    = current_setting('dev.tracker_dax')
    ,_updated = now()
where 1=1
    and _report = 'TRACKER'
    and _name   = 'DAX SRC';

The model loads that row as a one-row, one-column table, DAX_SOURCE[_code]. So the model carries its own recipe for the flat Excel view: business-friendly names, the column set, the order. Managed like every other query, from the same IDE, in the same repo.

3. Excel reads the recipe, then cooks with it

The workbook connects to the published model through its XMLA endpoint with AnalysisServices.Database. The Power Query makes two hops against the same model:

let
    _server   = "powerbi://api.powerbi.com/v1.0/myorg/<workspace>",
    _db       = "Tracker",
 
    // 1. read the raw DAX from the one-cell table
    _meta     = AnalysisServices.Database(_server, _db, [
        Query          = "EVALUATE SELECTCOLUMNS ( DAX_SOURCE, ""_dax"", DAX_SOURCE[_code] )",
        Implementation = "2.0"
    ]),
    _dax      = Text.Trim(Table.Column(_meta, Table.ColumnNames(_meta){0}){0}),
 
    // 2. run that DAX against the same model
    _src      = AnalysisServices.Database(_server, _db, [
        Query          = _dax,
        Implementation = "2.0"
    ])
in
    _src

Load it to a sheet as a table. That's it. No DAX in the workbook, no column list in the workbook. The workbook is a thin client.

What this buys

  • One source of truth for logic and layout. Add, drop, rename or reorder a column: one edit in one .sql file. After the next model refresh, the report and every copy of the workbook get the same view on their next refresh. Nobody emails a new template around.
  • Two UIs, one dataset. Power BI is the reading room: filters, visuals, drill-through. Excel is the workshop: pivots, custom columns, "let me just check something". Same numbers in both, because it's literally the same model.
  • Governed, not locked. Users still do whatever they like on top of the flat table. The baseline is shared and agreed; their extras are their own.
  • Git-backed everything. SQL and DAX sit side by side, reviewed and versioned the same way.

"Why not Analyze in Excel, or export the matrix?"

Fair question. Both are fine for a one-off. Neither lets you distribute a base view. With Analyze in Excel, every user builds their own pivot from the field list. With exports, every user gets a snapshot of whatever visual they clicked, row limits included. There is no central place that says "these 113 columns, named like this, in this order". Here, that place is a row in a table, and I own it.

Small print, learned the practical way

  • ORDER BY in a DAX query wants column references. ORDER BY [PO #] sorts. ORDER BY "PO #" sorts by a string constant, which is to say it doesn't sort at all, and nothing complains.
  • Native query approval. Excel asks users to approve native database queries. With a dynamic query the text can change, and the prompt comes back when it does. It's a per-user setting under Query Options → Security; decide your policy up front.
  • Strip the brackets. DAX results come back with headers like [Supplier]. Clean them in M, before anyone builds a pivot on top. Renaming them later breaks things.
  • Watch for ghost tables in the workbook's Data Model. A linked table built from the sheet under old headers keeps refreshing and complains that "field names are no longer valid". Delete it in Power Pivot.
  • One query per destination, not copies. If the same data goes to a sheet and to the Data Model, reference one query from the other. Don't paste the M twice. Copies drift.

Bottom line

Code in the DWH, data in the semantic model, two front doors on top. Power BI for people who read, Excel for people who work, one agreed default view behind both. It isn't fancy, and that's the point: the whole thing fits in a table row and a dozen lines of M, and it saves a lot of "which version is right?" conversations.

Related posts