Column not found in Power Query

A pipeline that works today has to survive a source that changes tomorrow. Schema drift means the upstream system has renamed, dropped, added, or reordered a column. It's the commonest way a refresh breaks, and the fixes are a set of defensive habits.

Reference by name, never by position. A step like Table.SelectColumns(Source, {"Date","SKU","Revenue"}) refers to columns by name, so reordering can't touch it. Shuffle the source columns and it still finds the three it wants. Now picture a fragile step that assumes column positions, which some transforms let you do by indexing columns numerically. Insert one column upstream and it grabs the wrong data without a word of warning. Name-based references are the schema-drift version of the "never hardcode a column index" rule from lookups.

How do you handle a missing column?

Tolerate a missing column. By default, asking for a column that isn't there is a hard error. If a source sometimes omits a column, opt into tolerance:

Table.SelectColumns(Source, {"Date", "SKU", "Discount"}, MissingField.UseNull)

MissingField.UseNull returns a column of nulls instead of erroring when Discount is absent. MissingField.Ignore simply skips it. That one option turns "the whole refresh fails because January had no discounts" into "the Discount column is null for January," and downstream steps can handle that gracefully.

Type defensively and late-bind names. Set types explicitly. Don't rely on the auto-detected Changed Type step that Power Query inserts. It hardcodes the column list as it stood at design time, so it breaks the moment a column appears or vanishes, and many drift bugs trace back to it. Where a header might be renamed upstream, rename it to your canonical name early, so the rest of the pipeline speaks one vocabulary.

How do folding and buffering affect refresh speed?

Folding and buffering for performance. Two ideas govern refresh speed.

• Query folding is the engine's ability to translate your steps back into the source's own language, such as SQL for a database, and let the source do the work. A folded filter runs as a WHERE clause on the server and returns only the rows you keep. Pulling everything into Excel and filtering locally is hugely slower. Folding survives simple, source-expressible steps (filter rows, remove/rename columns, group). It breaks at anything the source can't express: a custom M function, an added index column, Table.Buffer, or many text transforms. So as a rule of thumb, do the folding-friendly steps first. Filter and trim columns early, near the source, so less data crosses the wire, and push the folding-breakers late.
• Buffering with Table.Buffer, or List.Buffer, loads a value fully into memory and freezes it, so the engine doesn't re-evaluate it over and over. It pays off when a table is referenced many times, such as inside a per-row lookup. But buffering stops folding, because a buffered table is now a local snapshot. Treat it as a deliberate, measured choice. Don't sprinkle it everywhere.

Why load staging queries as connection only?

Load connection-only. A pipeline usually has staging queries that exist only to feed the final table: the file list, the sample, the per-file function. Load every one of them to a worksheet or the data model and you bloat the file and multiply refresh time. Set intermediate queries to Connection Only instead, and load just the final result. In the Import dialog, choose Only Create Connection, or right-click the query ▸ Load To ▸ Only Create Connection. A tidy pipeline produces one output, and everything upstream of it is connection-only scaffolding.