Loading and Shaping Data
Shape once, refresh fast, always
Power Query is the ETL engine inside Power BI: connect to sources, shape (filter, rename, split, type), combine, and load into the model. Good habits here keep models clean and refreshes fast.
What you will learn
- Connect to a warehouse and load modeled tables
- Apply basic shaping: filter, rename, split, typecast, merge/append
- Understand query folding and when it happens
What is this?
Loading data in Power BI is Power Query work: Get Data connects to a source (SQL, Excel, file, web); transformation steps reshape it (filters, renames, splits, merges, appends); and the Load step pushes it into the in-memory model. Well-managed queries fold work back to the source (query folding), keeping refreshes fast and reproducible.
Why do you need it?
- The model's quality starts at the load: garbage in, garbage everywhere.
- Doing transformations in Power Query crashes scale; folding them to SQL is the pro move.
- Named, ordered transform steps make refreshes debuggable.
- Loading only needed tables/columns keeps the in-memory model lean.
Mental model
Prepping ingredients at a butcher counter
Power Query
You ask the butcher (source) to trim, slice and bag the amount you need (folding to the source). Prep you can't push back — like marinating — happens at your station (Power Query), recorded as recipe steps.
How it works
- Get Data builds a connection and preview; the engine records every applied step.
- Applied Steps forms an ordered pipeline; each step is reversible and debuggable.
- Query folding: when the source is a SQL engine, steps compile to SQL so filtering happens source-side.
- Non-folding work (split columns, custom ICU transforms) runs in the engine locally.
- The Load step determines import vs DirectQuery; import materials into memory, DirectQuery live-queries.
Power Query pipeline.
In production
- Analysts connect Power Query to the gold layer's views — shaping is minimal because modeling already happened in the warehouse.
- A monthly Excel archive is appended and split into a clean table before load.
- Query folding check: the Applied Steps show two queries folded (Source, Navigation) so a 10M-row filter happens in SQL.
Best practices
- Push heavy work to the warehouse; load already-modeled views.
- Enable query folding (rename after a filter step can break it — order steps carefully).
- Name queries meaningfully; a rename isn't a transformation you can audit later.
- Disable unnecessary loads (only enable the tables the model needs).
Common mistakes
- Doing joins after load instead of using warehouse views, losing fold and adding model bloat.
- Splitting columns with delimiters that change — fragile parsing steps.
- Typing everything as text 'to be safe', wrecking aggregations.
- Loading all columns 'just in case', bloating memory and slowing refresh.
Common issues
Filter step doesn't apply at sourceEarlier rename/reorder broke query folding
Reorder steps so filters run before reshapes; keep source column names where possible.
Appended monthly files misalign columnsHeaders/order changed across files
Normalize headers and column order per file before append.
Debugging tips
- Slow refresh? Check the Applied Steps for a non-folded filter running on millions of rows.
- Wrong type? Look at the last Type step — a changed source column range breaks casts.
- Data disappears? Verify the filter step's predicate still matches the new source values.
Interview checkpoint
Hands-on lab
- Replicate Get Data + Applied Steps in SQLite via Python
- Implement folding: push filters and projections to the engine
- Compare 'folded' vs 'local' processing costs
Shape a Data Import like Power Query
40 min · intermediate
Quick quiz
A filter that compiles into the source SQL is an example of:
The order of Applied Steps can break query folding.