Power Query

Power Query Editor prepares data before it reaches the model. Queries appear on the left, the data preview is in the centre and Applied Steps appear on the right. Each transformation becomes a repeatable step that runs again when you refresh.

Check data types and column quality first. Remove unnecessary rows and columns, trim text and handle nulls or errors according to what the data means. Do not replace every missing value with zero: missing and zero can represent different things.

Rename columns clearly and assign appropriate data types. Split or combine text, extract dates and group rows when needed. Review Applied Steps in order because later steps depend on the structure produced by earlier ones.

Merge joins two queries using matching columns. Choose a join kind based on which rows must remain, then expand the related table to select the columns you need. Matching columns should use compatible types; duplicate keys can multiply rows.

Append stacks rows from one query beneath another. Columns are aligned by name rather than position. Use it for similarly structured files or tables, and check for inconsistent names or types before combining them.

Unpivot converts repeated columns, such as separate month columns, into attribute and value rows. Pivot turns values from a column into new column headings. A tidy structure with one row per observation usually makes filtering and calculations simpler.