Work & productivity / QUICK REFERENCE

Power Query: clean, merge and reshape data

Compact Power Query Editor tasks and M expressions for cleaning columns, unpivoting, joins and grouping.

48 entries

Open and prepare a table

Open Power Query in Excel: Data → Get Data
Choose an external or workbook source before transforming. [1][5][4]
Turn values into headers: Home → Use First Row as Headers
Promote first data row when it contains column labels. [1][5][4]
Change column type: Transform → Data Type
Choose Text, Whole Number, Decimal Number, Date or similar type. [1][5][4]
Explicit numeric type in M: Table.TransformColumnTypes(Source,{{"Amount",type number}})
Applies number conversion to a named column. [1][5][4]
Explicit date type in M: Table.TransformColumnTypes(Source,{{"Date",type date}})
Converts to date; locale affects text parsing. [1][5][4]
Set culture during conversion: Table.TransformColumnTypes(Source,{{"Amount",type number}},"en-US")
Explicit culture helps interpret localized numeric text. [1][5][4]
Keep selected columns: Table.SelectColumns(Source,{"ID","Amount"})
Produces a narrower table with exactly the listed fields. [1][5][4]
Remove unwanted column: Table.RemoveColumns(Source,{"Notes"})
Drops a column when it is not needed downstream. [1][5][4]
Rename a column: Table.RenameColumns(Source,{{"Amount","Sales"}})
Rename one column while preserving row data. [1][5][4]
Reorder columns: Table.ReorderColumns(Source,{"ID","Amount","Date"})
Requires listed columns exist with matching names. [1][5][4]
Count input rows: Table.RowCount(Source)
Counts every row in the input table. [1][5][4]
Preview data profile: View → Column quality / Column distribution
Inspect nulls and categories in the current preview. [1][5][4]

Clean text and values

Trim a text column: Table.TransformColumns(Source,{{"Name",Text.Trim,type text}})
Removes surrounding spaces from the named text field. [4][5]
Clean nonprinting text: Table.TransformColumns(Source,{{"Name",Text.Clean,type text}})
Removes control characters while retaining ordinary letters. [4][5]
Uppercase a field: Table.TransformColumns(Source,{{"Code",Text.Upper,type text}})
Normalizes case for consistent categories. [4][5]
Lowercase a field: Table.TransformColumns(Source,{{"Email",Text.Lower,type text}})
Converts text to lowercase, not validating email syntax. [4][5]
Replace a label: Table.ReplaceValue(Source,"N/A","Unknown",Replacer.ReplaceText,{"Status"})
Replaces matching substrings in selected columns. [4][5]
Fill missing values: Table.ReplaceValue(Source,null,0,Replacer.ReplaceValue,{"Amount"})
Replaces null numeric amounts; only when zero is semantically appropriate. [4][5]
Drop exact duplicates: Table.Distinct(Source)
Removes duplicate rows by whole-row equality. [4][5]
Drop duplicate IDs: Table.Distinct(Source,{"ID"})
Keeps one row per ID; preserve intended order explicitly before reliance on which row survives. [4][5]
Remove blank IDs: Table.SelectRows(Source,each [ID]<>null)
Filters out rows with null ID fields. [4][5]
Keep positive amounts: Table.SelectRows(Source,each [Amount]>0)
Retains rows meeting the numeric predicate. [4][5]
Add derived column: Table.AddColumn(Source,"Net",each [Sales]-[Cost],type number)
Creates a new numeric column from existing fields. [4][5]
Sort amounts descending: Table.Sort(Source,{{"Amount",Order.Descending}})
Orders rows according to a numeric column. [4][5]

Split, unpivot and reshape

Split text using delimiter: Text.Split("red|green|blue","|")
Returns a list containing three color strings. [3][4]
Split data column in UI: Transform → Split Column → By Delimiter
Creates multiple columns from delimited values. [3][4]
Unpivot selected fields: Transform → Unpivot Columns
Converts several metric columns into Attribute and Value records. [3][4]
Unpivot other columns: Transform → Unpivot Other Columns
Keeps selected identifier columns and turns the rest into rows. [3][4]
Unpivot other M fields: Table.UnpivotOtherColumns(Source,{"ID"},"Metric","Value")
Converts non-ID columns into paired metric and value rows. [3][4]
Unpivot named fields: Table.Unpivot(Source,{"Jan","Feb"},"Month","Sales")
Converts only listed source columns into long format. [3][4]
Expand nested table: Table.ExpandTableColumn(Source,"Matches",{"Name"},{"CustomerName"})
Retrieves selected columns from a nested table value. [3][4]
Expand record column: Table.ExpandRecordColumn(Source,"Meta",{"City"},{"City"})
Expands a record's field as its own table column. [3][4]
Extract first text segment: Text.BeforeDelimiter("A-B","-")
Retrieves text preceding the first hyphen. [3][4]
Extract last segment: Text.AfterDelimiter("A-B","-")
Retrieves text after the first delimiter. [3][4]
Append query rows: Home → Append Queries as New
Stacks columns from two tables, filling missing columns with null. [3][4]
Append M tables: Table.Combine({Source,Other})
Aligns column names when vertically combining tables. [3][4]

Join and group data

Merge as new query: Home → Merge Queries as New
Produces a separate query rather than editing a source query. [1][2]
Left outer merge: Join kind → Left Outer
Keeps every left-side row, adding matches from the right. [1][2]
Inner merge: Join kind → Inner
Keeps only matching keys from both tables. [1][2]
Full outer merge: Join kind → Full Outer
Keeps matching plus unmatched rows from either side. [1][2]
Left anti merge: Join kind → Left Anti
Finds rows on the left without any corresponding right match. [1][2]
Right anti merge: Join kind → Right Anti
Finds rows on the right without any corresponding left match. [1][2]
Match multiple keys: Select paired columns in matching order
Each key pair must have corresponding data types. [1][2]
M left join: Table.NestedJoin(Source,{"ID"},Other,{"ID"},"Matches",JoinKind.LeftOuter)
Creates a nested matching-table column for each left row. [1][2]
Group with sum: Table.Group(Source,{"City"},{{"Total",each List.Sum([Amount]),type number}})
Groups by City and aggregates numeric Amount values. [1][2]
Group count: Table.Group(Source,{"City"},{{"N",each Table.RowCount(_),Int64.Type}})
Counts group rows into a new N column. [1][2]
Group with average: Table.Group(Source,{"City"},{{"Average",each List.Average([Amount]),type number}})
Computes the mean numeric amount per city. [1][2]
Check join cardinality: Review distinct key counts before merge
Many-to-many matches can unexpectedly multiply result rows. [1][2]

Reserved placement, separate from reference controls. No ad network is active.

Updated 2026-10-20Power Query / M · 2026

Sources

cheatsheetatlas.com/sheets/power-query/ · Updated 2026-10-20 · Power Query / M · 2026

← Explore more of the atlas Contact ↗