Power Query
Transformations inside Power BI. No extra infrastructure.
Best when: modest volumes, and analysts will maintain it.
Power BI showcase, open data
Power BI and data pipeline work, on open data
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.
The pipeline, end to end
Five stages. Each one leaves something you can check, so a wrong number can be traced back to where it came from.
- 01 · EXTRACT
Load each source
Load from each source on a schedule: a database, an API, a spreadsheet.
- 02 · STAGE
Land it untouched
Keep raw copies in staging tables before anything is changed.
- 03 · TRANSFORM
Clean and conform
Fix types, remove duplicates and convert currencies at each day's ECB rate.
- 04 · MODEL
Star schema
Sales facts in the middle, with dimensions such as date, customer and product around them. One version of every number.
- 05 · REPORT
Power BI
Planned for this demo: a semantic model with a documented measure set and a report on top, its DAX checked against the values the pipeline computes.
Latest load of the demo
Finished 30 Sep 2026, 04:33 UTC. Data as of 31 May 2016.
| Step | Written to | Rows | Time |
|---|---|---|---|
| ECB exchange rates | stg.FxRate | 1,461 | 13.5 s |
| Budget workbook (synthetic) | stg.Budget | 324 | 0.03 s |
| Watermark | meta.Freshness | not counted | 0.13 s |
Which tool does the work
The pipeline has the same shape either way. What runs it depends on where your data lives and who maintains it after handover.
SQL Server, Azure SQL or Fabric
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.
Python scripts
Scripted extracts and rules. The loaders in this demo are Python.
Best when: APIs, messy files, or business rules too custom for a GUI.
Every load is checked
A report is only as good as its last refresh. The pipeline runs these checks after each load.
42 of 42 checks passed. Run 30 Sep 2026, 04:33 UTC.
Budget covers every month
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
Show the 5 checks
- budget_base_matches_actuals passedThe budget's base revenue matches prior-year actuals from the reference model.
- budget_extra_cells passedThe budget has no rows outside its 36-month, 9-territory grid.
- budget_missing_cells passedNo month and territory pair is missing from the budget.
- budget_null_amounts passedEvery budget cell has a base and a budget amount.
- budget_rounding passedThe budget is exactly prior-year revenue times 1.05, rounded to the nearest 100.
Database collation matches the source
Showcase uses the same text collation as WideWorldImporters, so territory and product names compare and join correctly across databases.
1 of 1 check passed
Show the 1 check
- showcase_collation_matches_source passedThe Showcase database's collation matches WideWorldImporters.
Row counts reconcile
The reference model and the Microsoft data warehouse (WideWorldImportersDW) agree on revenue, profit, row counts and sales territory names.
5 of 5 checks passed
Show the 5 checks
- dw_profit_diff passedThe reference model and WideWorldImportersDW agree on profit, by month and territory.
- dw_revenue_diff passedThe reference model and WideWorldImportersDW agree on revenue, by month and territory.
- dw_row_count passedThe reference model and WideWorldImportersDW report the same number of sales lines.
- dw_row_count_by_cell passedThe reference model and WideWorldImportersDW agree on row counts, by month and territory.
- dw_territory_names_match passedEvery sales territory name in the reference model exists in WideWorldImportersDW.
Every sale links to a known customer, product and date
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
Show the 7 checks
- budget_orphan passedEvery budget row's month and territory resolve against the reference model's own calendar and territory list.
- fact_duplicate_lines passedEach invoice line appears once in the reference model, however many times it was loaded.
- fact_missing_rate passedEvery sales line has a matching exchange rate for its invoice date.
- fact_orphan_customer passedEvery sales line links to a known customer.
- fact_orphan_date passedEvery sales line falls on a date in the reference calendar.
- fact_orphan_product passedEvery sales line links to a known product.
- fact_row_count passedThe reference model has exactly one row per source invoice line, none dropped.
A rate for every day
The ECB publishes no rates on weekends and holidays, so the last rate carries forward.
7 of 7 checks passed
Show the 7 checks
- fx_fill_consistency passedA carried-forward rate always matches the day it was carried from.
- fx_fill_span passedNo gap in ECB publication is longer than the 4-day cap (the longest real gap, Easter).
- fx_missing_days passedNo calendar day is left without a rate.
- fx_null_or_nonpositive passedEvery rate is a real, positive number.
- fx_orientation passedEvery rate is in the expected range for USD per EUR, not inverted.
- fx_out_of_range passedNo rate falls outside the demo's 2013 to 2016 date range.
- fx_row_count passedOne rate row for each day from 2013 to 2016.
The reporting numbers add up
The report's monthly, territory and KPI aggregates tie back to the reference model and to each other.
7 of 7 checks passed
Show the 7 checks
- ref_asof_edge_case_no_prior_year passedAn as-of date before any prior-year data exists correctly reports no prior-year value.
- ref_asof_edge_cases_row_count passedThe as-of measures were checked at fixed edge dates, not only at today's watermark.
- ref_asof_values_row_count passedEvery as-of measure is reported in both currencies.
- ref_budget_total_matches_stg passedThe reported budget total matches the underlying budget workbook, for the months shown.
- ref_kpi_period_matches_watermark passedThe KPI tiles cover year-to-date at the live watermark, not a stale or hardcoded period.
- ref_monthly_total_matches_facts passedThe monthly revenue-vs-budget total matches the reference model's own total.
- ref_territory_ytd_matches_kpis passedThe territory revenue total matches the KPI tiles' all-territories total.
Source databases are online and loaded
WideWorldImporters and WideWorldImportersDW are restored, online, and their core tables are not empty.
8 of 8 checks passed
Show the 8 checks
- db_online_WideWorldImporters passedThe WideWorldImporters sample database is restored and online.
- db_online_WideWorldImportersDW passedThe WideWorldImportersDW sample database is restored and online.
- nonempty_WideWorldImporters.Application.Cities passedWideWorldImporters has cities loaded.
- nonempty_WideWorldImporters.Application.StateProvinces passedWideWorldImporters has state provinces loaded.
- nonempty_WideWorldImporters.Sales.Customers passedWideWorldImporters has customers loaded.
- nonempty_WideWorldImporters.Sales.InvoiceLines passedWideWorldImporters has invoice lines loaded.
- nonempty_WideWorldImporters.Sales.Invoices passedWideWorldImporters has invoices loaded.
- nonempty_WideWorldImportersDW.Fact.Sale passedWideWorldImportersDW has its sales fact table loaded.
The watermark matches the data
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
Show the 2 checks
- manifest_watermark_matches_freshness passedThe latest successful load recorded the same watermark that is live today.
- watermark_matches_source passedThe recorded watermark equals the latest invoice date in WideWorldImporters.
Totals compared across systems
| 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 |
Measures that know how fresh the data is
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
Reference values at the watermark
| 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.
What it looks like at the end
Revenue against a synthetic budget, in either currency, by month and by sales territory. The figures are the demo's own output.
Data as of 31 May 2016. The budget is synthetic.
Sales performance
Revenue$22.6M1 Jan 2016 to 31 May 2016
vs budget (synthetic)-5.0%1 Jan 2016 to 31 May 2016
Gross margin49.4%1 Jan 2016 to 31 May 2016
Active customers6631 Jan 2016 to 31 May 2016
Revenue vs budget by month, 1 Jan 2013 to 31 May 2016
RevenueBudget (synthetic)
The synthetic budget starts in Jan 2014. It is based on the year before, so the first year of data has none.
Revenue by sales territory, 1 Jan 2013 to 31 May 2016
- Southeast$38.3M
- Mideast$25.8M
- Southwest$23.9M
- Plains$23.3M
- Great Lakes$20.2M
- Far West$19.9M
- Rocky Mountain$11.1M
- New England$7.7M
- External$2.2M
Show the figures behind these charts
| 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 |
The budget comparison by territory comes with the next update of the demo.
How a project runs
Scoping call
A short call on your sources, your users and the decisions the report should support.
A written scope
What is included and what is not, agreed in writing before any work starts.
Build
Built as code and checked automatically, with your review on a working draft.
Handover
Everything is delivered into your repository and your Power BI workspace.
Questions clients ask
- Do you need access to our production data?
- Not necessarily. I can work on a copy or a sample, or build the pipeline so your team runs it against production.
- Will you sign an NDA?
- Yes. Your data and your report stay private. Only open data appears on this site.
- Do we need Power BI Premium or Fabric?
- Not always. Pro licences cover many small teams. I confirm the cheapest setup that works for you during scoping.
- Who owns the work?
- You do. Everything is delivered into your repository and your Power BI workspace.
Tell me about your data
Five questions are enough to scope most projects. 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.
Compose email
No mail app? Write to vish21sep@gmail.com.