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