The spreadsheet worked fine when the business was smaller.
Most manual reporting setups weren't designed — they accumulated. The ERP went live, someone built a spreadsheet to pull the weekly numbers. The CRM arrived, someone added a tab. Finance started sending a separate file on Fridays. And now, every Monday, someone spends two or three hours opening those files, copying columns, checking that the totals match, and building the slide or summary that leadership actually reads.
The problem isn't that spreadsheets are bad. The problem is that your data now lives in three or four separate systems and none of them talk to each other in real time. Power BI connects directly to those systems, pulls the data on a schedule you define, and presents it in a format your team can read without a Friday export.
This guide walks through exactly how that works — where Power BI connects, what the dashboard actually looks like, and what it takes to get from your current setup to a live reporting view.
Before you open Power BI, you need to know your sources.
The most common reporting setup we see at growing businesses: one ERP or accounting system (QuickBooks, Xero, Sage, SAP Business One, Odoo), one CRM (Zoho, Salesforce, HubSpot, or Dynamics), and usually one or two spreadsheets that exist because the other systems don't quite cover everything. Sometimes there's a separate database behind a custom-built internal tool.
Each of these is a data source. Power BI can connect to all of them — but the connection method matters.
Direct database connection — the cleanest option
If your ERP or internal tool runs on SQL Server, PostgreSQL, or MySQL, Power BI can connect directly to the database. Live data, configurable refresh schedule, no export files. This is how we connected Holford Components' SAP Business One data — via the Service Layer API rather than directly, but the principle is the same.API connection — the most common for SaaS tools
Most modern SaaS systems (Zoho CRM, HubSpot, QuickBooks Online, Xero) have REST APIs that Power BI can connect to via certified connectors or custom Power Query scripts. The data refreshes on your schedule — hourly, daily, or on demand.File import — the fallback for legacy systems
If your system only exports to Excel or CSV, you can still automate. Power Automate can watch a SharePoint folder, pick up new exports when they arrive, and feed them into Power BI's dataset automatically. Not as clean as a direct connection, but it eliminates the manual step.The most common mistake in Power BI projects isn't technical. It's starting with 'let's put all our data in there' rather than starting with the three questions your Monday meeting is actually trying to answer.
Before touching Power BI, write down the specific questions your weekly report needs to answer. For a manufacturing business, those might be: What was our production output this week versus target? What was the rejection rate and which product lines contributed most? Where are we versus budget for the month? For a services business: How many active projects are on track, at risk, or behind? What's the pipeline value and how has it moved this week?
Three to five questions per stakeholder group is the right scope for a v1 dashboard. You can add more later. Starting with too broad a scope is why many Power BI projects take months instead of weeks.
Connecting data sources is straightforward. Building a data model that produces numbers you can trust is where the real work is.
A data model in Power BI defines how your tables relate to each other. Your sales table has a customer ID — that needs to link to your customer table. Your production orders have a date — that needs to link to a date table so you can filter by week, month, or quarter. If these relationships are wrong or missing, the numbers in your dashboard will be wrong in ways that are very difficult to diagnose.
This is also where data quality problems surface. We always spend time with the data before building any dashboards. If your ERP has production orders with missing completion timestamps — something we found in a manufacturing client's SAP system — you need to decide how to handle that before you build a 'production output this week' chart. Showing a number that excludes 15% of orders because they weren't closed properly is worse than not showing the number at all.
// Pre-build validation checks
if (missing_date_relationships)
report_issue("Slicers and time intelligence may fail");
if (duplicate_fact_rows)
report_issue("Measures and totals will be overstated");
if (inconsistent_category_names)
report_issue("Dimensions will fragment");
if (null_foreign_keys)
report_issue("Relationships may break");
if (mixed_time_zones)
report_issue("Date filtering will be inaccurate");
// Outcome
clean_data_model = true;
dashboard_accuracy = improved;
reporting_confidence = high;
A well-built Power BI dashboard doesn't need a user manual. The person reading it — whether that's a plant manager, a sales director, or a CFO — should be able to look at it for 30 seconds and know whether things are going well or not.
That means using the right visual for each metric. A KPI card for a single number with a comparison to target. A bar chart for comparison across categories. A line chart for trends over time. Not every chart type, not because they look interesting, but because each one is right for the data it's showing.
It also means building separate views for different audiences. An executive summary for the Monday meeting — one screen, the five numbers that matter most, colour-coded red/amber/green against target. A detail view for the operations team — drill-down by product line, by shift, by week. The same underlying data, different layers of detail.
The executive view — one screen, five KPIs
Revenue vs target · Gross margin · Pipeline value · Open items requiring action · One trend chart. That's it. If leadership needs to drill down, they ask the operations team. The executive view answers 'are we on track?' without requiring analysis.The operations view — drill-down and filters
The same metrics, but with slicers for time period, region, product line, or cost centre. Operations managers want to be able to ask 'why is the North region below target this month?' and find the answer without calling someone.The finance view — actuals vs budget, period close
Cost centre breakdown, actuals vs budget, variance explanation. Connected to your accounting system directly — no more waiting for the Friday spreadsheet. Updated daily, or on the schedule that makes sense for your close cycle.The last step is the most important: removing the manual process that Power BI is replacing. This means setting a refresh schedule — usually overnight, so the data is current when people arrive — and making sure the person who previously built the manual report knows it's been replaced.
It also means a handover session. Everyone who uses the dashboard should understand how to read it, how to change the date filter, and how to drill down if they need more detail. A dashboard that nobody uses because nobody was trained on it is a common outcome of Power BI projects that got the technical side right but skipped the adoption side.
We always include a training session and documentation in our Power BI deliveries. The documentation covers how to refresh manually if the scheduled refresh fails, how to add a new metric, and what to do if a data source connection breaks. The person who receives the dashboard should be able to manage it independently.
The honest answer depends on two things: how many data sources you have, and how clean the data is. A single data source with well-structured data and three clear questions to answer can produce a working dashboard in two to three weeks. A business with an ERP, a CRM, a custom database, and a finance spreadsheet — with data quality issues in at least one of them — is looking at six to ten weeks for a complete, trustworthy setup.
The biggest variable is always data quality. We always spend time with the data first. If we find issues that need to be addressed before the numbers are trustworthy, we flag them and agree on how to handle them before building anything. A fast dashboard built on bad data is not useful. A slower dashboard built on clean data runs every Monday without anyone questioning the numbers.
Single source · Clean data · 3–5 KPIs
Two to three sources · Some data quality work needed
ERP + CRM + custom database · Complex data model
Questions we get asked about Power BI reporting projects
Ready to stop building your report manually every Monday?
We've connected Power BI to SAP, Odoo, QuickBooks, Zoho, and custom-built systems for clients across manufacturing, services, and retail. ISO 27001. NDA from day one. Fixed-price projects.