Excel & Power BI for Finance: From Excel to Power BI: The Bridge

Download the AtlasParts dataset and the Episode 7 Power BI starter file — follow along in your own copy of Power BI Desktop: github.com/GoCelesteAI/excel-powerbi-for-finance.

We spent six episodes turning AtlasParts’ messy GL extracts into a robust Excel workbook. Tables, named ranges, dynamic arrays, Power Query for ETL, structured references everywhere. The workbook now survives — new month, new analyst, no #REF! errors. Season 1 done.

Then the CFO asks for three things in the same Monday meeting. Can the regional VPs get this dashboard on their phones? Can we refresh nightly without anyone clicking buttons? Can we filter by region and have every chart update at once?

Each of those is solvable in Excel. None of them is easy in Excel. All three together is the moment your workbook stops being the right tool.

Power BI is the right tool. Same data, same business logic, same DNA — but the engine underneath is a column-store database, the refresh is a scheduled service, and the consumption surface is a phone app. This episode is the bridge: when does Excel stop being enough, what does Power BI actually do differently, and how do we move the AtlasParts model across without throwing away what we built.

When Excel stops being enough

Excel is the right tool for a lot of finance work. Ad-hoc analysis on small extracts. One-time reconciliations. Models that one or two analysts maintain together. Anything where the audience is “the people who built the file.”

It stops being the right tool when one of three things happens.

The data is too big to live in a single workbook. The 1,048,576-row sheet limit is the headline number, but the practical limit hits earlier. AtlasParts’ GL has 4 million journal lines a year. Even if you could fit them, the SUMIFS over a million rows takes ten seconds per recalc. The file is 200MB. Email won’t carry it. SharePoint chokes on it. Power Query can pull a lot, but at some point you’re trying to do database work in a calculator.

The audience is bigger than the authors. Twelve regional VPs need to see the same revenue dashboard. They want it filtered to their region. They don’t want to learn Excel slicers. They want to look at it on a phone. Excel’s sharing model — send a file, hope they don’t break it — was designed for a smaller world.

The refresh has to happen without you. The CFO needs the dashboard updated by 7am every Monday. You don’t want to be the person clicking “Refresh All” at 6:30am every Monday. Excel’s automation story is macros and Power Automate workarounds. Power BI’s automation story is “set a schedule, walk away.”

If none of those are problems, stay in Excel. The conversion cost isn’t worth it. If one of them is a problem, Power BI was built for exactly that gap.

What Power BI actually is

Power BI is three things that share a name.

Power BI Desktop is the authoring tool — a Windows application where you build the data model, write DAX measures, and design report pages. This is where finance analysts spend their time. It’s free.

Power BI Service is the cloud platform — app.powerbi.com — where reports get published, refresh schedules run, and consumers (your VPs) view the dashboards in a browser. This is where IT and the data team spend their time. Free for individual use; paid per user beyond that, with a higher tier for premium capacity.

Power BI Mobile is the iOS / Android app that consumers use to look at reports on their phones. Same dashboards, optimized for small screens. Comes with the Service.

For finance, the mental model is: I author in Desktop, I publish to Service, my consumers read from Service or Mobile. The skills you need are 90% Desktop. This series teaches Desktop.

The three engines under the hood

A Power BI file is a .pbix file — a single document, like a .xlsx, but the contents are wildly different. Three engines are bundled inside.

Power Query (M). The same Power Query you used in Episode 5 of Season 1, here at the front of every Power BI report. It loads data from sources (SQL, Excel, CSV, web, APIs), shapes it (filter, group, pivot, merge), and lands it as Tables in the data model. The language is M; the editor is identical to Excel’s. The skill transfers one-for-one.

The Vertipaq engine — the columnar in-memory database. This is the part that has no Excel analog. When Power BI loads your data, it stores it column-by-column in compressed memory. A column with 4 million rows but only 200 distinct customer IDs takes maybe 2MB. Queries against this engine return in milliseconds even on massive Tables. This is why a .pbix file with 4M GL rows is 30MB, not 200MB.

DAX — the formula language. DAX (Data Analysis Expressions) is to Power BI what formulas are to Excel. You write Total Revenue = SUM(Sales[Revenue]), and that’s a measure that recalculates instantly across whatever filters the user applies on the report. DAX has the same goal as =SUMIFS() but a different shape, because it operates over the column-store and over filter context, not over cell ranges. We’ll spend Episode 4 on DAX fundamentals.

When you open Power BI Desktop, all three engines are running silently in the background. You author a Power Query M expression in one window, define a DAX measure in another, and visualize it on a report canvas. The pipeline is: M loads → Vertipaq stores → DAX queries → visuals render.

A tour of Power BI Desktop

Open Power BI Desktop and you get a window with a specific shape. Five things on the screen, each doing one job.

The ribbon at the top. Tabs across the top: Home, Insert, Modeling, View, Optimize, Help. Power BI’s ribbon mirrors Office’s, but the contents are BI-specific — Get data instead of Open, New measure instead of Insert function, Manage relationships instead of anything in Excel.

The view-switcher rail on the left. Three icons stacked vertically: Report, Data, Model. These are the three lenses you can put on the same .pbix file. Report view is what your audience sees — pages with charts and KPI cards. Data view is the row-level data, like Excel’s grid but read-only. Model view is the relationship diagram between your Tables.

The report canvas in the middle. A blank page where you drag fields to build visuals. Each visual is a tile — a column chart, a card, a slicer, a table — sized and positioned by you. Pages stack like sheets in Excel; each report can have many pages.

The Visualizations pane on the right. Sixteen-ish visual types laid out as a grid of icons: stacked bar, clustered column, line, area, pie, donut, card, KPI, table, matrix, slicer, map, gauge, scatter. Click an icon to insert that visual on the canvas, then drag fields onto its “wells” — X-axis, Y-axis, Legend, Tooltips — to bind data.

