Stop Building One Report Per Data Source — Bring Everything Into One Power BI Model First

A pattern we keep seeing in new client projects: a company sells through its own store, a marketplace like Amazon, and runs everything through an ERP like NetSuite or SAP. Each system has its own Power BI report, built by whoever set that connector up first. Nobody can answer "what's our total revenue by product this month" without exporting three reports to Excel and adding them by hand. The fix isn't a fourth report — it's one model that all three sources feed into.

Why separate reports per source break down

Each source-specific report usually has its own definition of "customer," its own date table (or none at all), and its own product codes. Cross-source questions — total revenue, margin by channel, inventory turns across the warehouse and the marketplace's fulfillment center — become spreadsheet work because the reports were never built to be compared, only to display one system's numbers. The underlying problem is architectural: the data was never unified, so no report built on top of it can be either.

Step 1 — Land each source with Power Query, don't reshape yet

In Power BI Desktop (or a Dataflow if multiple reports will reuse the same tables), create one query per source system, and keep the first version close to raw:

// NetSuite orders via its REST API
let
    Source = Json.Document(Web.Contents("https://<account>.suitetalk.api.netsuite.com/services/rest/record/v1/salesOrder")),
    Items = Source[items],
    ToTable = Table.FromList(Items, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    ToTable

Do the same for Shopify (its REST Admin API or the Shopify connector) and for the Amazon Selling Partner API export (usually landed as CSV/Parquet in storage first, since Amazon doesn't expose a direct Power Query connector — a scheduled export to SharePoint or Azure Blob is the common workaround). At this stage each query still looks like its source. Resist the urge to merge or rename yet — get all three landed and refreshing reliably first.

Step 2 — Build dimension tables that don't belong to any one source

The reason cross-source reports break is usually a missing shared dimension. Build these once, independent of any source system:

• Date — a standalone calendar table (CALENDAR or CALENDARAUTO in DAX, or a Power Query function), never borrowed from one source's order table.
• Product — pick one system as the source of truth for the product list (often the ERP), then map the other two systems' SKUs to it with a lookup query. This mapping table is usually the single most valuable artifact in the whole model — expect to maintain it by hand at first.
• Customer/Channel — even if you don't need customer-level detail, a small channel dimension (Own Store / Amazon / Wholesale) lets every fact table roll up to "channel" consistently.

Step 3 — Shape each source into a fact table and load a star schema

Now go back to each source query and transform it into a fact table with foreign keys that point at the shared dimensions — one row per order line, with ProductKey, DateKey, ChannelKey, and the measures (quantity, revenue, cost). Use Table.NestedJoin or the Merge Queries UI to attach the mapped ProductKey from Step 2 instead of each source's native SKU.

// Mapping Shopify's SKU to the shared ProductKey before loading the fact table
let
    Source = ShopifyOrderLines,
    Merged = Table.NestedJoin(Source, {"SKU"}, ProductMap, {"ShopifySKU"}, "Mapped", JoinKind.LeftOuter),
    Expanded = Table.ExpandTableColumn(Merged, "Mapped", {"ProductKey"})
in
    Expanded

Load the three fact tables (NetSuite orders, Shopify orders, Amazon orders) and the shared dimensions into the model, then relate each fact table to Date, Product and Channel with one-to-many relationships — the classic star schema, just with three fact tables around the same dimensions instead of one.

Step 4 — Write one measure, not three

Once the star schema is in place, a single DAX measure covers every channel:

Total Revenue = 
SUMX ( 'Fact NetSuite Orders', 'Fact NetSuite Orders'[Revenue] )
+ SUMX ( 'Fact Shopify Orders', 'Fact Shopify Orders'[Revenue] )
+ SUMX ( 'Fact Amazon Orders', 'Fact Amazon Orders'[Revenue] )

Slice it by Channel[Name], Product[Category] or Date[Month] and it works identically regardless of which system the row came from, because every fact table agrees on what a date, a product and a channel are.

Where a real data warehouse pays off

For two or three sources refreshed daily, doing this entirely inside Power Query/Dataflows is enough. Once you're combining five or more systems, need hourly refresh, or want other tools besides Power BI to reuse the same clean tables, move Steps 1–3 into an actual warehouse (Fabric Lakehouse, Azure SQL, Snowflake, or SAP Datasphere if SAP is already in the mix) and point Power BI at that instead of re-running the same Power Query transforms in every report. The star schema and the dimension-mapping logic don't change — only where they physically live.

The takeaway

The fix for "we have a report per system and no combined view" is never a fourth report. It's landing every source with Power Query, agreeing on shared Date/Product/Channel dimensions once, and loading a star schema so one measure and one report answer questions that used to take three exports and a spreadsheet.