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.

Dashboard examples

Five dashboards on one model. Open one, try it in each theme, or take the theme for your own report.

Executive dashboard

4 pages · in Harbour

Executive overview

Data to 31 May 2016

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
RevenueBudget (synthetic)

Revenue against the synthetic budget by month, 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

Revenue by sales territory, 1 Jan 2013 to 31 May 2016.

The theme file works in Power BI Desktop: Design, Import theme. It holds colours only, no data.

How to use these files

Theme

  1. Open your report in Power BI Desktop.
  2. On the Design tab, choose Import theme.
  3. Pick the file. Your visuals take its colours. Colours you set on a visual yourself stay as they are.

Step by step, with screenshots

Sample data

  1. In Power BI Desktop, choose Get data, then Text/CSV.
  2. Pick a file and choose Load.
  3. Build visuals on it, then try a theme from this page.

Theme files hold colours only. The sample data is the open Wide World Importers demo, the same numbers as above.

Show the numbers
Monthly, 1 Jan 2013 to 31 May 2016
MonthRevenueBudget (synthetic)
Jan 2013$3,770,410.85none
Feb 2013$2,776,786.20none
Mar 2013$3,870,505.30none
Apr 2013$4,059,606.85none
May 2013$4,417,965.55none
Jun 2013$4,069,036.20none
Jul 2013$4,381,767.45none
Aug 2013$3,495,991.00none
Sep 2013$3,779,040.85none
Oct 2013$3,752,608.45none
Nov 2013$3,697,461.90none
Dec 2013$3,636,007.40none
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
By sales territory, 1 Jan 2013 to 31 May 2016
TerritoryRevenue
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
Revenue and budget, 1 Jan 2014 to 31 May 2016
TerritoryRevenueBudget (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.

By customer category, 1 Jan 2013 to 31 May 2016
Customer categoryRevenue
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
By territory and buying group, 1 Jan 2013 to 31 May 2016
TerritoryBuying groupRevenue
ExternalTailspin Toys$797,736.15
ExternalWingtip Toys$1,044,206.60
ExternalNo buying group$358,195.85
Far WestTailspin Toys$8,057,345.80
Far WestWingtip Toys$5,053,531.30
Far WestNo buying group$6,768,715.70
Great LakesTailspin Toys$4,915,771.15
Great LakesWingtip Toys$8,505,232.30
Great LakesNo buying group$6,732,230.40
MideastTailspin Toys$8,264,736.35
MideastWingtip Toys$9,061,783.10
MideastNo buying group$8,431,789.85
New EnglandTailspin Toys$2,762,904.70
New EnglandWingtip Toys$1,705,126.70
New EnglandNo buying group$3,228,023.40
PlainsTailspin Toys$8,389,768.50
PlainsWingtip Toys$5,769,154.50
PlainsNo buying group$9,148,252.85
Rocky MountainTailspin Toys$4,058,373.05
Rocky MountainWingtip Toys$3,113,273.90
Rocky MountainNo buying group$3,905,303.00
SoutheastTailspin Toys$10,900,993.20
SoutheastWingtip Toys$11,142,090.85
SoutheastNo buying group$16,222,081.70
SouthwestTailspin Toys$6,349,580.70
SouthwestWingtip Toys$8,841,036.65
SouthwestNo buying group$8,734,102.95

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.

  1. 01 · EXTRACT

    Load each source

    Load from each source on a schedule: a database, an API, a spreadsheet.

  2. 02 · STAGE

    Land it untouched

    Keep raw copies in staging tables before anything is changed.

  3. 03 · TRANSFORM

    Clean and conform

    Fix types, remove duplicates and convert currencies at each day's ECB rate.

  4. 04 · MODEL

    Star schema

    Tables in the Power BI model: DimDate, DimSalesTerritory, DimCustomer, DimProduct, FactSales, FactBudget, Currency.

  5. 05 · REPORT

    Power BI

    A semantic model with a documented measure set, and a report on top. Its DAX is checked against the values the pipeline computes.

Latest load of the demo

Finished 15 Nov 2026, 05:46 UTC. Data as of 31 May 2016.

StepWritten toRowsTime
ECB exchange ratesstg.FxRate1,46144.21 s
Budget workbook (synthetic)stg.Budget3240.03 s
Watermarkmeta.Freshnessnot counted0.15 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.

Power Query

Transformations inside Power BI. No extra infrastructure.

Best when: modest volumes, and analysts will maintain it.

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.

Inside the Power Query pipeline

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.

Applied steps, DimDate
fx= List.Dates(#date(2013, 1, 1), 1461, #duration(1, 0, 0, 0))

Every load is checked

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.

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.

13 of 13 checks passed

Show the 13 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_buying_groups_match_territory_facts passedEach territory's revenue by buying group adds up to that territory's revenue in the reference model, customers with no group included.
  • ref_category_total_matches_facts passedRevenue by customer category adds up to the reference model's revenue for the period.
  • ref_kpi_period_matches_watermark passedThe KPI tiles cover year-to-date at the live watermark, not a stale or hardcoded period.
  • ref_lfl_budget_matches_facts passedLike-for-like budget matches the synthetic budget for the same months.
  • ref_lfl_revenue_matches_facts passedLike-for-like revenue matches the reference model's sales from the first budget month to the watermark.
  • ref_lfl_row_count passedThe like-for-like budget comparison has one row per sales territory plus a total.
  • ref_lfl_territory_budget_sums_to_total passedThe territories' like-for-like budgets add up to the total.
  • 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

Invoice lines: source to reference modelInvoice lines in WideWorldImporters against the reference model's fact table.Left228,265WideWorldImporters.Sales.InvoiceLinesRight228,265Showcase model.FactSalesDifference0ResultAgree
Invoice lines: reference model to data warehouseInvoice lines in the reference model against WideWorldImportersDW's sales fact table.Left228,265Showcase model.FactSalesRight228,265WideWorldImportersDW.Fact.SaleDifference0ResultAgree
Revenue (tax-exclusive)Revenue (tax-exclusive) in the reference model against WideWorldImportersDW, summed over every month and territory.Left$172,261,341.20Reference modelRight$172,261,341.20WideWorldImportersDWDifference$0.00ResultAgree
ProfitProfit in the reference model against WideWorldImportersDW, summed over every month and territory.Left$85,729,180.90Reference modelRight$85,729,180.90WideWorldImportersDWDifference$0.00ResultAgree

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

MeasureWindowValue
As-of Date31 May 2016
Revenue MTD1 May 2016 to 31 May 2016$4,970,932.65
Revenue Last 30 Days2 May 2016 to 31 May 2016$4,970,932.65
Revenue MTD PY1 May 2015 to 31 May 2015$4,480,730.55
Revenue Last 30 Days PY2 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.

DAX, as saved in the model
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] )

How a project runs

  1. Scoping call

    A short call on your sources, your users and the decisions the report should support.

  2. A written scope

    What is included and what is not, agreed in writing before any work starts.

  3. Build

    Built as code and checked automatically, with your review on a working draft.

  4. 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 what you need

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.

What do you need?

Choose one to see the questions.

Compose email

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