The Data pane on the far right. A collapsible tree of every Table in your model and every column or measure inside each Table. Calendar, Customers, Sales, Products. Expand Sales and you see OrderDate, Revenue, Total Revenue (with a ∑ icon, marking it as a measure), YoY Revenue %. Drag any of these onto a visual or into the Visualizations pane’s wells.

That’s it. Five pieces. Master those and you can build any Power BI report.

The same data, a new shape

The AtlasParts data we used in Season 1 — gl_journal.csv, customers.csv, products.csv, monthly_budget.csv — works in Power BI without modification. Same files, same columns, same row counts. What changes is how we organize them inside Power BI.

In Excel, we kept everything as parallel Tables linked by VLOOKUP / XLOOKUP. The GL had a CustomerID column; we’d lookup the customer name from the Customers table when building a report. That works for moderate-size data but doesn’t scale, and every aggregation re-runs the lookup.

In Power BI, we build a star schema. One central fact Table — Sales — holds every transaction with foreign keys (CustomerID, ProductID, DateKey, RegionID). Around it, smaller dimension Tables — Customers, Products, Calendar, Regions — hold the descriptive attributes. We define relationships between them in Model view. Now when you put Calendar.Month on a chart’s X-axis and Sales.Revenue on its Y-axis, Power BI follows the relationship automatically. No VLOOKUP required.

Star schemas are the central modeling pattern of Power BI, and they’re the topic of Episode 3. For now, the takeaway is: the data files are the same, but Power BI wants them organized in this specific shape, and once they are, every visual just works.

What changes from Excel to Power BI

The conversion is more than a file format. Five things change practically.

Refresh becomes scheduled. In Excel you click Refresh All to re-pull from sources. In Power BI Service, you publish the report once, set up a refresh schedule (every weekday at 6am), and the data updates automatically. Consumers always see fresh numbers without anyone clicking anything.

Sharing becomes link-based. In Excel you send the file. In Power BI you publish to Service, share a link or grant access in a workspace. Consumers don’t get a file; they get a live view. They can’t accidentally break a formula.

Filters become global and interactive. A slicer on a Power BI page filters every visual on that page at once. Click a bar in a chart, and every other chart on the page filters to the selection. This is called cross-filtering and it’s the single biggest UX upgrade over Excel pivot tables.

Calculations become measures, not cells. In Excel, totals live in cells. In Power BI, they live as DAX measures inside the model — once defined, they show up correctly under whatever filter context the user applies. Define Total Revenue once; it works on the company total, the region detail, and the per-product breakdown without rewriting.

Volume becomes a non-issue. A Power BI model with 50 million rows runs faster than an Excel workbook with 50 thousand. Vertipaq is built for that scale. The bottleneck shifts from “can the calculator hold this” to “how do we model this cleanly.”

The skills that don’t change: Power Query (identical), the discipline of clean source data, the importance of named-and-typed columns, the value of a Calendar Table for time-based reporting. Everything we built in Season 1 carries forward.

Setting up Power BI Desktop

Power BI Desktop is free and Windows-only. Get it from the Microsoft Store (auto-updates) or directly from powerbi.microsoft.com/desktop. Mac and Linux users use a Windows VM, the web-based authoring in Service (limited), or a remote Windows desktop.

First-launch defaults to know about. Under File → Options → Data Load, turn off Auto date/time — it creates a hidden calendar table for every date column and bloats the model. We’ll bring our own Calendar Table in Episode 5. Under Preview features, leave defaults; the previews change frequently and aren’t part of this series.

Sign in with the same Microsoft account you use for Office, or a Power BI-specific account if your IT team set one up. Sign-in is required to publish to Service but not to author in Desktop.

The S2 roadmap

Six episodes ahead, building the AtlasParts finance dashboard end-to-end:

Episode 2 — Power Query at scale. The same Power Query, applied to bigger and messier sources. Joining files, parameterizing connection strings, error handling. The ETL layer that feeds every report.

Episode 3 — Star schema for finance. The fact-and-dimension model that makes Power BI fast. AtlasParts as a star: Sales fact, Customers / Products / Calendar / Regions dimensions. Relationships, cardinality, filter direction.

Episode 4 — DAX fundamentals. The formula language. Measures vs calculated columns. The CALCULATE function. Filter context. The patterns finance analysts actually use.

Episode 5 — Time intelligence. YTD, MTD, prior-period comparisons, variance to budget. The Calendar Table earns its keep here.

Episode 6 — Finance dashboard end-to-end. Putting it all together. AtlasParts CFO board pack as a Power BI report — KPIs, trend charts, region drill-downs, mobile layout, scheduled refresh. The Season 2 finale.

By the end you’ll have a working Power BI dashboard that reads from the same AtlasParts data we’ve been using all season, refreshes on a schedule, and answers the questions Excel couldn’t.

What to do before Episode 2

Two things will make Episode 2 land faster.

Install Power BI Desktop and open it once. Get past the first-run dialogs, the sign-in flow, the “import sample” prompt. Five minutes of just letting it open and load.

Re-pull the AtlasParts dataset from the GitHub repo. Same files we used in Season 1. We’ll start Episode 2 by Get data → Folder → AtlasParts/ and pulling in everything at once. Knowing where the files live saves time on the next episode.

That’s it. No new spreadsheet skills, no new formulas, no DAX yet. Episode 2 is purely Power Query — the part you already know — applied to a Power BI report instead of an Excel workbook. We’ll build on familiar ground before introducing the parts that are new.

Same data. New tool. Bigger ambition. Let’s go.