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
- orders.csv — 18 orders: OrderID, CustomerID, OrderDate, Amount.Download CSV (627 B)
- customers.csv — 8 customers: CustomerID, Name, RegionCode.Download CSV (220 B)
- regions.csv — Region codes N, S, E and W.Download CSV (53 B)
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 .pqEvery 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 .pqOrders 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
- Download the pack and extract the zip, for example to C:\PQE\<pack name>\.
- In Excel (Microsoft 365 or 2016+ on Windows), go to Data → Get Data → From Other Sources → Blank Query.
- In the Power Query Editor choose Home → Advanced Editor, select everything and paste the contents of the .pq file.
- Change the PackFolder line to the folder you extracted to, then choose Done.
- 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
- Download the pack and extract the zip.
- In Power BI Desktop choose Home → Transform data to open the Power Query Editor, then Home → New Source → Blank Query.
- Choose Home → Advanced Editor, paste the contents of the .pq file and change the PackFolder line to your folder.
- Choose Done, check the preview against the expected rows in the query header, then Close & Apply.
Then try this
- Change JoinKind.LeftOuter to JoinKind.Inner in the first query and count how many orders disappear.
- 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.