Moving Excel reports to Power BI: why and how

Why companies move from Excel to Power BI

The problem is usually not Excel itself but the way reports are built in it. Every month an analyst takes the accounting or sales export, deletes some columns, aligns the codes, pastes the data into the master file and refreshes the pivot tables. It takes days, every step can introduce errors, and when that analyst is on leave, the report does not get made.

• The monthly report takes several working days.
• Two reports on the same subject show different numbers.
• The file is so heavy it takes minutes to open, or crashes.
• Different versions of the same file circulate by email and chat.
• Every new question from management waits for a new report.

If two or more of these sound familiar, it is time to think about automating your reports.

Excel vs. Power BI

Power BI does not replace everything Excel does. The two tools are built for different jobs and in most companies they work side by side:

The general rule: recurring reports that several people need go to Power BI; ad-hoc analysis, budgeting and scenario modelling stay in Excel. The good news is that Power BI can hand its data to Excel too, so Excel users lose nothing.

A seven-step migration roadmap

This is the order I follow in migration projects, and it has caused the least trouble so far:

• List the reports: every current report, who uses it, how often it is made and how long it takes.
• Prioritise: move first the report that takes the most time and has the widest audience, usually the monthly sales or profit and loss report.
• Find the original source: for each number, identify the system it comes from. The aim is for Power BI to read from the source, not from an intermediate Excel file.
• Clean with Power Query: manual monthly steps (deleting columns, aligning codes, merging files) become automatic Power Query steps.
• Build the data model: fact and dimension tables are separated and related, and a calendar table is built.
• Rewrite formulas in DAX: Excel formulas become DAX measures, defined once and used everywhere.
• Run in parallel and reconcile: for one or two periods the old and new reports are produced side by side and their numbers reconciled. Only once you are fully confident is the old report retired.

Power Query: the heart of automation

Power Query exists in both Excel and Power BI and records the steps of reading and preparing data. Suppose twelve branch sales files arrive every month. In Power Query you define once: "read every file in this folder, remove extra columns, take the branch name from the file name, align product codes with the master table and stack them all". From then on you drop the new month's files in the folder and press refresh.

The catch is that Power Query only runs reliably when the input files keep a fixed structure. If column names change every month, the refresh fails. That is why part of every migration project is designing a fixed template for data entry.

From Excel formula to DAX measure

In Excel a formula is written in a cell and repeated for every row or table. In Power BI a measure is defined once and recalculates for whatever filter the user picks. Instead of ten SUMIFS formulas for each region's sales, you define one "net sales" measure, and when the user selects a region, product or month, the same measure shows the right number.

The difference is not only technical. When every KPI has a single definition, the "which report is right" argument disappears. That is why a written definition of each KPI is one of the main deliverables of every project, the same work described for management KPIs in what a management dashboard is.

Common migration mistakes

• Copying the look of the Excel report into Power BI instead of redesigning around the manager's questions.
• Reading from intermediate Excel files instead of the original source; the manual work just moves.
• Retiring the old report before the numbers fully reconcile; the first discrepancy destroys trust.
• Skipping training: if users do not know how to filter and drill down, they ask for the Excel report again.
• Moving every report at once: the project drags on and shows no result. Start with one report.

What you gain after the move

In projects I have delivered, a report that took several working days now takes minutes. More important than the time saved are two things: the analyst who spent their time on copy and paste now has time to analyse, and the manager no longer waits for a new report for every new question but finds the answer in a few clicks.

Once the core reports are in Power BI, a financial dashboard for the CFO or a production downtime dashboard on the same data model is the natural next step. The migration service is described on the Excel report automation page and the broader service on Power BI dashboard design.

Frequently asked questions