From Excel to a Real Power BI Report - The Walkthrough I Wish I Had Years Ago

Pretty much every Power BI engagement we run starts the same way. The client has an Excel workbook (or twenty), an executive who wants "a dashboard," and a finance or operations team that has been keeping the business running on a mountain of pivot tables. Somewhere in the conversation, someone says "we just want to get the data into Power BI." That sentence does a lot of heavy lifting.

Microsoft has a tutorial that walks through exactly this scenario using the Financial Sample workbook. It's a decent starting point if you've never touched the tool. What it doesn't tell you is which parts of the workflow actually matter on a real engagement, where the time goes, and what tends to bite you when you try to do the same thing with messier data. So that's what this post is about.

Think of this as the walkthrough, augmented with what we've learned actually doing this work for clients in mining, professional services, financial services, and healthcare. The fundamentals are the same. The realities are different.

What the tutorial actually covers

The Microsoft tutorial takes you from an Excel file to a published report in maybe forty-five minutes if you follow along. The high-level flow is:

1. Get the data (load the sample workbook).
2. Prepare the data in Power Query Editor (fix types, format text, rename columns, filter rows).
3. Optionally write some DAX (a measure for Total Units Sold, and a calculated calendar table).
4. Build visuals (charts, a title, a slicer).
5. Publish to the Power BI service.

This is a perfectly reasonable introduction. If you've never used Power BI before, working through it will give you a working mental model in an afternoon. Anyone on your team being introduced to the tool should probably do it.

But the gap between this tutorial and real work is wider than the tutorial implies. Let me walk through where the differences show up.

Getting the data is not the easy bit

In the tutorial, the data is a clean Excel file with one well-shaped table. In reality, you'll get:

• A file with multiple sheets, where some are reference data, some are working copies, some are notes to self, and one is the actual data with merged cells and embedded subtotals.
• A file that someone has been emailing around for six months, with formula references to other files that no longer exist.
• A file where the same column is called "Customer" on some rows and "Client Name" on others because two people merged two reports.
• A file with thousands of rows of data and an extra hundred rows of cleverly disguised summary calculations at the bottom.

The Microsoft Navigator preview helps you spot some of this before loading, but you have to know to look. We always check the row count against what the business expects, scan the column headers for anything suspicious, and look at the data types Power BI auto-detected. Auto-detection is decent but not perfect, especially with date columns where Australian and US formats can flip silently.

A useful habit on first connection. Load the data with a filter applied to the first thousand rows only. Look at it carefully. Confirm shape, confirm types, confirm join keys actually join. Then go back, remove the filter, and let the full load run.

This is the kind of discipline that pays back hugely on a real engagement and which the tutorial necessarily skips because the sample data is perfect.

Power Query is where the real work happens

The tutorial covers four transforms in Power Query Editor. Changing a data type, uppercasing a text column, renaming a column, and filtering out a discontinued product. Four steps, neatly applied, problem solved.

On a real client engagement, the Power Query steps list often runs to forty or more. We've seen it run to a hundred and fifty on legacy migrations. This isn't a sign of bad data, although it can be. More often it's the sign of data that came from operational systems where the constraints we care about for analytics simply weren't enforced.

The patterns we end up using over and over:

Type coercion done explicitly. Auto-detection of types is convenient but not stable across refreshes. We always set explicit types on every column that matters, in a step we can audit. If a refresh fails because someone added a row with a malformed date, we want to know exactly where in the query that happened.

Header normalisation. If you've got data from multiple sources or files, harmonising column names early is much cheaper than dealing with type or join issues later. Lowercase, replace spaces with underscores, trim whitespace, applied to every table consistently. It's boring work. Do it anyway.

Removing operational fields we don't need for analytics. Source system row identifiers, audit timestamps, status flags that mean nothing to a business user. Strip them in Power Query. The data model is cleaner, the report is more performant, and you avoid awkward "what's that column" questions in user training.

Unpivoting where the data has been pivoted in Excel. This is the one most analysts learn about the hard way. If your source data has months across columns instead of down rows, you can't build a useful date hierarchy. Unpivot first, model second.

For deeper Power Query work, we often pair the data prep with a Microsoft Fabric consulting discussion. Power Query inside a Power BI dataset is fine for small models, but when the data prep work gets serious you really want it living in Dataflows Gen2 or pipelines, with proper orchestration and lineage. Where to draw that line is a decision that deserves five minutes of thought rather than defaulting to whatever was easiest.

The DAX section is where Australian clients get bitten

