How to Migrate Excel and Google Sheets Reports to Power BI: One Data Model, Scheduled Refresh and Numbers That Reconcile

"Every Monday I update the spreadsheet, check the totals by hand and email it to six people. Last week two of them had different numbers."

Almost every Power BI project starts from a report that already exists, and a surprising number of those reports are a spreadsheet. Not a bad spreadsheet: it was built by someone smart, it answers a real question, and the business has trusted it for years. That is exactly why moving it is risky. If the new report is even slightly off, nobody will care that it is prettier.

This article covers how we move recurring Excel and Google Sheets reports to Power BI: inventory first, then a data model, then Power Query to consolidate the files, scheduled refresh to kill the Monday email, and a reconciliation step that proves the totals match before anyone switches over. It ends with a checklist you can copy.

Why "the report is a spreadsheet" breaks

The problems are rarely the spreadsheet itself. They are what happens around it as it grows:

• Formulas nobody understands. A SUMIFS with six conditions, written by someone who left two years ago, nested inside an INDEX/MATCH. It works, so no one touches it, so no one can change it.
• The wrong version of the file. Sales_v2_final.xlsx, Sales_v2_final_FIXED.xlsx, and the copy on someone's desktop. Two people quote two different totals and both are "right".
• Consolidation by hand. Four regional managers send four files. Someone copies the tabs into one workbook, fixes the column that moved, and repeats it next month.
• No history and no scale. Last month's numbers are overwritten, and the file opens slower every quarter.

Power BI fixes these only if you change the shape of the work, not just the tool. The goal is a single data model that reads the files, owns the logic in measures anyone can inspect, and refreshes on a schedule.

Step 1: Inventory what you have

Before opening Power BI Desktop, list every workbook behind the report. For each one you want to know the tabs, which are hidden, how many formulas they hold, whether cells are merged (merged cells are the enemy of a clean table), and whether the file links to other files. A short script saves you from clicking through every tab:

import sys
from pathlib import Path
from openpyxl import load_workbook

for path in sorted(Path(sys.argv[1]).glob("*.xlsx")):
    wb = load_workbook(path)  # formulas, not cached values
    print(f"\n{path.name}  ({len(wb._external_links)} external links)")
    for ws in wb.worksheets:
        formulas = sum(1 for row in ws.iter_rows() for c in row if c.data_type == "f")
        print(
            f"  {ws.title:<20} state={ws.sheet_state:<7} "
            f"used={ws.dimensions:<10} formulas={formulas:<5} "
            f"merged={len(ws.merged_cells.ranges):<3} tables={len(ws.tables)}"
        )

Then classify every tab into one of four types. This is the core of the migration plan:

Tab type	What it is	Where it goes in Power BI
Data	One row per transaction, order, hour or invoice	Fact table
Lookup	A list of regions, products, cost centers, with attributes	Dimension table
Calculation	Subtotals, margins, running totals, percentages	Measures (never loaded as data)
Presentation	The formatted summary people actually read	A report page (matrix and cards)

Also write down every manual override: a cell where someone typed a number over a formula. These are the most dangerous items in the file, because the logic lives in someone's head. Each one becomes either a documented rule or a row in an adjustments table with a reason, an owner and a date.

Step 2: Map the tabs to facts and dimensions

Take a typical case: four regional managers each send a workbook with a "Sales" tab (one row per product, one column per month, a totals row at the bottom) and a "Lists" tab with product names. In Power BI this becomes:

• Sales (fact): one row per file, region, product and month, with a numeric Amount.
• Date (dimension): a real calendar table. A month stored as the text "Jan" cannot sort or drive year-to-date, so turn it into the first day of the month.
• Product and Region (dimensions): built from the lookup lists, not from whatever values happen to appear in the data.

If you want the longer version of the star-schema argument, we wrote it up in our multi-source data warehouse article. The short version: facts at the bottom, dimensions around them, relationships one-to-many, and measures that never care which file a row came from.

Excel formulas do not move one by one. They change form:

In the spreadsheet	In Power BI
SUMIFS(Amount, Region, "North")	A visual or slicer filtered by region. If you need it explicitly: CALCULATE ( SUM ( Sales[Amount] ), Region[Region] = "North" )
VLOOKUP / XLOOKUP into the "Lists" tab	A relationship to a dimension table, not a formula
The totals and subtotals rows	The matrix visual calculates its own totals. Never load them as data
=C5/C$20 (share of total)	DIVIDE ( [Sales], CALCULATE ( [Sales], ALL ( Region ) ) )
=SUM(B$2:B2) (running total, year to date)	TOTALYTD ( [Sales], 'Date'[Date] )
IFERROR(a/b, 0)	DIVIDE ( a, b ). It returns blank on a zero denominator, which is usually what you want. Pass a third argument only if the business really wants a 0
A number typed over a formula	A row in an adjustments table, with reason, owner and date

A rule that saves a lot of rework: row-level math goes in Power Query or in the source, ratios and totals go in measures. A margin percentage stored in a column will be averaged incorrectly the first time someone puts it in a total.

