Excel to Power BI: How to Migrate Your Reports Step by Step
· Power BI
Can Power BI replace Excel? When to move, what transfers, and how to convert Excel dashboards to Power BI step by step, including rewriting formulas as DAX.
Excel is not the problem. Using Excel as a database, an ETL tool, a reporting server and a distribution system at the same time is the problem. Here is how to tell when that line has been crossed, how to move without stalling for six months, and exactly which buttons to press when you do.
Signals it is time to move
- The monthly pack takes more than a day of manual assembly.
- Two people produce different numbers for the same metric and both are defensible.
- Files are emailed as versions:
_final,_final_v2,_final_actual. - Row counts are pushing past a few hundred thousand and the file opens slowly.
- Different readers must see only their own region, customer or cost centre.
- Someone needs the numbers on a phone, on a Monday, before you are awake.
Fewer than two of those? Fix the Excel file instead — a proper three-layer workbook solves most of it at a fraction of the cost.
When not to migrate
The decision is not always yes, and the migrations that fail are usually the ones nobody argued against early.
- The output is a form or template that people fill in. Report consumers cannot type into a Power BI report. If the deliverable is data entry, it stays in Excel. The same applies to anything people annotate or highlight.
- The analysis is exploratory and one-off. If nobody will look at it again next week, a PivotTable is faster and nobody maintains a semantic model for a single question.
- The audience has no Power BI licence. Check the licensing position for every reader before you build, not after. A report nobody can open is worse than a spreadsheet everyone can.
- The logic is heavily VBA-dependent with no plan for it. There is no VBA runtime in Power BI. A half-migrated report is worse than the original file.
- You want a model without moving the data. This one surprises people. Live Connect lets Power BI model an Excel workbook in place — you get proper relationships, DAX measures and sharing while the file stays where it is. It is the right answer for a shared master workbook that many people still need to open.
If refresh latency rather than tooling is the problem, consider DirectQuery before a full migration. You keep the source where it is and Power BI queries it live. You trade some query speed and some DAX flexibility for a much smaller project.
What transfers and what does not
| Excel asset | Fate in Power BI |
|---|---|
| Power Query steps (M code) | Transfer almost as-is — same engine |
| Power Pivot model and DAX measures | Import directly |
| DAX calculated columns | Import with the model |
| Worksheet formulas | Rebuilt as DAX measures |
| Pivot tables | Recreated as Matrix or Table visuals |
| Conditional formatting | Supported, configured differently |
| VBA macros | Not supported — replaced by Power Query, Power Automate or Python |
| Named ranges | Only if selected explicitly in the import navigator |
| Chart layout | Rebuilt as report pages |
| Manual adjustment tabs | Need a real source: a mapping table or an entry app |
Grouped by how much work each one is:
- Migrates automatically — Power Query steps, the data model, measures, calculated columns.
- Manual rebuild — pivot tables, charts, conditional formatting rules. Mechanical work, low risk.
- Must be rewritten — cell formulas and VBA. This is the real migration, and it is where the time goes.
- Partial — named ranges, which do not reliably appear as queries.
That "manual adjustment tabs" row causes the most friction. Every Excel pack has a tab where someone types corrections. In Power BI it has to become a maintained table, otherwise the numbers cannot be reproduced.
Model first, visuals last
The single biggest quality difference is the model. Flat tables that felt fine in Excel become slow and ambiguous in Power BI.
- One fact table per business process, at the finest grain you will ever need.
- Dimension tables for date, customer, product, cost centre — one row each, integer keys.
- Single-direction relationships from dimension to fact. Bi-directional only with a written reason.
- A proper date table marked as a date table, covering full years.
- Business logic as measures, not calculated columns, wherever possible.
A basic time-comparison measure set:
Revenue = SUM( Sales[NetAmount] )
Revenue LY =
CALCULATE( [Revenue], SAMEPERIODLASTYEAR( 'Date'[Date] ) )
Revenue YoY % =
DIVIDE( [Revenue] - [Revenue LY], [Revenue LY] )
SAMEPERIODLASTYEAR assumes a contiguous, marked date table. If your date table has gaps or is not flagged as a date table, it silently returns wrong numbers — check this before you trust a variance figure.
Row-level security replaces the "one file per region" habit:
-- Role: Region Manager, applied on the Region table
[RegionEmail] = USERPRINCIPALNAME()
The mechanics, step by step
The strategy above is the hard part. This is the mechanical part.
1. Audit the workbook before you touch Power BI. Open Power Query Editor inside Excel and list every query. Note which ones hit external sources (SQL, SharePoint, APIs) and which read other sheets in the same file. Check the Data Model for relationships and measures. Delete dead queries and unused columns now — migration is a terrible time to discover a query that pointed at a folder someone deleted last year.
2. Import the workbook. In Power BI Desktop: Home → Get Data → More → File → Excel workbook, then pick the file. A navigator pane lists everything Power BI found: every sheet, named range, table and Power Query. Select the items that represent your loaded queries — not the raw sheet data behind them. Importing both gives you a duplicate of every table and a relationship conflict you will regret.
3. Check the Queries pane. Click Transform Data. Every query from the workbook should appear on the left. Run through them and resolve source errors. This step is where most migrations pause, and it is almost always file paths pointing at somebody's laptop.
4. Move off local file paths. Desktop tolerates C:\Reports\sales.xlsx because it runs on the machine that has the file. The Power BI service cannot. Rewrite the source:
// Before — works on one machine only
Excel.Workbook( File.Contents( "C:\Reports\sales.xlsx" ), null, true )
// After — the service can reach it
Excel.Workbook( SharePoint.Files( "https://yourorg.sharepoint.com/sites/FinanceData", [ApiVersion = 17] ), null, true )
OneDrive for Business is usually the lower-friction option for a team already using SharePoint. Note the third argument true — it defers type detection, which is usually what you want for large extracts.
5. Rebuild the visuals. Each pivot table becomes a Matrix or Table visual. Each chart becomes the matching native visual. The Fields pane is already populated, so this is the quickest part of the project. Cross-filtering between visuals is automatic and usually better than what you had.
6. Publish and configure refresh. Home → Publish, pick a workspace. Then in the Power BI service open the dataset settings and set a scheduled refresh. If the source is on-premises or in local files, install and configure the On-Premises Data Gateway first — refresh will fail without it, and the failure message is not helpful.
Rewriting cell logic in DAX
This is the part that makes migration a project rather than an afternoon. Excel holds logic in cells; Power BI holds it in measures. The translations are mechanical once you know them.
VLOOKUP becomes a relationship plus RELATED. Create the relationship in Model view, then the lookup is just a column reference:
Product Category = RELATED( Products[Category] )
When you cannot create a relationship, use LOOKUPVALUE instead. It is a legitimate DAX pattern, not a compromise, when the relationship would be one-to-many or would create an ambiguity:
Category Name =
LOOKUPVALUE( Products[Category], Products[ProductKey], Sales[ProductKey] )
SUMIF and COUNTIF become CALCULATE with a filter:
West Sales =
CALCULATE(
SUM( Sales[Amount] ),
Sales[Region] = "West"
)
COUNTIF is the same shape with COUNTROWS or COUNT over the filtered table. Resist the urge to translate every Excel formula one at a time — several of them are usually the same measure with a different filter, and one parameterised measure beats five near-identical ones.
Move all of this before the report goes live. A report with cell logic still sitting in an Excel tab behind it is not migrated, it is duplicated.
Common gotchas
Mixed data types in a column. Excel allows numbers and text to coexist. Power BI enforces one type per column and will throw a load error. Fix it in Power Query with an explicit type step rather than letting the column fail — this is the single most common import failure and the least obvious from the error message.
Named ranges do not appear as queries. They show up separately in the Get Data navigator, under the workbook node. Select them explicitly or they are silently dropped.
Local paths break the moment you publish. Covered above, but it is the most common reason a migrated report has no data.
VBA has no migration path. If macros prepare data or produce exports, every piece of that has to be rebuilt in Power Automate or Python. Plan it separately from the model migration and give it its own estimate.
Relationships do not always import the way you left them. Check cardinality and filter direction after import, especially any relationship that was bi-directional. A silently flipped filter direction changes every total in the report.
A migration sequence that works
- Pick one report, not the department. The one that costs the most manual effort.
- Agree definitions in writing. Revenue, margin, headcount, active customer. This is the part that saves the project.
- Connect to the source, not the export. If the Excel file is fed by a system, connect Power BI to that system.
- Build and validate the model. Reconcile totals against the existing pack to the penny before drawing a single chart.
- Rewrite the cell logic as measures. While you are validating, not after go-live.
- Rebuild the report pages. Same questions, native layout — do not photocopy the spreadsheet.
- Set refresh, security and alerts. Then run both old and new in parallel for one cycle.
- Retire the old file deliberately. If it stays open, people keep using it.
- Document and hand over. Measure descriptions, source lineage, and who owns each definition.
Repeat for the next report. Sequenced this way you get value in weeks and never carry a giant unfinished migration.
Pre-migration checklist
- All data transformations are inside Power Query, not in sheet cells or VBA
- KPI calculations exist as Power Pivot measures, not cell formulas
- Table relationships are defined in the Data Model, not just matched by VLOOKUP
- External data sources are reachable from the network, not only from local paths
- Unused queries and orphaned named ranges have been removed
- All VBA macros are documented and have a migration plan
- Every reader's Power BI licensing is confirmed
- The target workspace in the Power BI service exists
- The On-Premises Data Gateway is installed, if the source is on-premises
- Stakeholders know the new report URL and how their access works
What you gain on the other side
- Scheduled refresh — the report updates on a cadence without anyone opening a file.
- One live URL — share a link, not an attachment. There is no "which version is current" conversation any more.
- Row-level security — one report, different data per user, based on their login.
- Incremental refresh — for large datasets, only new rows are processed each cycle.
- A phone layout — designed once, published once, available to everyone.
- Reusable transformations — Power Query logic and dataflows shared across reports instead of copied.
- Built-in analysis — anomaly detection and key-influencers, without writing any of it.
Costs people forget
- Licensing for every reader, or a capacity if there are many of them.
- Gateway setup and ongoing maintenance for on-premises sources.
- Time from the person who knows the definitions — usually the scarcest resource.
- A small ongoing change budget after go-live.
Keep Excel where it wins
Analysts should still get Excel access to the model — connect a PivotTable to the Power BI dataset and ad-hoc analysis carries on against governed numbers. That combination, governed model plus familiar tool, is what makes the move stick culturally.
Where this ends up
Migrating is not a lift-and-shift. It is a deliberate move of the right pieces: Power Query and DAX measures travel almost intact, visuals need rebuilding but the rebuild is quick once the model is right, and cell logic is the actual work — and the opportunity to turn scattered formulas into reusable, documented measures.
The payoff is a reporting stack that scales: automatic refresh, one live URL for every stakeholder, and a model you can build the next report on top of without reopening the original file. For any report that gets opened, refreshed and shared on a recurring basis, that is a good trade.
Further reading: Power BI data modelling best practices and how to hire a Power BI consultant. If you want the first report migrated and documented, that is the Power BI service.
Related: Power BI and Python integration, EU financial regulations 2026 and the HTML bar chart template. Ready to migrate? Send your workbook details.