Level 4 · A lean import that folds

The cheapest data is the data you never import. Every column you load is compressed, stored and scanned on every refresh; every row of a cancelled order is one more row someone has to filter out in DAX later.

What you are working in

This page is a practice editor for Power Query’s way of working, not Power Query itself. The Source step holds the imported table; you add steps one per line, and the preview shows the result after each step. Step names and results match Power Query’s.

In Power BI Desktop: Home → Transform data opens the Power Query Editor. Filter with the arrow on the status header (untick everything but completed), keep columns with Home → Choose Columns, and change the type with the icon left of the order_date header → Date. Finish with Home → Close & Apply.

The three steps

Filter rows: status = completed
Choose columns: order_id, order_date, channel, shipping_fee
Change type: order_date to date

The order matters for two reasons. You can’t filter on status after you have removed it. And in Power Query, steps that can be sent back to the source should come first.

Query folding

When the source is a database, Power Query tries to translate your steps into one query in the source’s language. This is query folding: the filter becomes a WHERE, Choose columns becomes the SELECT list, and the type change becomes a CAST. SQL Server does the work, and only the rows and columns you keep cross the network into Power BI.

Folding stops at the first step the source can’t express (many custom columns, some text functions, merging with an Excel file, adding an index column). Every step after that runs inside Power Query on the whole table it received. So put the steps that fold, above all filters and column choices, at the top, and the ones that don’t at the end.

To check, right-click a step in Applied Steps: if View Native Query is available, that step folds; if it is greyed out, folding stopped at or before it. Newer versions also show folding indicators next to each step.

Why a date and not a timestamp

A timestamp to the second has a different value on nearly every row, and VertiPaq compresses columns by their distinct values. The report needs days, so keep days: about 1,100 values for three years instead of hundreds of thousands. If someone later wants the hour, add an hour column, not the full timestamp.

Do it at the source when you can

Heavy shaping, such as joins of big tables or history going back years, is cheaper in a database view or the data warehouse than in Power Query. Power Query is best for light, foldable trimming. For large fact tables, incremental refresh (right-click the table in Desktop → Incremental refresh) reloads only the recent days on each refresh.

Common mistakes

• Removing columns instead of choosing them. New source columns sneak in later.
• Filtering in the report. A visual filter hides rows; the model still stores them.
• A non-folding step first. Everything after it runs on the full table.