Power Query
Transformations inside Power BI. No extra infrastructure.
Best when: modest volumes, and analysts will maintain it.
A working demo on three open data sources, with checks on every load. Every figure on this page is read from the pipeline's own output.
I build reports and pipelines like this for clients. The questions at the end write me an email.
Five dashboards on one model. Open one, try it in each theme, or take the theme for your own report.
4 pages · in Harbour
Data to 31 May 2016
Revenue against the synthetic budget by month, 1 Jan 2013 to 31 May 2016.
Revenue by sales territory, 1 Jan 2013 to 31 May 2016.
Data to 31 May 2016
The synthetic budget starts in Jan 2014. It is based on the year before, so the first year of data has none.
Monthly revenue against the synthetic budget, 1 Jan 2013 to 31 May 2016.
Data to 31 May 2016
Revenue and synthetic budget by sales territory, 1 Jan 2014 to 31 May 2016.
Data to 31 May 2016
Revenue by sales territory, 1 Jan 2013 to 31 May 2016.
Data to 31 May 2016
Date
KPIs: 1 Jan 2016 to 31 May 2016.
Layout after the SalesSense sample by Rui Romano (MIT licence).
Data to 31 May 2016
KPIs: 1 Jan 2016 to 31 May 2016.
| Territory | Revenue | Budget (synthetic) | Revenue and budget, drawn |
|---|---|---|---|
| External | $1,553,048 | $1,606,400 | |
| Far West | $14,621,638 | $14,476,700 | |
| Great Lakes | $14,919,994 | $14,375,800 | |
| Mideast | $18,889,725 | $18,612,800 | |
| New England | $5,865,024 | $5,235,000 | |
| Plains | $17,026,219 | $16,809,000 | |
| Rocky Mountain | $8,390,836 | $7,801,100 | |
| Southeast | $27,876,562 | $27,460,000 | |
| Southwest | $17,411,108 | $17,855,300 |
Data to 31 May 2016
KPIs: 1 Jan 2016 to 31 May 2016.
| Territory | Tailspin Toys | Wingtip Toys | No buying group |
|---|---|---|---|
| Southeast | $10.9M | $11.1M | $16.2M |
| Mideast | $8.3M | $9.1M | $8.4M |
| Southwest | $6.3M | $8.8M | $8.7M |
| Plains | $8.4M | $5.8M | $9.1M |
| Great Lakes | $4.9M | $8.5M | $6.7M |
| Far West | $8.1M | $5.1M | $6.8M |
| Rocky Mountain | $4.1M | $3.1M | $3.9M |
| New England | $2.8M | $1.7M | $3.2M |
| External | $797.7K | $1M | $358.2K |
Data to 31 May 2016
KPIs: 1 Jan 2016 to 31 May 2016.
| Month | 2013 | 2014 | 2015 | 2016 |
|---|---|---|---|---|
| Jan | $3.8M | $4.1M | $4.4M | $4.4M |
| Feb | $2.8M | $3.5M | $4.2M | $4M |
| Mar | $3.9M | $3.9M | $4.5M | $4.6M |
| Apr | $4.1M | $4.1M | $5.1M | $4.6M |
| May | $4.4M | $4.6M | $4.5M | $5M |
| Jun | $4.1M | $4.3M | $4.5M | |
| Jul | $4.4M | $4.8M | $5.2M | |
| Aug | $3.5M | $4.1M | $3.9M | |
| Sep | $3.8M | $3.9M | $4.7M | |
| Oct | $3.8M | $4.4M | $4.5M | |
| Nov | $3.7M | $4M | $4.1M | |
| Dec | $3.6M | $4.4M | $4.5M |
Stronger colour, more revenue.
The theme file works in Power BI Desktop: Design, Import theme. It holds colours only, no data.
Theme
Sample data
Theme files hold colours only. The sample data is the open Wide World Importers demo, the same numbers as above.
| Month | Revenue | Budget (synthetic) |
|---|---|---|
| Jan 2013 | $3,770,410.85 | none |
| Feb 2013 | $2,776,786.20 | none |
| Mar 2013 | $3,870,505.30 | none |
| Apr 2013 | $4,059,606.85 | none |
| May 2013 | $4,417,965.55 | none |
| Jun 2013 | $4,069,036.20 | none |
| Jul 2013 | $4,381,767.45 | none |
| Aug 2013 | $3,495,991.00 | none |
| Sep 2013 | $3,779,040.85 | none |
| Oct 2013 | $3,752,608.45 | none |
| Nov 2013 | $3,697,461.90 | none |
| Dec 2013 | $3,636,007.40 | none |
| Jan 2014 | $4,067,538.00 | $3,958,900.00 |
| Feb 2014 | $3,470,209.20 | $2,915,700.00 |
| Mar 2014 | $3,861,928.75 | $4,064,000.00 |
| Apr 2014 | $4,095,234.65 | $4,262,600.00 |
| May 2014 | $4,590,639.10 | $4,638,700.00 |
| Jun 2014 | $4,266,644.10 | $4,272,500.00 |
| Jul 2014 | $4,786,301.05 | $4,600,900.00 |
| Aug 2014 | $4,085,489.60 | $3,670,800.00 |
| Sep 2014 | $3,882,968.85 | $3,968,100.00 |
| Oct 2014 | $4,438,683.65 | $3,940,300.00 |
| Nov 2014 | $4,018,967.45 | $3,882,300.00 |
| Dec 2014 | $4,364,882.80 | $3,817,800.00 |
| Jan 2015 | $4,401,699.25 | $4,270,900.00 |
| Feb 2015 | $4,195,319.25 | $3,643,700.00 |
| Mar 2015 | $4,528,131.65 | $4,055,100.00 |
| Apr 2015 | $5,073,264.75 | $4,299,900.00 |
| May 2015 | $4,480,730.55 | $4,820,200.00 |
| Jun 2015 | $4,515,840.45 | $4,480,100.00 |
| Jul 2015 | $5,155,672.00 | $5,025,500.00 |
| Aug 2015 | $3,938,163.40 | $4,289,900.00 |
| Sep 2015 | $4,662,600.00 | $4,077,200.00 |
| Oct 2015 | $4,492,049.40 | $4,660,600.00 |
| Nov 2015 | $4,089,208.50 | $4,220,000.00 |
| Dec 2015 | $4,458,811.25 | $4,583,200.00 |
| Jan 2016 | $4,447,705.95 | $4,621,900.00 |
| Feb 2016 | $4,005,616.85 | $4,405,100.00 |
| Mar 2016 | $4,645,254.00 | $4,754,600.00 |
| Apr 2016 | $4,563,666.10 | $5,326,800.00 |
| May 2016 | $4,970,932.65 | $4,704,800.00 |
| Territory | Revenue |
|---|---|
| Southeast | $38,265,165.75 |
| Mideast | $25,758,309.30 |
| Southwest | $23,924,720.30 |
| Plains | $23,307,175.85 |
| Great Lakes | $20,153,233.85 |
| Far West | $19,879,592.80 |
| Rocky Mountain | $11,076,949.95 |
| New England | $7,696,054.80 |
| External | $2,200,138.60 |
| Territory | Revenue | Budget (synthetic) |
|---|---|---|
| External | $1,553,047.65 | $1,606,400.00 |
| Far West | $14,621,638.25 | $14,476,700.00 |
| Great Lakes | $14,919,993.60 | $14,375,800.00 |
| Mideast | $18,889,725.05 | $18,612,800.00 |
| New England | $5,865,024.30 | $5,235,000.00 |
| Plains | $17,026,218.75 | $16,809,000.00 |
| Rocky Mountain | $8,390,835.60 | $7,801,100.00 |
| Southeast | $27,876,561.75 | $27,460,000.00 |
| Southwest | $17,411,108.25 | $17,855,300.00 |
Revenue and budget cover the same months, from the first month of the budget. The territory table above starts a year earlier, so do not read the two together.
| Customer category | Revenue |
|---|---|
| Novelty Shop | $123,220,010.30 |
| Supermarket | $14,360,495.80 |
| Gift Store | $11,683,614.10 |
| Computer Store | $11,560,346.80 |
| Corporate | $11,436,874.20 |
| Territory | Buying group | Revenue |
|---|---|---|
| External | Tailspin Toys | $797,736.15 |
| External | Wingtip Toys | $1,044,206.60 |
| External | No buying group | $358,195.85 |
| Far West | Tailspin Toys | $8,057,345.80 |
| Far West | Wingtip Toys | $5,053,531.30 |
| Far West | No buying group | $6,768,715.70 |
| Great Lakes | Tailspin Toys | $4,915,771.15 |
| Great Lakes | Wingtip Toys | $8,505,232.30 |
| Great Lakes | No buying group | $6,732,230.40 |
| Mideast | Tailspin Toys | $8,264,736.35 |
| Mideast | Wingtip Toys | $9,061,783.10 |
| Mideast | No buying group | $8,431,789.85 |
| New England | Tailspin Toys | $2,762,904.70 |
| New England | Wingtip Toys | $1,705,126.70 |
| New England | No buying group | $3,228,023.40 |
| Plains | Tailspin Toys | $8,389,768.50 |
| Plains | Wingtip Toys | $5,769,154.50 |
| Plains | No buying group | $9,148,252.85 |
| Rocky Mountain | Tailspin Toys | $4,058,373.05 |
| Rocky Mountain | Wingtip Toys | $3,113,273.90 |
| Rocky Mountain | No buying group | $3,905,303.00 |
| Southeast | Tailspin Toys | $10,900,993.20 |
| Southeast | Wingtip Toys | $11,142,090.85 |
| Southeast | No buying group | $16,222,081.70 |
| Southwest | Tailspin Toys | $6,349,580.70 |
| Southwest | Wingtip Toys | $8,841,036.65 |
| Southwest | No buying group | $8,734,102.95 |
Five stages. Each one leaves something you can check, so a wrong number can be traced back to where it came from.
Load from each source on a schedule: a database, an API, a spreadsheet.
Keep raw copies in staging tables before anything is changed.
Fix types, remove duplicates and convert currencies at each day's ECB rate.
Tables in the Power BI model: DimDate, DimSalesTerritory, DimCustomer, DimProduct, FactSales, FactBudget, Currency.
A semantic model with a documented measure set, and a report on top. Its DAX is checked against the values the pipeline computes.
Finished 15 Nov 2026, 05:46 UTC. Data as of 31 May 2016.
| Step | Written to | Rows | Time |
|---|---|---|---|
| ECB exchange rates | stg.FxRate | 1,461 | 44.21 s |
| Budget workbook (synthetic) | stg.Budget | 324 | 0.03 s |
| Watermark | meta.Freshness | not counted | 0.15 s |
The pipeline has the same shape either way. What runs it depends on where your data lives and who maintains it after handover.
Transformations inside Power BI. No extra infrastructure.
Best when: modest volumes, and analysts will maintain it.
A database the pipeline loads into on a schedule, with basic monitoring.
Best when: you already run one of them, or the sources need cleaning before they reach a report.
Scripted extracts and rules. The loaders in this demo are Python.
Best when: APIs, messy files, or business rules too custom for a GUI.
Each applied step is small and named for what it does, so the next person can read it. Click a step to see its M code.
= List.Dates(#date(2013, 1, 1), 1461, #duration(1, 0, 0, 0))A report is only as good as its last refresh. The pipeline runs these checks after each load.
48 of 48 checks passed. Run 17 Nov 2026, 03:50 UTC.
The synthetic budget has one row for every month and sales territory, matching prior-year actuals times 1.05, rounded to the nearest 100.
5 of 5 checks passed
Showcase uses the same text collation as WideWorldImporters, so territory and product names compare and join correctly across databases.
1 of 1 check passed
The reference model and the Microsoft data warehouse (WideWorldImportersDW) agree on revenue, profit, row counts and sales territory names.
5 of 5 checks passed
The reference model has no orphaned, duplicated or dropped sales lines, and the synthetic budget's own keys resolve against the model's dimensions.
7 of 7 checks passed
The ECB publishes no rates on weekends and holidays, so the last rate carries forward.
7 of 7 checks passed
The report's monthly, territory and KPI aggregates tie back to the reference model and to each other.
13 of 13 checks passed
WideWorldImporters and WideWorldImportersDW are restored, online, and their core tables are not empty.
8 of 8 checks passed
The recorded watermark equals the latest invoice date, and the load that set it is the one recorded in the run manifest.
2 of 2 checks passed
| Comparison | Left | Right | Difference | Result |
|---|---|---|---|---|
| Invoice lines: source to reference modelInvoice lines in WideWorldImporters against the reference model's fact table. | Left228,265WideWorldImporters.Sales.InvoiceLines | Right228,265Showcase model.FactSales | Difference0 | ResultAgree |
| Invoice lines: reference model to data warehouseInvoice lines in the reference model against WideWorldImportersDW's sales fact table. | Left228,265Showcase model.FactSales | Right228,265WideWorldImportersDW.Fact.Sale | Difference0 | ResultAgree |
| Revenue (tax-exclusive)Revenue (tax-exclusive) in the reference model against WideWorldImportersDW, summed over every month and territory. | Left$172,261,341.20Reference model | Right$172,261,341.20WideWorldImportersDW | Difference$0.00 | ResultAgree |
| ProfitProfit in the reference model against WideWorldImportersDW, summed over every month and territory. | Left$85,729,180.90Reference model | Right$85,729,180.90WideWorldImportersDW | Difference$0.00 | ResultAgree |
Most reports compute "this month" from today's date. When a load runs late, the top of the report goes blank or half-empty, and people stop trusting it.
Here every time-based measure is anchored to the watermark the pipeline records: the latest date it actually loaded. The report always answers "as of when?".
Watermark:Data as of 31 May 2016
| Measure | Window | Value |
|---|---|---|
| As-of Date | 31 May 2016 | |
| Revenue MTD | 1 May 2016 to 31 May 2016 | $4,970,932.65 |
| Revenue Last 30 Days | 2 May 2016 to 31 May 2016 | $4,970,932.65 |
| Revenue MTD PY | 1 May 2015 to 31 May 2015 | $4,480,730.55 |
| Revenue Last 30 Days PY | 2 May 2015 to 31 May 2015 | $4,273,392.50 |
Computed by the pipeline in SQL. The report's DAX measures are checked against these values.
Revenue MTD = VAR AsOf = [As-of Date] RETURN CALCULATE ( [Revenue MTD (at date)], REMOVEFILTERS ( DimDate ), TREATAS ( { AsOf }, DimDate[Date] ) ) Revenue Last 30 Days = VAR AsOf = [As-of Date] RETURN CALCULATE ( [Revenue Last 30 Days (at date)], REMOVEFILTERS ( DimDate ), TREATAS ( { AsOf }, DimDate[Date] ) ) Revenue MTD PY = VAR AsOf = [As-of Date] RETURN CALCULATE ( [Revenue MTD PY (at date)], REMOVEFILTERS ( DimDate ), TREATAS ( { AsOf }, DimDate[Date] ) ) Revenue Last 30 Days PY = VAR AsOf = [As-of Date] RETURN CALCULATE ( [Revenue Last 30 Days PY (at date)], REMOVEFILTERS ( DimDate ), TREATAS ( { AsOf }, DimDate[Date] ) ) As-of Date = MAX ( Freshness[Watermark] )
A short call on your sources, your users and the decisions the report should support.
What is included and what is not, agreed in writing before any work starts.
Built as code and checked automatically, with your review on a working draft.
Everything is delivered into your repository and your Power BI workspace.
Choose one, then answer what you can. Rough answers are fine.
This builds an email in your own mail app. Nothing is sent from this page, and nothing you type here is stored.
Choose one to see the questions.
No mail app? Write to hello@expresslint.com.
What you put into the tools never leaves your browser. We count page views and keep them for 90 days. Accept and we also remember this browser, so a return visit is not counted as new. Privacy policy