digest
Short Summary
Main recommendation: Split repeated text columns such as city names out of a large CSV fact table into a separate lookup (Dimension) table, replacing the text with an index-based City ID, then link the two tables in Power Pivot to reduce file size and speed up calculations. The video demonstrates building this with Power Query's From Folder import so the lookup table updates automatically when new CSV files are added and refreshed.
Key Points
- The lookup table is built from the file names: the "City" name is extracted from each file name (split on a dash and a period), and an index column becomes the "City ID."
- The fact table keeps only the City ID in place of the repeated city names, which the presenter says saves file size when loaded into Power Pivot.
- A relationship between the fact table and the City dimension table in Power Pivot lets filters on one table (for example, a City slicer) flow through to the other, so Power Pivot calculates over fewer rows.
- Power Query's folder-based import means that adding new files to the folder and refreshing (Data > Refresh All) adds the new cities to the lookup table and the new sales rows to the fact table automatically.
Details Worth Noting
- Concrete instructions:
- In Power Query, choose From File > From Folder, browse to the City Data folder, and click OK twice.
- Right-click the Content column and choose Remove Other Columns, keeping only the file name for the lookup table.
- Split the file name first with a Custom delimiter of a dash (-), remove the unneeded piece, then split again with a Custom delimiter of a period (.) and remove the extension piece.
- Rename the remaining column "City," then add an Index Column (moved to the front) and rename it "City ID."
- Close and Load To > Only Create Connection, and name the query "D City table."
- For the fact table, import the same folder again, keep only the Content column, expand and set data types (Date, Text for the store/destination field, and Sales), then filter out rows where the City column reads "City" (the repeated header rows in the source files).
- Name the imported query "Imported Tables," load it as a connection only, then Merge the two queries on the City column, expand the matched table to get the City ID, remove the City text column, and name the result "F All Data."
- Load "F All Data" as a connection only, then right-click it and choose Load To > Load to Data Model. Load the "D City table" to the data model as well.
- In Power Pivot (Manage), go to Diagram View to confirm the relationship on City ID, then insert a PivotTable with Store ID in Rows and Sales in Values, and add a slicer from City.
- To test, paste new CSV files into the folder, then use Data > Refresh All in Excel and check the City table in Power Pivot.
- The presenter notes that the Remove Other Columns and filter steps are used because the file names and header rows are repeated; the "Not City" filter is a simple method that works only when the number of distinct city values is small.
- The video mentions that a column-based method will be used in the next video, and notes that the method used here (filtering) only works because there are few distinct cities; the column method is needed when there are too many different items.
Bottom Line
A separate lookup table plus a Power Pivot relationship keeps large sales data lean and fast to filter, and a folder-based Power Query setup lets the workbook pick up new files on refresh without manual rebuilding.
