digest
Short Summary
Main recommendation: Use Power Query to import, clean, and merge related Excel data sources (transactions and a product lookup table), then load the combined result into the Excel Data Model (Power Pivot) rather than a worksheet table when you want a reportable, refreshable model. The video demonstrates this with a sales dataset, building the relationship through Power Query's Merge feature, then creating a net profit measure in Power Pivot and a pivot table with a company slicer.
Key Points
- Power Query's Merge operation works like a VLOOKUP-style relationship between two tables, pulling in product and company columns from the product table based on a matching key column.
- The product ID in the transaction source has extra characters after a second dash, so it must be split to match the product table's IDs before merging.
- Loading the query as a connection only, then adding it to the Data Model, keeps the data compact and avoids dumping a large table onto a worksheet.
- A net profit measure is created in Power Pivot as a calculated field (a measure), not a calculated column, using an iterating expression over the final sales and cost table.
Details Worth Noting
- Concrete instructions:
- Import the transaction data via Get External Data from File, choose Excel, double-click the sheet's table in the Navigator, then choose Edit to open Power Query.
- Remove the invoice number column, since it is not needed for reports.
- Select the product ID column, use Split Column by Delimiter, choose Custom, type a dash, and select "At the right-most delimiter." This keeps the portion up to the second dash.
- Remove the extra split column created by the split, then rename the remaining column back to Product ID, removing the trailing ".1" suffix.
- Close and Load To, choose "Only Create Connection," and load.
- Import the product table from a text file via From Text, and in the preview click the upper-left drop-down and choose "Use First Row as Headers." Rename the query to D_Product.
- Close and Load To, choose "Only Create Connection," and load.
- In Power Query, use Merge Queries with transactions as the first table and the product table as the second. Select Product ID in both tables and choose a join. Expand the new table column and select Product and Company, then rename them to Product and Company.
- Remove the now-redundant Product ID column from the merged query, name the query (e.g., "Final Sales and Cost Table"), then choose Close and Load To, select "Only Create Connection," and check "Add this data to the Data Model."
- In Power Pivot (Manage), format the date column as a date, then create a measure named Net Profit with an expression that iterates over the final sales and cost table and subtracts cost from final net sales. Format it as currency.
- Create a pivot table from the Data Model, add Net Profit to Values and Product to Rows, then insert a slicer for Company.
- The presenter notes that the same result could be achieved in plain Excel without Power Pivot, but the Data Model approach is shown to highlight how Power Query can transform data and feed either a table or the model.
- The dataset in the example is small (about 1,421 rows), so normalization is not a concern, and Power Pivot's columnar storage compresses the data well.
- Merge is distinguished from Append: Append stacks tables, while Merge creates a lookup-style relationship between tables.
Bottom Line
Power Query's Merge plus loading to the Data Model lets you combine lookup data into a clean, reportable model without manual VLOOKUPs, and Power Pivot measures then drive pivot tables and slicers.