The tutorial includes two DAX expressions. A measure for Total Units Sold and a calculated calendar table covering 2013 and 2014. Both are good first DAX experiences. Both have a trap that catches Australian and European users frequently.

The tutorial example uses US locale defaults, which means commas as argument separators. If your Power BI install is set to an Australian English locale, your separators might be semicolons instead. The same DAX expression that works fine in the US tutorial will throw a syntax error on your machine. Microsoft has improved this in the tutorial copy by calling it out explicitly, but we still see it trip up people who skim.

The other trap is the calendar table. Calling CALENDAR(DATE(2013,01,01),DATE(2014,12,31)) works in the tutorial because the sample data is bounded to those years. In real work, you almost never want a hard-coded date range. You want the calendar to cover at least the range present in your fact data, plus a buffer for forecasts and projections. Our standard pattern uses CALENDARAUTO() or a MIN/MAX lookup from the fact table to set the boundaries.

We also push clients pretty hard to use a proper calendar table from day one rather than relying on auto date/time hierarchies. The auto hierarchies cause problems later, blow up your model size, and don't support the kind of fiscal year handling that Australian organisations almost always need. End of June. Not December. Standard Australian calendar table includes a FiscalYear column, a FiscalQuarter column, and a FiscalMonth column. Just set this up properly at the start and save yourself rework.

Building visuals - the part everyone enjoys but where errors compound

The tutorial walks through dropping a few visuals onto a page. A title, a slicer, a chart, a card. Cookie-cutter stuff.

The temptation when you're new to Power BI is to keep dropping visuals onto pages because each one looks impressive. We've seen clients end up with twelve charts on a single page, six of which contradict each other because the filter context wasn't consistent across them.

What we actually do on client engagements:

Decide what the report is for before we build anything. The tutorial asks three executive questions up front. Which month and year had the most profit? Where is the company seeing the most success? Which product and segment should the company continue to invest in? That's three questions. The report should answer those three. Everything else is noise.

This sounds obvious. It is not. Half of every Power BI report we audit has visuals on it that nobody actually uses, because they got added during the build phase to fill space.

Standardise filter context across visuals on a page. Use sync slicers. Use page-level filters where appropriate. If a slicer for product affects three visuals but not the fourth, document why. Otherwise that fourth visual will quietly produce different numbers and someone in a senior leadership meeting will catch it and your credibility is gone.

Apply theming early. Power BI themes are not just about colour. They're about consistent typography, consistent number formatting, consistent visual structure. Set up a theme JSON file at the start of the engagement, apply it to every report. We've built up a standard theming approach we apply to client work because it dramatically reduces the "this looks unprofessional" feedback.

Stop chasing visual variety. If a bar chart answers the question, use a bar chart. The fancy radial gauges and animated tree maps look great in demos and answer no actual business question. Boring charts that load fast and read clearly beat clever charts every time.

Publishing is not the same as deploying

The last step in the tutorial is publishing to the Power BI service. Click the button, choose a workspace, done.

For a real organisation, this is where the governance conversation should happen. Who owns this workspace? Who has access? Is this report part of a larger app, or a one-off? What's the refresh schedule? Are we using a gateway, and if so, is the gateway connection set up properly? Are we using Premium capacity or Pro? What's the data sensitivity classification?

These aren't things you bolt on later. They're things you should figure out before you publish. We've seen Australian organisations end up with hundreds of workspaces, thousands of reports, and no clear ownership for any of them. Cleaning that up is expensive. Setting it up properly is cheap.

For organisations earlier in their Power BI journey, a structured engagement with Power BI consultants saves you from learning these lessons the painful way. We've seen what poor workspace governance does to an organisation after three years of unchecked growth, and you really don't want to be that case study.

What we'd actually recommend

If you're new to Power BI, work through the Microsoft tutorial. It's a fast way to get the basic mental model.

Then take a step back and think about your actual data, your actual business questions, and your actual organisational context. The tutorial pattern works for sample data because the sample data was designed for it. Your data will not behave that nicely.

If you've got an Excel workbook that's running important business processes and you're thinking about turning it into a proper Power BI report, our advice is to spend more time on data shape, calendar table setup, and the business questions you actually need answered, than on visual design. The reports that hold up over time are the ones with sound foundations. The reports that get rebuilt every six months are the ones where someone rushed straight to visuals.

For Australian organisations thinking about scaling Power BI properly, our AI and data services bring together the data engineering, governance, and analytics work into something that actually holds together.

Reference: Microsoft tutorial - Create a report from an Excel workbook in Power BI