Your monthly report in Excel takes three hours to update. Power BI can do it automatically. But how do you move over without losing data or the trust of your users? This guide shows exactly how.
At Excel Department we have migrated 50+ Excel solutions to Power BI. We have seen what works and what goes wrong. This guide gathers those experiences into a practical process you can follow step by step.
Important before you start: Don’t migrate everything
The biggest mistake we see is companies deciding to “migrate everything to Power BI”. That is the wrong approach.
The truth: Some Excel solutions should stay in Excel.
Power BI is suited for: – Recurring reports that are updated regularly – Data from several sources that must be combined – Reports shared with many users – Datasets over 100,000 rows
Excel is still better for: – Budgets and forecasts with many assumptions – Ad hoc analyses that are done once – Complex calculations with cell references – Templates where users enter data manually
If you are unsure, first read our guide Power BI vs Excel to decide what should be migrated.
Phase 1: Mapping and prioritisation (Week 1)
Before you open Power BI Desktop you need to know exactly what you are going to build.
Take inventory of your Excel reports
Create a list of all Excel files used for reporting. For each file, document:
Basic info: – File name and where it is saved – Who owns/maintains it – How often it is updated (daily, weekly, monthly) – How many users need it
Data sources: – Where does the data come from? (another Excel file, database, manual entry, export from a system) – How is the data retrieved today? (copy-paste, import, VBA macro) – Is the data source available for automation?
Complexity: – How many worksheets? – Are there VBA macros? – Are there complex formulas or cross-sheet references? – How many different visualisations (charts, pivot tables)?
Business impact: – How much time is spent on manual updating? – How important is the report for decisions? – What happens if the report is wrong?
Prioritise by ROI
Sort the reports using this formula:
Prioritet = (Tid sparad per uppdatering × Antal uppdateringar per år) / Migrationskomplexitet
Där komplexitet = Låg (1), Medel (3), Hög (5)Example:
Report A: Sales report – Time: 3 hours/update – Frequency: Weekly (52 times/year) – Total time: 156 hours/year – Complexity: Low (1) – Priority: 156/1 = 156
Report B: Monthly report to the board – Time: 8 hours/update – Frequency: Monthly (12 times/year) – Total time: 96 hours/year – Complexity: High (5) – Priority: 96/5 = 19
Choose report A first. It gives more value for less effort.
Choose a pilot report
Your first migration should meet these criteria:
A good pilot report: – Important enough that people care – Not so critical that errors have major consequences – A clear data source that can be connected automatically – 3-10 visualisations (not too simple, not too complex) – 3-5 users (enough to test sharing)
A poor pilot report: – A mission-critical report where errors are unacceptable – Complex VBA macros – Data from 10+ different sources – Manual data entry that cannot be automated
Phase 2: Prepare the data (Week 1-2)
Power BI requires structured data. Excel allows chaos. This is the gap you have to bridge.
Clean up the Excel source
Before you import into Power BI, fix these common problems in Excel:
1. Merged cells Unmerge all merged cells. Power BI does not handle them.
2. Blank rows and empty columns Remove all blank rows in the middle of the data. Power Query sees them as the end of the table.
3. Inconsistent data types Columns must have the same data type in all rows. Text in a number column breaks the import.
4. Summary rows in the middle of the data Move all sums and totals to separate areas. Power BI calculates totals automatically.
5. Headers that are not in the first row Make the first row the column headers. No empty rows above.
6. Formatted data instead of raw values “3.5 million SEK” should be 3500000 in the cell, with formatting for display.
Create Excel tables
Turn your data range into an Excel table (Ctrl+T). It gives you: – Automatic expansion when new rows are added – Easier referencing in Power Query – Structured references in formulas
Document transformations
If you have VBA code or complex formulas in Excel, document what they do. You will need to recreate the logic in Power Query or DAX.
Create a simple list: – Which columns are calculated from other columns? – Which filters are applied? – Which lookups/VLOOKUPs are done? – Which date calculations exist?
Phase 3: Build in Power BI Desktop (Week 2-3)
Now it is time to actually migrate. Follow this sequence:
Step 1: Connect the data source
Open Power BI Desktop and select “Get Data”.
For Excel files: – Select Excel Workbook – Browse to the file – Select the right worksheet/table – Click Transform Data (not Load directly)
For databases: – Select SQL Server / Azure SQL / Access – Enter the server name and database – Select Import mode (not DirectQuery at first)
Step 2: Transform in Power Query
Power Query is where you clean and shape the data. Here are common transformations:
Remove unnecessary columns: Right-click → Remove Columns on everything you don’t need
Change data type: Click the icon next to the column name → select the right type (Number, Date, Text)
Filter out test data: Use the filter icon → deselect test values or old dates
Pivot/Unpivot: If the data is in the wrong format (wide instead of long or vice versa)
Merge queries: A replacement for VLOOKUP. Combine two tables based on a common column.
Add custom columns: Click Add Column → Custom Column for calculations
Example: Create “Age” from “Date of birth”:
= Date.Year(DateTime.LocalNow()) - Date.Year([Födelsedatum])Step 3: Create the data model
After Power Query comes data modelling. This is new for most Excel users.
Facts vs dimensions:
Fact tables contain measurable events: – Sales transactions – Orders – Log entries
Dimension tables describe the facts: – Customers – Products – Dates – Regions
Create relationships:
Go to the Model view. Drag a column from the fact table to the corresponding column in the dimension table.
Example: – Sales[KundID] → Customers[KundID] – Sales[ProduktID] → Products[ProduktID] – Sales[Datum] → Calendar[Datum]
Important: One-to-many relationships (a single product can appear in many sales).
Step 4: Create measures with DAX
DAX is Power BI’s formula language. It resembles Excel formulas but works differently.
Basic measures:
Total Försäljning = SUM(Försäljning[Belopp])
Antal Ordrar = COUNTROWS(Försäljning)
Genomsnittlig Order = DIVIDE([Total Försäljning], [Antal Ordrar])
Föregående Månad =
CALCULATE(
[Total Försäljning],
DATEADD(Kalender[Datum], -1, MONTH)
)
Tillväxt % =
DIVIDE(
[Total Försäljning] - [Föregående Månad],
[Föregående Månad]
)Tip: Create measures for everything you want to measure. Don’t use calculated columns for aggregations.
Step 5: Build visualisations
Now comes the fun part. Create the same (or better) visualisations as in Excel.
Card: For single figures – Drag the measure to the Card visual – Format decimals, thousands separator
Bar/Column chart: For comparisons – X-axis: Category (product, region, month) – Y-axis: Measure (sales, quantity) – Legend: An additional dimension for colour coding
Line chart: For trends over time – X-axis: Date – Y-axis: Measure – Add a trend line under Analytics
Table: For detailed data – Drag dimensions and measures – Sort by clicking the column headers
Slicers: For filters – Drag a dimension to the Slicer – Change the format to Dropdown or List
Tip: Max 6-8 visual objects per page. Create several pages if needed.
Step 6: Format for usability
Add a title and description: – Every visual should have a clear title – Add a description for complex visualisations
Colours: – Use the company’s colour palette – Use them consistently (same colour = same category) – Avoid too many colours (max 5-7)
Format numbers: – Thousands separator: comma (1,234 SEK) – Decimals: a relevant number (money = 0, percent = 1) – Prefix/suffix: “SEK”, “%”, etc.
Phase 4: Validate against Excel (Week 3)
Many people skip this step. That is a mistake. You must prove that Power BI shows the same figures as Excel.
Compare figure by figure
Create a validation list:
| KPI | Excel value | Power BI value | Difference | Status |
|---|---|---|---|---|
| Total sales | 1,234,567 SEK | 1,234,567 SEK | 0 SEK | ✓ |
| Number of orders | 4 532 | 4 532 | 0 | ✓ |
| Average order | 272 SEK | 272 SEK | 0 SEK | ✓ |
Go through every measure and every figure. Write down deviations.
When the figures don’t match
Common causes:
1. Different filters Excel may have hidden filters you’ve forgotten. Check the Source tab in Excel carefully.
2. Data filtered out in Power Query Check Applied Steps in Power Query. Have you removed too much?
3. Wrong relationships If relationships are wrong, data is multiplied or disappears. Double-check the cardinality.
4. Wrong aggregation SUM vs AVERAGE vs COUNT. Are you sure you are using the right one?
5. Date assumptions Excel may have assumptions about the financial year or dates that Power BI doesn’t know.
Get approval from the data owner
The person who owns the Excel report must approve that the Power BI version is correct. Book a meeting where you go through the figures one by one together.
Phase 5: Publish and test (Week 4)
Now it is time to move from Desktop to the cloud.
Publish to Power BI Service
In Power BI Desktop, click “Publish”.
Select a workspace: – My Workspace: For testing and personal work – Shared workspace: For team work
The first publication takes longer (data is uploaded). Future publications are faster.
Set up automatic refresh
In Power BI Service, go to the dataset settings:
Scheduled refresh: – Click “Schedule refresh” – Select the frequency (daily, weekly) – Select the time (often at night or early morning) – Enter credentials for the data sources
Important: For Excel files, they must be on OneDrive or SharePoint for automatic refresh.
Share with test users
Give 3-5 users access: – Go to the workspace – Click Access – Add users with the “Viewer” role
Book 30 minutes with each user: – Show how to open the report – Show how filters work – Show how to export to Excel if needed – Listen to feedback
Phase 6: Launch and retire Excel (Week 5)
Once the test users have approved, it is time for the full launch.
Communicate the change
Email to all users:
Subject: New sales report in Power BI – Replaces Excel from 1 March
Content: – What is changing (Excel is replaced by Power BI) – Why (automatic refresh, always up-to-date data) – Link to the report – When it takes effect – Who to contact if there are problems
Tip: Include a short video walkthrough (2-3 min) showing how to use the report.
Training
Book a 30-minute introduction for all users: – Show how to open the report – Demonstrate interactivity (click, filter, drill down) – Show the mobile app if relevant – Questions and answers
Retire the Excel file
Step 1 (Week 1-4): Run in parallel. Excel is updated as usual + Power BI is available.
Step 2 (Week 5): Stop updating Excel. Add a large red banner: “THIS REPORT IS NO LONGER UPDATED. Use Power BI: [länk]”
Step 3 (Week 6-8): Move the Excel file to an Archive folder. Keep it for 2-3 months for safety.
Step 4 (Month 3+): Delete the Excel file if nobody has used it for three months.
Common problems and solutions
“Power BI shows wrong figures”
Checklist: – Check the relationships in the Model view – Verify that all filters are correct – Compare the Power Query Applied Steps with the Excel source – Test with a simpler dataset to isolate the problem
“The report is slow”
Common causes: – Too many visual objects on one page (max 6-8) – Calculated columns instead of measures – DirectQuery when Import mode works better – Large images in the report
“Users can’t find the report”
Solution: – Create a Power BI App instead of sharing the workspace – Pin the report in a Teams channel – Add a bookmark in the browser – Send a direct link by email
“Data is not refreshed automatically”
Checklist: – Check that scheduled refresh is enabled – Verify the credentials for the data sources – For Excel: move to SharePoint/OneDrive – Check the refresh history for error messages
When you should get help
You can migrate on your own, but there are situations where consultant help pays off:
Hire a consultant if: – Your data model is complex (10+ tables, many relationships) – You have complex VBA macros to translate – Data sources are hard to connect (custom APIs, legacy systems) – You have a tight deadline – You want to avoid common mistakes
At Excel Department we have migrated 50+ Excel solutions to Power BI. We can: – Evaluate which reports should be migrated – Build a pilot report together with your team – Train your users – Set up automatic refreshes – Document the solution
Contact us for a free consultation (30 min).
Next steps
You now have a complete plan for migrating from Excel to Power BI. Here is your checklist:
Week 1: – [ ] Take inventory of Excel reports – [ ] Prioritise based on ROI – [ ] Choose a pilot report – [ ] Clean up and structure the source data
Week 2-3: – [ ] Build the report in Power BI Desktop – [ ] Create the data model and relationships – [ ] Build visualisations – [ ] Validate against Excel
Week 4: – [ ] Publish to Power BI Service – [ ] Set up automatic refresh – [ ] Test with 3-5 users – [ ] Adjust based on feedback
Week 5: – [ ] Launch to all users – [ ] Provide training – [ ] Retire the Excel version – [ ] Document the solution
Related guides: – Power BI for businesses: A complete guide – 5 Power BI dashboards every CFO needs – Power BI vs Excel: When should you choose which?
Need help?
Excel Department helps Swedish companies migrate from Excel to Power BI. We can lead the whole project or coach your team through the process.
Book a free consultation – 30 minutes where we discuss your situation.
Contact Excel Department: – Phone: 010-264 20 20 – Email: info@exceldepartment.se – Address: Slottsbacken 8, 111 30 Stockholm
Need help with this? Our Power BI consultants help you – the first consultation is free of charge.
Book a free call or call 010-264 20 20.
