{"id":1964,"date":"2021-10-01T10:50:38","date_gmt":"2021-10-01T10:50:38","guid":{"rendered":"https:\/\/www.exceldepartment.com\/?p=1964"},"modified":"2026-10-31T12:14:05","modified_gmt":"2026-10-31T12:14:05","slug":"migrating-excel-to-power-bi","status":"publish","type":"post","link":"https:\/\/www.exceldepartment.com\/en\/migrating-excel-to-power-bi\/","title":{"rendered":"Migrating from Excel to Power BI \u2013 Step-by-Step Guide 2026"},"content":{"rendered":"
\n

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.<\/p>\n

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.<\/p>\n

Important before you start: Don’t migrate everything<\/h2>\n

The biggest mistake we see is companies deciding to \u201cmigrate everything to Power BI\u201d. That is the wrong approach.<\/p>\n

The truth:<\/strong> Some Excel solutions should stay in Excel.<\/p>\n

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<\/p>\n

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<\/p>\n

If you are unsure, first read our guide Power BI vs Excel<\/a> to decide what should be migrated.<\/p>\n<\/p>\n

Phase 1: Mapping and prioritisation (Week 1)<\/h2>\n

Before you open Power BI Desktop you need to know exactly what you are going to build.<\/p>\n

Take inventory of your Excel reports<\/h3>\n

Create a list of all Excel files used for reporting. For each file, document:<\/p>\n

Basic info:<\/strong> – File name and where it is saved – Who owns\/maintains it – How often it is updated (daily, weekly, monthly) – How many users need it<\/p>\n

Data sources:<\/strong> – 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?<\/p>\n

Complexity:<\/strong> – How many worksheets? – Are there VBA macros? – Are there complex formulas or cross-sheet references? – How many different visualisations (charts, pivot tables)?<\/p>\n

Business impact:<\/strong> – How much time is spent on manual updating? – How important is the report for decisions? – What happens if the report is wrong?<\/p>\n

Prioritise by ROI<\/h3>\n

Sort the reports using this formula:<\/p>\n

Prioritet = (Tid sparad per uppdatering \u00d7 Antal uppdateringar per \u00e5r) \/ Migrationskomplexitet\n\nD\u00e4r komplexitet = L\u00e5g (1), Medel (3), H\u00f6g (5)<\/code><\/pre>\n
Example:<\/strong><\/p>\n
Report A:<\/strong> Sales report – Time: 3 hours\/update – Frequency: Weekly (52 times\/year) – Total time: 156 hours\/year – Complexity: Low (1) – Priority: 156\/1 = 156<\/strong><\/p>\n
Report B:<\/strong> 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<\/strong><\/p>\n
Choose report A first. It gives more value for less effort.<\/p>\n
Choose a pilot report<\/h3>\n
Your first migration should meet these criteria:<\/p>\n
A good pilot report:<\/strong> – 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)<\/p>\n
A poor pilot report:<\/strong> – A mission-critical report where errors are unacceptable – Complex VBA macros – Data from 10+ different sources – Manual data entry that cannot be automated<\/p>\n
Phase 2: Prepare the data (Week 1-2)<\/h2>\n
Power BI requires structured data. Excel allows chaos. This is the gap you have to bridge.<\/p>\n
Clean up the Excel source<\/h3>\n
Before you import into Power BI, fix these common problems in Excel:<\/p>\n
1. Merged cells<\/strong> Unmerge all merged cells. Power BI does not handle them.<\/p>\n
2. Blank rows and empty columns<\/strong> Remove all blank rows in the middle of the data. Power Query sees them as the end of the table.<\/p>\n
3. Inconsistent data types<\/strong> Columns must have the same data type in all rows. Text in a number column breaks the import.<\/p>\n
4. Summary rows in the middle of the data<\/strong> Move all sums and totals to separate areas. Power BI calculates totals automatically.<\/p>\n
5. Headers that are not in the first row<\/strong> Make the first row the column headers. No empty rows above.<\/p>\n
6. Formatted data instead of raw values<\/strong> \u201c3.5 million SEK\u201d should be 3500000 in the cell, with formatting for display.<\/p>\n
Create Excel tables<\/h3>\n
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<\/p>\n
Document transformations<\/h3>\n
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.<\/p>\n
Create a simple list: – Which columns are calculated from other columns? – Which filters are applied? – Which lookups\/VLOOKUPs are done? – Which date calculations exist?<\/p>\n<\/p>\n
Phase 3: Build in Power BI Desktop (Week 2-3)<\/h2>\n
Now it is time to actually migrate. Follow this sequence:<\/p>\n
Step 1: Connect the data source<\/h3>\n
Open Power BI Desktop and select \u201cGet Data\u201d.<\/p>\n
For Excel files:<\/strong> – Select Excel Workbook – Browse to the file – Select the right worksheet\/table – Click Transform Data (not Load directly)<\/p>\n
For databases:<\/strong> – Select SQL Server \/ Azure SQL \/ Access – Enter the server name and database – Select Import mode (not DirectQuery at first)<\/p>\n
Step 2: Transform in Power Query<\/h3>\n
Power Query is where you clean and shape the data. Here are common transformations:<\/p>\n
Remove unnecessary columns:<\/strong> Right-click \u2192 Remove Columns on everything you don’t need<\/p>\n
Change data type:<\/strong> Click the icon next to the column name \u2192 select the right type (Number, Date, Text)<\/p>\n
Filter out test data:<\/strong> Use the filter icon \u2192 deselect test values or old dates<\/p>\n
Pivot\/Unpivot:<\/strong> If the data is in the wrong format (wide instead of long or vice versa)<\/p>\n
Merge queries:<\/strong> A replacement for VLOOKUP. Combine two tables based on a common column.<\/p>\n
Add custom columns:<\/strong> Click Add Column \u2192 Custom Column for calculations<\/p>\n
Example:<\/strong> Create \u201cAge\u201d from \u201cDate of birth\u201d:<\/p>\n
= Date.Year(DateTime.LocalNow()) - Date.Year([F\u00f6delsedatum])<\/code><\/pre>\n
Step 3: Create the data model<\/h3>\n
After Power Query comes data modelling. This is new for most Excel users.<\/p>\n
Facts vs dimensions:<\/strong><\/p>\n
Fact tables<\/strong> contain measurable events: – Sales transactions – Orders – Log entries<\/p>\n
Dimension tables<\/strong> describe the facts: – Customers – Products – Dates – Regions<\/p>\n
Create relationships:<\/strong><\/p>\n
Go to the Model view. Drag a column from the fact table to the corresponding column in the dimension table.<\/p>\n
Example:<\/strong> – Sales[KundID] \u2192 Customers[KundID] – Sales[ProduktID] \u2192 Products[ProduktID] – Sales[Datum] \u2192 Calendar[Datum]<\/p>\n
Important:<\/strong> One-to-many relationships (a single product can appear in many sales).<\/p>\n
Step 4: Create measures with DAX<\/h3>\n
DAX is Power BI’s formula language. It resembles Excel formulas but works differently.<\/p>\n
Basic measures:<\/strong><\/p>\n
Total F\u00f6rs\u00e4ljning = SUM(F\u00f6rs\u00e4ljning[Belopp])\n\nAntal Ordrar = COUNTROWS(F\u00f6rs\u00e4ljning)\n\nGenomsnittlig Order = DIVIDE([Total F\u00f6rs\u00e4ljning], [Antal Ordrar])\n\nF\u00f6reg\u00e5ende M\u00e5nad =\nCALCULATE(\n    [Total F\u00f6rs\u00e4ljning],\n    DATEADD(Kalender[Datum], -1, MONTH)\n)\n\nTillv\u00e4xt % =\nDIVIDE(\n    [Total F\u00f6rs\u00e4ljning] - [F\u00f6reg\u00e5ende M\u00e5nad],\n    [F\u00f6reg\u00e5ende M\u00e5nad]\n)<\/code><\/pre>\n
Tip:<\/strong> Create measures for everything you want to measure. Don’t use calculated columns for aggregations.<\/p>\n<\/p>\n
Step 5: Build visualisations<\/h3>\n
Now comes the fun part. Create the same (or better) visualisations as in Excel.<\/p>\n
Card:<\/strong> For single figures – Drag the measure to the Card visual – Format decimals, thousands separator<\/p>\n
Bar\/Column chart:<\/strong> For comparisons – X-axis: Category (product, region, month) – Y-axis: Measure (sales, quantity) – Legend: An additional dimension for colour coding<\/p>\n
Line chart:<\/strong> For trends over time – X-axis: Date – Y-axis: Measure – Add a trend line under Analytics<\/p>\n
Table:<\/strong> For detailed data – Drag dimensions and measures – Sort by clicking the column headers<\/p>\n
Slicers:<\/strong> For filters – Drag a dimension to the Slicer – Change the format to Dropdown or List<\/p>\n
Tip:<\/strong> Max 6-8 visual objects per page. Create several pages if needed.<\/p>\n
Step 6: Format for usability<\/h3>\n
Add a title and description:<\/strong> – Every visual should have a clear title – Add a description for complex visualisations<\/p>\n
Colours:<\/strong> – Use the company’s colour palette – Use them consistently (same colour = same category) – Avoid too many colours (max 5-7)<\/p>\n
Format numbers:<\/strong> – Thousands separator: comma (1,234 SEK) – Decimals: a relevant number (money = 0, percent = 1) – Prefix\/suffix: \u201cSEK\u201d, \u201c%\u201d, etc.<\/p>\n
Phase 4: Validate against Excel (Week 3)<\/h2>\n
Many people skip this step. That is a mistake. You must prove that Power BI shows the same figures as Excel.<\/p>\n
Compare figure by figure<\/h3>\n
Create a validation list:<\/p>\n\n\n\n\n\n\n\n
KPI<\/th>\nExcel value<\/th>\nPower BI value<\/th>\nDifference<\/th>\nStatus<\/th>\n<\/tr>\n<\/thead>\n
Total sales<\/td>\n1,234,567 SEK<\/td>\n1,234,567 SEK<\/td>\n0 SEK<\/td>\n\u2713<\/td>\n<\/tr>\n
Number of orders<\/td>\n4 532<\/td>\n4 532<\/td>\n0<\/td>\n\u2713<\/td>\n<\/tr>\n
Average order<\/td>\n272 SEK<\/td>\n272 SEK<\/td>\n0 SEK<\/td>\n\u2713<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Go through every measure and every figure. Write down deviations.<\/p>\n
When the figures don’t match<\/h3>\n
Common causes:<\/strong><\/p>\n
1. Different filters<\/strong> Excel may have hidden filters you’ve forgotten. Check the Source tab in Excel carefully.<\/p>\n
2. Data filtered out in Power Query<\/strong> Check Applied Steps in Power Query. Have you removed too much?<\/p>\n
3. Wrong relationships<\/strong> If relationships are wrong, data is multiplied or disappears. Double-check the cardinality.<\/p>\n
4. Wrong aggregation<\/strong> SUM vs AVERAGE vs COUNT. Are you sure you are using the right one?<\/p>\n
5. Date assumptions<\/strong> Excel may have assumptions about the financial year or dates that Power BI doesn’t know.<\/p>\n
Get approval from the data owner<\/h3>\n
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.<\/p>\n
Phase 5: Publish and test (Week 4)<\/h2>\n
Now it is time to move from Desktop to the cloud.<\/p>\n
Publish to Power BI Service<\/h3>\n
In Power BI Desktop, click \u201cPublish\u201d.<\/p>\n
Select a workspace: – My Workspace:<\/strong> For testing and personal work – Shared workspace:<\/strong> For team work<\/p>\n
The first publication takes longer (data is uploaded). Future publications are faster.<\/p>\n
Set up automatic refresh<\/h3>\n
In Power BI Service, go to the dataset settings:<\/p>\n
Scheduled refresh:<\/strong> – Click \u201cSchedule refresh\u201d – Select the frequency (daily, weekly) – Select the time (often at night or early morning) – Enter credentials for the data sources<\/p>\n
Important:<\/strong> For Excel files, they must be on OneDrive or SharePoint for automatic refresh.<\/p>\n
Share with test users<\/h3>\n
Give 3-5 users access: – Go to the workspace – Click Access – Add users with the \u201cViewer\u201d role<\/p>\n
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<\/p>\n
Phase 6: Launch and retire Excel (Week 5)<\/h2>\n
Once the test users have approved, it is time for the full launch.<\/p>\n
Communicate the change<\/h3>\n
Email to all users:<\/strong><\/p>\n
Subject: New sales report in Power BI \u2013 Replaces Excel from 1 March<\/p>\n
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<\/p>\n
Tip:<\/strong> Include a short video walkthrough (2-3 min) showing how to use the report.<\/p>\n
Training<\/h3>\n
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<\/p>\n
Retire the Excel file<\/h3>\n
Step 1 (Week 1-4):<\/strong> Run in parallel. Excel is updated as usual + Power BI is available.<\/p>\n
Step 2 (Week 5):<\/strong> Stop updating Excel. Add a large red banner: \u201cTHIS REPORT IS NO LONGER UPDATED. Use Power BI: [l\u00e4nk]\u201d<\/p>\n
Step 3 (Week 6-8):<\/strong> Move the Excel file to an Archive folder. Keep it for 2-3 months for safety.<\/p>\n
Step 4 (Month 3+):<\/strong> Delete the Excel file if nobody has used it for three months.<\/p>\n
Common problems and solutions<\/h2>\n
\u201cPower BI shows wrong figures\u201d<\/h3>\n
Checklist:<\/strong> – 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<\/p>\n
\u201cThe report is slow\u201d<\/h3>\n
Common causes:<\/strong> – 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<\/p>\n
\u201cUsers can’t find the report\u201d<\/h3>\n
Solution:<\/strong> – 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<\/p>\n
\u201cData is not refreshed automatically\u201d<\/h3>\n
Checklist:<\/strong> – 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<\/p>\n
When you should get help<\/h2>\n
You can migrate on your own, but there are situations where consultant help pays off:<\/p>\n
Hire a consultant if:<\/strong> – 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<\/p>\n
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<\/p>\n
Contact us<\/a> for a free consultation (30 min).<\/p>\n
Next steps<\/h2>\n
You now have a complete plan for migrating from Excel to Power BI. Here is your checklist:<\/p>\n
Week 1:<\/strong> – [ ] Take inventory of Excel reports – [ ] Prioritise based on ROI – [ ] Choose a pilot report – [ ] Clean up and structure the source data<\/p>\n
Week 2-3:<\/strong> – [ ] Build the report in Power BI Desktop – [ ] Create the data model and relationships – [ ] Build visualisations – [ ] Validate against Excel<\/p>\n
Week 4:<\/strong> – [ ] Publish to Power BI Service – [ ] Set up automatic refresh – [ ] Test with 3-5 users – [ ] Adjust based on feedback<\/p>\n
Week 5:<\/strong> – [ ] Launch to all users – [ ] Provide training – [ ] Retire the Excel version – [ ] Document the solution<\/p>\n
Related guides:<\/strong> – Power BI for businesses: A complete guide<\/a> – 5 Power BI dashboards every CFO needs<\/a> – Power BI vs Excel: When should you choose which?<\/a><\/p>\n
Need help?<\/strong><\/p>\n
Excel Department helps Swedish companies migrate from Excel to Power BI. We can lead the whole project or coach your team through the process.<\/p>\n
Book a free consultation<\/a> \u2013 30 minutes where we discuss your situation.<\/p>\n
\n
Contact Excel Department:<\/strong> – Phone:<\/strong> 010-264 20 20<\/a> – Email:<\/strong> info@exceldepartment.se<\/a> – Address:<\/strong> Slottsbacken 8, 111 30 Stockholm<\/p>\n<\/div>\n
\n
Need help with this?<\/strong> Our Power BI consultants<\/a> help you \u2013 the first consultation is free of charge.<\/p>\n
Book a free call<\/a> or call 010-264 20 20<\/a>.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
A practical guide to migrating Excel reports to Power BI. Avoid common mistakes, validate data and get users on board. From Excel Department.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[],"tags":[],"class_list":["post-1964","post","type-post","status-publish","format-standard","hentry"],"_links":{"self":[{"href":"https:\/\/www.exceldepartment.com\/en\/wp-json\/wp\/v2\/posts\/1964","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.exceldepartment.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.exceldepartment.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.exceldepartment.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.exceldepartment.com\/en\/wp-json\/wp\/v2\/comments?post=1964"}],"version-history":[{"count":0,"href":"https:\/\/www.exceldepartment.com\/en\/wp-json\/wp\/v2\/posts\/1964\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.exceldepartment.com\/en\/wp-json\/wp\/v2\/media?parent=1964"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.exceldepartment.com\/en\/wp-json\/wp\/v2\/categories?post=1964"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.exceldepartment.com\/en\/wp-json\/wp\/v2\/tags?post=1964"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}