Exported reports often put a category label in the first row of each group and leave blank cells below for all other rows in that group. This looks tidy in a formatted report but breaks every formula and PivotTable that expects each row to be self-contained. Fill Down fixes it instantly — it replaces every null with the last non-null value above it, propagating the label through all rows in the group. Fill Up does the reverse, pushing a footer label upward through null cells above it. The critical detail most users miss: Fill Down only replaces null, not empty strings. This guide covers the five-step workflow, how to convert empty strings to null before filling, how to prevent Fill Down from bleeding across group boundaries, and six examples including classic category propagation, merged-cell repair, footer label fill-up, multi-column simultaneous fill, and a running balance using List.Accumulate.

Fifty thousand transaction rows need to become a clean summary — total revenue by region, order count by product, average order value by month. Power Query’s Group By builds this in a few clicks and delivers a real flat table you can load anywhere, merge into other queries, or use as a lookup source. Advanced mode lets you add multiple group columns and multiple aggregations simultaneously. The All Rows aggregation type unlocks custom M expressions for patterns the UI doesn’t natively support — distinct customer counts, SUMIF-style conditional sums, and weighted averages. This guide covers both Basic and Advanced mode, six worked examples including regional revenue summaries, multi-metric aggregations, distinct customer counts, conditional aggregation using Table.SelectRows inside a group, and a revenue-weighted average price pattern.

Fifty thousand rows and duplicates inflating every total — Power Query’s Remove Duplicates fixes this in one step. But there’s a choice most people miss: which duplicate survives? Power Query always keeps the first row it encounters in the current order. Sort the table before deduplicating and you control exactly which row wins — the most recent, the highest-value, or the last entry received. This guide covers four deduplication patterns: entire-row exact matching, single-column key deduplication, the sort-first technique for keeping the most recent record, and the index-column trick that guarantees the last original row is preserved. Six worked examples include removing CRM duplicates, keeping one row per customer, flagging duplicates for manual review, composite-key deduplication, and case-insensitive matching.

When a cell holds “London, Paris, Berlin” and you need three separate rows, Split Column into Rows is the answer. Unlike Split to Columns — which creates a wide, ragged table with empty cells — splitting to rows stacks every value vertically under the same header. The result is clean, normalized data that works perfectly with PivotTables and Power Pivot relationships. This guide walks through the full five-step process, explains how to handle comma, semicolon, and line-break delimiters, shows how to remove blank rows caused by trailing delimiters, and covers six worked examples including product tag expansion, multi-select survey answers, mixed delimiter handling, and a rejoin-after-split pattern for tag popularity scoring.