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.2Profile and clean the data
Understand evaluating data with data profiling (column quality/distribution/statistics) and resolving inconsistencies, nulls, error values, and import errors in Power Query.
Before modeling, understand the data’s state and remove dirt. The standard flow is to find issues via data profiling and fix them in Power Query.
1.2.1Data profiling
Use column quality (valid/error/empty %), column distribution (distinct/unique counts), and column profile (min/max/average/distinct statistics) in the Power Query editor to evaluate data. Profiling defaults to the top 1000 rows, so switch to profiling based on the entire dataset when needed.
1.2.2Cleansing (inconsistencies, nulls, errors)
Normalize inconsistent text via replace values or formatting (trim/clean/case). Handle nulls with fill up/down, replacement, or removing affected rows. Fix error values at the cause (e.g., failed type conversion) or remove/replace errors. Import errors often stem from type/delimiter/encoding mismatches—revisit source settings or steps. Each action is recorded as an applied step, keeping it reproducible.
Cues: "valid/error/empty %" = column quality. "distinct/unique counts" = column distribution. "min/max/average stats" = column profile. "evaluate all data" = switch to profiling based on the entire dataset. "fill nulls" = fill. "failed type conversion" = remove/replace errors or fix the type.
Watch the mix-ups: (1) Profiling defaults to the top 1000 rows—switch for full evaluation. (2) Null vs error differ (null=no value / error=evaluation failed). (3) Cleanse in Power Query (at load), separate from DAX (model side). (4) Step order changes results.
1.2.3Section summary
- Evaluate via column quality/distribution/profile; switch for whole-dataset profiling
- Normalize with replace/format; nulls via fill; errors via remove/replace or fix the cause
- Cleanse via reproducible applied steps in Power Query
Sign in to track progress — Log in.
Quick check
(just a quick review)Q1. You want to see each column’s valid/error/empty percentages at a glance. Which Power Query feature?
Q2. You want statistics like min/max/average/distinct counts for a column. Which feature?
Q3. Profiling only looks at the top 1000 rows; you want to evaluate the whole dataset. Best action?
Q4. A column has many nulls and you want to fill them with the previous valid value. Best operation?
Q5. Rows with failed type conversion became error values; you want to exclude them from the report. Best operation?