The Hidden Truth About Generating Excel Reports That Every Data Analyst Needs to Know

One of the more practically useful announcements to come out of Fabcon never made a headline: Fabric Dataflows Gen2 can now write Excel files as an output destination, with enough say over the layout that the result is a simple formatted report rather than a flat data dump. The feature surfaced halfway through a roundup post on new Dataflows Gen2 capabilities, which is presumably why so few people noticed it. For anyone running finance reporting on Fabric, it is worth ten minutes of attention.

Excel is still the delivery format, whether we like it or not

Self-service BI programmes tend to treat the dashboard as the destination. Finance reality disagrees. Month-end packs get annotated in workbooks, auditors ask for extracts they can filter themselves, reconciliation breaks get worked line by line in a grid, and there is always one senior stakeholder who wants a file, not a link. The request 'can you send that as Excel' tends to outlive every dashboard initiative that was supposed to retire it. When I built a global expense dashboard for 50+ users at Morgan Stanley, the Power BI layer did its job, but the operation around it still processed 600+ invoices a month arriving as PDFs and Excel files; the spreadsheet never left the building.

The announcement also signals something broader, which the original write-up calls out explicitly: Dataflows Gen2 has grown from a way of getting data into Fabric into a proper self-service ETL tool. Many sources in, Power Query in the middle, and destinations both inside Fabric and outside it, including CSV files, Snowflake, and now Excel.

How the Excel destination actually works

The mechanics are pleasantly mundane. You build your queries as usual, then add one final query that returns what the documentation calls a navigation table: one row per element you want in the workbook. Each row names the target sheet, the element, the part type (a cell range, a formatted Excel table, or a chart), a record of properties such as the start cell or table style, and the data to place there. You then set that query's data destination to create a new Excel file in the Files section of a lakehouse and choose the advanced format option, which tells Fabric to use the navigation table as the blueprint for the workbook. Refresh the dataflow, download the file from the lakehouse, done. The documentation for the destination is unusually thorough.

The example that introduced the feature placed a report title in one cell range, a short AI-generated summary below it, a formatted sales table under that, and a column chart bound to the table: small, but recognisably a report rather than a data dump.

What a month-end version could look like

Translating that into finance terms: imagine a controller who wants a variance snapshot written into the lakehouse after every close, ready to attach to the close documentation. Adapted and generalised from the documented format (illustrative, not production code), the output query could look like this:

let
    // One row per element of the workbook
    workbook = #table(
        type table [
            Sheet = nullable text,
            Name = nullable text,
            PartType = nullable text,
            Properties = nullable record,
            Data = any
        ],
        {
            // Report header written to a fixed cell
            {"Close", "Header", "Range",
                [StartCell = "B1", SkipHeader = true], ReportHeader},
            // Variance by cost centre as a formatted Excel table
            {"Close", "VarianceTable", "Table",
                [StartCell = "B4", TableStyle = "TableStyleMedium2"],
                CostCentreVariance},
            // Chart bound to the table above via its name
            {"Close", "VarianceChart", "Chart",
                [ChartType = "Column",
                 ChartTitle = "Variance vs budget by cost centre",
                 DataSeries = [AxisColumns = {"CostCentre"},
                               ValueColumns = {"Variance"}]],
                #table({}, {}) meta [Name = "VarianceTable"]}
        }
    )
in
    workbook

The chart row is the detail worth noticing: it carries no data of its own. It points at the named table through metadata, so the chart in the finished workbook is driven by the table Fabric writes.

The AI summary deserves a pause

The original example includes a query that passes the data to the FabricAI.Prompt M function, asks for a short text summary, and places the result in the workbook as its own range. As a demo it is neat. In a finance document it needs a second thought: variance commentary in a close pack is an assertion somebody signs off, and an LLM-written paragraph sitting inside an otherwise deterministic artifact mixes two levels of assurance in one file. Having contributed to the design and testing of a firmwide document-query RAG system for finance at Morgan Stanley, I'm not arguing against language models in finance workflows; the place they have earned is assistant with a reviewer, not unsupervised author of commentary that travels under the team's name. Generate the draft summary by all means, but route it past a human before the workbook becomes the record.

Dataflows Gen2 or a paginated report?

The write-up raises this question directly and answers it honestly with 'not sure', especially now that Power Query is available in paginated reports too. The assessment, in short:

Two factors the write-up explicitly leaves undecided are capacity (CU) cost and how long each approach takes to generate a file. Both are measurable in your own tenant, and for a recurring month-end artifact both matter more than authoring comfort. Run the comparison before standardising on either.

Where I'd start

If your team currently exports a recurring workbook by hand (a reconciliation extract, or a monthly snapshot for a stakeholder who is never going to open a dashboard), that is the candidate. Rebuild it as a dataflow with the Excel destination and write it to the lakehouse Files area, so the artifact lives in governed storage with a refresh history instead of in mailboxes. Two caveats carry over from the write-up: you do not get full control over the file's formatting, and the approach is code-heavy, although the author's view is that with AI assistance few people will be writing navigation tables by hand anyway.

The full worked example, including the AI-generated summary and the chart definition, is in Chris Webb's Generating Excel reports with Fabric Dataflows Gen2, and the destination's documentation goes deeper still.

The hidden truth promised in the title is an unglamorous one: among the most quietly useful things Fabric announced at Fabcon is a feature that writes Excel files.