Power BI Tutorial - Combining Excel and OData Feed Data for Sales Analysis

One of the most common things we do in Power BI consulting projects is combine data from multiple sources into a single report. Product data lives in a spreadsheet. Order data comes from an API. Customer data sits in a CRM database. Finance data is in yet another system. Getting all of that into one coherent model is where Power BI earns its keep.

Microsoft has a solid tutorial that walks through combining an Excel workbook with an OData feed to build a sales analysis report. The technique itself is straightforward, but there are practical considerations the documentation doesn't cover that matter a lot when you're doing this for a real business.

Why This Pattern Comes Up So Often

Almost every mid-size Australian business we work with has data scattered across multiple systems. The product catalogue might live in Excel because someone built it years ago and it works fine. The order data sits in an ERP or e-commerce platform accessible via API (often OData). Customer segments live in a CRM. Pricing rules are in another spreadsheet somewhere.

The first instinct is usually to build a data warehouse that brings everything together. That's often the right long-term answer, but it takes months and costs real money. Power BI's Power Query engine lets you do the combination directly - pull from Excel, pull from an API, join them together, and build your report. No intermediate infrastructure needed.

This isn't a permanent architecture. But it gets insights in front of decision-makers now, which is often more valuable than a perfect data warehouse that arrives next quarter.

The Setup - Products from Excel, Orders from OData

The pattern Microsoft demonstrates uses two sources. An Excel workbook contains product information - product IDs, names, quantities per unit, and stock levels. An OData feed (their example uses the classic Northwind sample) provides order data - order dates, shipping details, line item quantities, and prices.

In Power BI Desktop, you connect to each source separately through Get Data. For Excel, you select the file and choose the specific table or sheet. For OData, you provide the feed URL and select the tables you need.

This is where most tutorials stop. Connect, load, done. But in practice, the work starts here.

Cleaning Data in Power Query Editor

Power Query Editor is where you prepare your data before it hits the report. Think of it as a staging area where you shape, filter, rename, and transform your data before it becomes part of your model.

Removing unnecessary columns. The first step with both sources is to strip out columns you don't need. The Excel workbook might have 15 columns, but your report only needs four - ProductID, ProductName, QuantityPerUnit, and UnitsInStock. Select the columns you want, right-click, and choose "Remove Other Columns."

This sounds trivial, but it matters for performance and clarity. Every column you keep increases your model size. For Import mode that means more memory usage and slower refreshes. For DirectQuery it means wider queries. And from a usability perspective, a model with 50 columns across five tables is harder to work with than one with 20 relevant columns.

Expanding nested tables. OData feeds often return data with related tables as nested references rather than flat columns. The Orders table from Northwind has an Order_Details column that contains references to line item data - product IDs, unit prices, and quantities. You need to expand these nested references to get at the actual data.

In Power Query, you click the expand icon on the column header and select which fields to bring through. This is essentially a join operation happening in the query layer. After expansion, the Order_Details column disappears and you get individual columns for ProductID, UnitPrice, and Quantity.

One thing to watch - expanding can multiply your row count significantly. An order with five line items becomes five rows. This is correct behaviour (you want line-item granularity for analysis), but it surprises people who expect the order count to stay the same.

Creating Calculated Columns in Power Query

This is an area where I see teams make a common mistake. They load raw data into the report and then try to build all their calculations in DAX. Sometimes DAX is the right place, but for row-level calculations that derive directly from source columns, Power Query is often better.

The tutorial creates a LineTotal column - simply UnitPrice multiplied by Quantity. Doing this in Power Query means the calculation happens once during data load, not repeatedly during report rendering. For large datasets, the performance difference is real.

The formula is straightforward:

[Order_Details.UnitPrice] * [Order_Details.Quantity]

After creating the column, set its data type to "Fixed decimal number" so Power BI treats it as currency rather than a generic number. Getting data types right at this stage prevents formatting headaches later.

Renaming and Reordering for Sanity

After expanding nested tables, you end up with column names like "Order_Details.ProductID" and "Order_Details.UnitPrice." These prefixes make sense to the system but are ugly in reports and confusing for business users.

Rename them. Double-click the column header and strip the prefix. ProductID, UnitPrice, Quantity - clean and readable.

Also consider column order. Put frequently used columns toward the left. Move your calculated LineTotal column next to the shipping columns so related data sits together. This is a small thing, but it makes the model easier to work with for everyone on your team.

Applied Steps - Your Audit Trail

Everything you do in Power Query gets recorded as Applied Steps in the Query Settings pane. This is quietly one of Power Query's best features.

Each step is a recorded transformation with a formula written in the M language (Power Query's underlying language). You can step through them to see how your data changes at each point. You can delete steps, reorder them, or edit the underlying M formula directly.

For consulting work, this is gold. When we hand a report over to a client's team, they can see exactly what transformations were applied. There's no black box. If something breaks six months later because a column name changed in the source, they can trace through the steps and find the issue.

A word of warning - editing earlier steps can break later ones. If step 3 renames a column and step 7 references that column by its new name, deleting step 3 breaks step 7. Always work through steps sequentially when troubleshooting.

Beyond the Tutorial - Real-World Considerations

The Northwind sample data is clean. Real-world data is messy. Here's what we deal with regularly.

Data type mismatches. Your Excel ProductID might be text while your OData ProductID is an integer. Power Query won't join these automatically. You need to convert one to match the other before merging queries. This catches people out more than almost anything else.

Null handling. Real order data has gaps. Missing shipping addresses, null quantities on cancelled lines, products without categories. Power Query propagates nulls through calculations, so a null UnitPrice times Quantity gives you null, not zero. Decide early how you want to handle missing data and add explicit null replacement steps.

Refresh failures. Excel files move. OData endpoints change URLs. Network permissions shift. Your Power Query connections will break eventually. Set up error handling and alerting in the Power BI service so you know when refreshes fail rather than discovering it when a manager asks why the numbers haven't changed in a week.

Incremental loading. The tutorial imports all orders every time. For small datasets that's fine. For a business with years of order history, you'll want incremental refresh - only loading new or changed records. Power BI supports this natively, but it requires partitioning your data by date and configuring the incremental refresh policy.

When to Move Beyond Power Query

Power Query is great for combining a few sources with moderate data volumes. But it has limits.

If you're combining more than five or six sources, the Power Query editor starts feeling unwieldy. If your transformations are complex with many conditional steps, the M code becomes hard to maintain. If data volumes run into tens of millions of rows with complex joins, refresh times can blow out.

At that point, consider moving the data preparation upstream. Azure Data Factory or Microsoft Fabric pipelines can handle the heavy lifting, outputting clean tables that Power BI simply imports. You keep the simplicity in the report layer and put the complexity where it belongs - in a proper data integration tool.

For most Australian businesses starting their analytics journey though, Power Query is enough. It gets you from scattered data to working reports without needing a data engineering team. And when you outgrow it, the path to something more capable is clear.

Microsoft's full tutorial includes downloadable sample files if you want to follow along step by step. It's a good exercise even for experienced Power BI users - sometimes going back to basics reveals shortcuts you've been missing.

Making It Stick

The technique of combining multiple data sources in Power Query is one of those skills that pays off repeatedly. Once you understand the pattern - connect, clean, expand, calculate, merge - you can apply it to almost any data combination scenario.

The key lesson from our consulting work is this: spend more time in Power Query than you think you need to. Clean data and well-structured queries make everything downstream easier. Report building goes faster, performance is better, and maintenance is simpler. It's the least glamorous part of a BI project, but it's where the quality actually lives.