What's changed: Created PL-300 Chapter 1 (domain: Prepare the data): getting/connecting data (sources/shared semantic model, Import/DirectQuery/DirectLake, credentials/privacy, parameters), profiling/cleansing (column quality/distribution/profile, null/error/import errors), and transforming/loading (types/columns/grouping, unpivot, fact/dimension, merge/append, reference/duplicate, load config).
1.3Transform and load the data
Understand column types/creation, grouping/aggregation, pivot/unpivot, building fact/dimension tables, reference vs duplicate, merge vs append, relationship keys, and query load configuration.
Shape the cleaned data into an analysis-friendly form (star schema) and load it. Whoever controls the shape controls the model.
1.3.1Types, columns, aggregation, pivot
Set the right data type per column and, if needed, create/transform columns (split/extract/custom). Group rows to aggregate (sum/count, etc.). Unpivot turns wide columns into tall attribute/value pairs; pivot does the reverse. Many analyses are easier after unpivoting to a tall shape. Expand semi-structured data (JSON/list/record) into a table.
1.3.2Fact/dimension and combining
For analysis, a star schema splitting fact tables (numeric/event data behind measures) from dimension tables (descriptive attributes like product/date/customer) is recommended. There are two ways to combine: merge joins by columns (bring another table’s columns via a common key = like SQL JOIN), and append joins by rows (stack same-shaped tables = like UNION). Relationships require a unique key on the appropriate table.
1.3.3Reference vs duplicate; load configuration
Reference builds a new query from another query’s result, so upstream changes flow downstream (good for staging/reuse). Duplicate is an independent copy that then changes separately. Disable load (uncheck Enable load) for intermediate queries so they are not added to the model, avoiding unnecessary tables.
Cues: "bring another table’s columns via a key" = merge (JOIN). "stack same-shaped tables" = append (UNION). "wide→tall" = unpivot. "numbers/events" = fact; "descriptive attributes" = dimension. "propagate upstream changes" = reference; "independent copy" = duplicate. "keep intermediate out of model" = disable load.
Watch the mix-ups: (1) Do not swap merge (columns) and append (rows). (2) Pivot and unpivot are inverses. (3) Reference (dependent) vs duplicate (independent). (4) Do not confuse fact and dimension. (5) Disable load on intermediate queries or the model bloats.
1.3.4Section summary
- Merge=columns (JOIN) / append=rows (UNION); unpivot=wide→tall
- Fact (numbers/events) + dimension (attributes) = star schema
- Reference=dependent / duplicate=independent; disable load on intermediates
Sign in to track progress — Log in.
Quick check
(just a quick review)Q1. You want to bring product-master columns into the sales table using a common product ID key. Best operation?
Q2. You have 12 monthly files with the same columns and want to stack them into one table. Best operation?
Q3. You want to turn wide data (each month a column) into a tall month/value shape. Best operation?
Q4. You want intermediate staging that propagates upstream changes downstream. Best?
Q5. In star-schema design, which table holds numeric event data such as sales amount and quantity?