Step 3: Consolidate the files with Power Query

Power BI can read a folder and stack every file in it, which replaces the copy-and-paste routine. The wizard does it in three clicks (Get Data → Folder → Combine & Transform), but it builds everything from one sample file, so it is only as good as that file. We write the transformation as a function and control each step ourselves.

The function reads one file. It keeps the sheet name explicit, so a renamed tab makes the refresh fail loudly instead of silently loading nothing. It skips the title block above the header, removes blank rows, and unpivots the month columns into rows:

// fnReadSales
(FileContent as binary) as table =>
let
    Workbook   = Excel.Workbook(FileContent, null, true),
    Sheet      = Table.SelectRows(Workbook, each [Kind] = "Sheet" and [Name] = "Sales"){0}[Data],
    BelowTitle = Table.Skip(Sheet, 3),
    Promoted   = Table.PromoteHeaders(BelowTitle, [PromoteAllScalars = true]),
    Renamed    = Table.RenameColumns(
                     Promoted,
                     {{"Rgn", "Region"}, {"Product Name", "Product"}},
                     MissingField.Ignore
                 ),
    NoBlanks   = Table.SelectRows(
                     Renamed,
                     each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))
                 ),
    Filled     = Table.FillDown(NoBlanks, {"Region"}),
    Unpivoted  = Table.UnpivotOtherColumns(Filled, {"Region", "Product"}, "Month", "Amount"),
    Typed      = Table.TransformColumnTypes(Unpivoted, {{"Amount", type number}}, "en-US")
in
    Typed

Three details worth copying:

• Table.UnpivotOtherColumns unpivots every column except the ones you name, so when the file gets a new month column, the new month flows into the model with no edit.
• MissingField.Ignore on the rename lets the same function handle the file where someone typed "Rgn" and the file where they did not.
• The culture argument ("en-US") decides how text becomes numbers and dates. This is where 1,234.50 versus 1.234,50 and 03/04 meaning March or April go wrong. Set it deliberately.

The query lists the folder and applies the function:

let
    Source  = Folder.Files("\\fileserver\finance\sales-by-region"),
    Files   = Table.SelectRows(Source, each [Extension] = ".xlsx" and not Text.StartsWith([Name], "~$")),
    Loaded  = Table.AddColumn(Files, "Data", each fnReadSales([Content])),
    Slim    = Table.SelectColumns(Loaded, {"Name", "Date modified", "Data"}),
    Rows    = Table.ExpandTableColumn(Slim, "Data", {"Region", "Product", "Month", "Amount"}),
    Final   = Table.RenameColumns(Rows, {{"Name", "SourceFile"}})
in
    Final

The ~$ filter skips the temporary lock file Excel creates while someone has the workbook open. Without it, the refresh fails every time an analyst is working in the folder. And the SourceFile column is not optional: it is what lets you trace any number in the report back to the file it came from, and it is what makes the reconciliation in Step 5 possible.

If the files live in SharePoint or OneDrive, use SharePoint.Files instead of Folder.Files and filter by folder path. For Google Sheets, Power BI has a native connector (Get Data → Google Sheets, import mode, one connection per sheet URL, signed in with a Google account). That is fine for one or two sheets. For a dozen, a single folder of exported files is easier to manage than a dozen connections, each one authenticated separately.

Step 4: Replace "update and email" with a scheduled refresh

The Monday routine, "update the file, then email it", has two jobs hidden inside it: refreshing the data and distributing the result. Power BI does both separately.

Refresh. Publish the dataset to a workspace and schedule the refresh for a time after the files are due. Where the files sit decides whether you need a gateway:

• SharePoint or OneDrive for Business: the service reads them directly. No gateway.
• A file server or a local folder: you need an on-premises data gateway on a machine that can reach the path.

A Pro workspace can schedule up to 8 refreshes a day and Premium, PPU and Fabric capacities up to 48, so "daily at 07:30" is nowhere near the limit. Turn on failure notifications and send them to a named person. A refresh that fails quietly is how a dashboard shows last month's numbers with this month's title.

Distribution. Instead of attaching a file, publish the report to a workspace or an app and give people access. Where someone really wants an email, Power BI email subscriptions send a snapshot of a report page with a link on a schedule, so the Monday email still exists, but it is generated from the model, not typed.

One change comes with this: in a spreadsheet, everyone who received the file saw everything in it. In Power BI, access is managed by workspace, app and row-level security, so decide who should see which region before you share the link.

Finally, keep an eye on the inputs. Reading Excel is a bridge, not the destination. The best improvement after go-live is a standard template with a named Excel Table and fixed headers, or moving the entry to a SharePoint list or a form. We showed that pattern in this SharePoint and Power Automate project dashboard.

Step 5: Prove the numbers match

This is the step that decides whether the migration is accepted. Nobody will use a report they cannot verify, and "it looks about right" does not survive the first question from finance. Plan the validation like a small project of its own.

Define "match" first. Ask the report owner which total is the reference (the one the business quotes), and what tolerance is acceptable: zero for row counts, a few cents for money.

