// 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"