Power QueryExercises

Orders, customers and regions

Three related CSVs with an order for a customer that doesn't exist and a customer in an unknown region. Practise join kinds and find the orphans.

intermediate · Skills: Merge queries, Left outer and left anti joins, Unmatched keys · Updated 2026-11-04

Data files

Queries

Validated in the Power Query Exercises simulator against the files above. Paste them into Excel or Power BI yourself; the header comments explain each source binding.

Orders with customer and region orders-with-customers.pq

Download .pq

Every order kept (left outer); missing customers and regions labelled. Returns 18 rows: OrderID, CustomerID, OrderDate, Amount, Name, RegionCode, Region.

// Orders with customer and region
// Pack: Orders, customers and regions (Power Query Exercises, https://powerqueryexercises.com/downloads/orders-customers-merge)
//
// Source bindings
// In the Power Query Exercises simulator each name below is bound directly to a pack file. In Excel or
// Power BI Desktop the first steps of this query do that binding instead:
//   Orders         the pack file orders.csv -> File.Contents(PackFolder & "orders.csv")
//   Customers      the pack file customers.csv -> File.Contents(PackFolder & "customers.csv")
//   Regions        the pack file regions.csv -> File.Contents(PackFolder & "regions.csv")
// Set PackFolder to the folder where you extracted the pack (keep the trailing backslash).
//
// Validated in the Power Query Exercises simulator (engine 0.1.0) against this pack's files; it returns
// 18 rows with columns OrderID, CustomerID, OrderDate, Amount, Name, RegionCode, Region.
// It has not been run in Excel or Power BI by us. This is query text, not a ready-to-refresh workbook.
// Independent practice material; not affiliated with or endorsed by Microsoft.
let
    PackFolder = "C:\PQE\orders-customers-merge\",
    Orders = File.Contents(PackFolder & "orders.csv"),
    Customers = File.Contents(PackFolder & "customers.csv"),
    Regions = File.Contents(PackFolder & "regions.csv"),
O = Table.TransformColumnTypes(Table.PromoteHeaders(Csv.Document(Orders, [Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv])), {{"OrderDate", type date}, {"Amount", type number}}, "en-US"),
    C = Table.PromoteHeaders(Csv.Document(Customers, [Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv])),
    R = Table.PromoteHeaders(Csv.Document(Regions, [Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv])),
    #"With customer" = Table.ExpandTableColumn(Table.NestedJoin(O, {"CustomerID"}, C, {"CustomerID"}, "C", JoinKind.LeftOuter), "C", {"Name", "RegionCode"}),
    #"With region" = Table.ExpandTableColumn(Table.NestedJoin(#"With customer", {"RegionCode"}, R, {"RegionCode"}, "R", JoinKind.LeftOuter), "R", {"Region"}),
    #"Labelled" = Table.TransformColumns(#"With region", {{"Name", each if _ = null then "Unknown customer" else _, type text}, {"Region", each if _ = null then "Unassigned" else _, type text}})
in
    #"Labelled"

Orphan orders orphan-orders.pq

Download .pq

Orders whose customer isn't in customers.csv (left anti join). Returns 2 rows: OrderID, CustomerID, OrderDate, Amount.

// Orphan orders
// Pack: Orders, customers and regions (Power Query Exercises, https://powerqueryexercises.com/downloads/orders-customers-merge)
//
// Source bindings
// In the Power Query Exercises simulator each name below is bound directly to a pack file. In Excel or
// Power BI Desktop the first steps of this query do that binding instead:
//   Orders         the pack file orders.csv -> File.Contents(PackFolder & "orders.csv")
//   Customers      the pack file customers.csv -> File.Contents(PackFolder & "customers.csv")
// Set PackFolder to the folder where you extracted the pack (keep the trailing backslash).
//
// Validated in the Power Query Exercises simulator (engine 0.1.0) against this pack's files; it returns
// 2 rows with columns OrderID, CustomerID, OrderDate, Amount.
// It has not been run in Excel or Power BI by us. This is query text, not a ready-to-refresh workbook.
// Independent practice material; not affiliated with or endorsed by Microsoft.
let
    PackFolder = "C:\PQE\orders-customers-merge\",
    Orders = File.Contents(PackFolder & "orders.csv"),
    Customers = File.Contents(PackFolder & "customers.csv"),
O = Table.PromoteHeaders(Csv.Document(Orders, [Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv])),
    C = Table.PromoteHeaders(Csv.Document(Customers, [Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv])),
    #"Orphans" = Table.NestedJoin(O, {"CustomerID"}, C, {"CustomerID"}, "C", JoinKind.LeftAnti),
    #"Removed" = Table.RemoveColumns(#"Orphans", {"C"})
in
    #"Removed"

Follow along in a host

Follow along in Excel

  1. Download the pack and extract the zip, for example to C:\PQE\<pack name>\.
  2. In Excel (Microsoft 365 or 2016+ on Windows), go to Data → Get Data → From Other Sources → Blank Query.
  3. In the Power Query Editor choose Home → Advanced Editor, select everything and paste the contents of the .pq file.
  4. Change the PackFolder line to the folder you extracted to, then choose Done.
  5. If Excel asks about privacy levels or file access, allow access to that folder. Then choose Home → Close & Load.

Follow along in Power BI Desktop

  1. Download the pack and extract the zip.
  2. In Power BI Desktop choose Home → Transform data to open the Power Query Editor, then Home → New Source → Blank Query.
  3. Choose Home → Advanced Editor, paste the contents of the .pq file and change the PackFolder line to your folder.
  4. Choose Done, check the preview against the expected rows in the query header, then Close & Apply.

Then try this

  1. Change JoinKind.LeftOuter to JoinKind.Inner in the first query and count how many orders disappear.
  2. Add customer C-099 to customers.csv and refresh the orphan query: it becomes empty.

Related lessons

Original fictional data, free to use for learning and teaching. Power Query Exercises is not affiliated with or endorsed by Microsoft.