Use the old totals as the control. Remember the totals row we removed from the fact table? It is the best test you have. Load it into a separate query, SalesControl, from the same function, keeping only the rows we dropped:

// in the Sales query: keep detail rows only
Detail  = Table.SelectRows(Final, each not Text.StartsWith([Region], "Total"))

// in the SalesControl query: keep only the totals rows from each file
Control = Table.SelectRows(Final, each Text.StartsWith([Region], "Total"))

Add a small File table with one row per file, relate it to both Sales and SalesControl (and relate both to the Date table), then build four measures:

Loaded Amount  = SUM ( Sales[Amount] )
Control Amount = SUM ( SalesControl[Amount] )
Difference     = [Loaded Amount] - [Control Amount]
Check          = IF ( ABS ( [Difference] ) < 0.005, "OK", "CHECK" )

Put them in a matrix with file and month on the rows. A good reconciliation page has four things: the difference per file and month, a count of rows loaded per file, a list of expected files that did not arrive, and a count of rows that failed type conversion (in Power Query, Table.SelectRowsWithErrors shows them).

When the numbers differ, the spreadsheet is not automatically right. Sometimes the old totals row has a SUM range that stopped one row short, and the migration just exposed a bug that had been there for a year. Decide which side is correct, and write it down. Most differences come from a short list:

Symptom	Likely cause
Power BI is higher than the control	Two versions of the same period in the folder, or a subtotal loaded as data
Power BI is lower than the control	Rows with errors dropped, numbers stored as text, blank key columns filtered out, or a hidden sheet
The year matches but the months do not	Day and month swapped in dates (dd/mm read as mm/dd)
A small, constant difference	Rounding in the old workbook, or a manual override that was never documented
Differs only in one file	A moved or renamed column, caught by MissingField.Ignore and now empty

Run both reports in parallel. For at least one full closing cycle, preferably two, the old spreadsheet and the Power BI report run side by side, with the reconciliation page visible to the owner. Set the exit criterion in advance, for example: zero unexplained differences in two consecutive closes. When you reach it, announce a date, then make the old file read-only and move it to an archive. If both stay alive, you have rebuilt the original problem with two sources of truth.

Keep the reconciliation page after go-live. It becomes the data quality page of the report, and it is the first place to look when someone says "this number looks wrong".

Migration checklist

Before you start

1. List every workbook, tab, hidden sheet, external link and manual override.
2. Classify each tab: data, lookup, calculation or presentation.
3. Agree with the report owner on the reference total and the tolerance.
4. Name an owner for the input files.

Model

5. Define the fact grain (one row per what?) and write it down.
6. Build dimensions from master lists. Check the fact for keys missing from them (a left-anti merge in Power Query shows them).
7. Create a real date table. Convert month names into dates.
8. Move row-level math to Power Query or the source. Move ratios and totals to measures.
9. Replace every manual override with a documented rule or an adjustments table.

Power Query

10. One function reads one file, with an explicit sheet or table name.
11. Skip the title block, promote headers, remove blank rows, fill down merged cells.
12. Keep the totals rows aside as a control query. Do not load them as data.
13. Unpivot month columns. Set types with an explicit culture.
14. Filter out ~$ lock files and add a SourceFile column.

Refresh and sharing

15. Decide the gateway question: SharePoint (no gateway) or file server (gateway).
16. Schedule the refresh after the files are due. Send failure alerts to a named person.
17. Publish to a workspace or an app. Decide access and row-level security by region.
18. Replace the Monday email with a subscription or a link.

Validation and go-live

19. Build the reconciliation page: difference, row counts, missing files, conversion errors.
20. Investigate and document every difference, even if it turns out to be a bug in the old file.
21. Run in parallel until the exit criterion is met.
22. Archive the old workbook as read-only and announce the date.

Common pitfalls

1. Fixing only the sample file query. If you use Combine & Transform, changes belong in the generated function, not in the sample. Fixing the sample only fixes the one file you are looking at.
2. Referring to ranges instead of names. A reference to Sheet1!A4:F200 breaks the day someone inserts a row. Use a named Excel Table when you control the template, and an explicit sheet name when you do not.
3. Letting Date modified stand in for the period. A file that someone re-saves looks newer than it is. Take the period from the file name, a cell, or the data itself.
4. Rebuilding the spreadsheet layout and nothing else. A matrix with twelve month columns and forty rows is fine as one page, but it is the spreadsheet again. Add the trend, the comparison and the drill-down that the spreadsheet could not give.
5. Skipping the parallel run. The migration looks finished on the day the report is published. It is finished two closes later, when finance has stopped checking it.
6. No owner for the inputs. The first time someone adds a column, Monday's refresh fails. Someone needs to own the template and receive the failure alert.
7. Keeping both versions alive. An old workbook still circulating by email will outlive the migration by years unless it is archived on a published date.

---

Have a report that lives in spreadsheets and want it in Power BI without the numbers changing? This is our day job. See how we work on our Power BI consulting page.