Reusable Power Query M code snippets.
Combines every query name in a list into one table, useful when the set of source tables can change.
Merging & Appending Advanced
Copy code
let
TableNames = {"Sales_Jan" , "Sales_Feb" , "Sales_Mar" },
Tables = List.Transform (TableNames, each Expression.Evaluate (_, #shared )),
Combined = Table.Combine (Tables)
in
Combined
TableNames must exactly match the names of existing queries in this file. Expression.Evaluate resolves a query by name at runtime; keep the list of names trusted and under your own control.
Requires: Several existing queries with the same column structure.
Defines a query as a function so it can be called with different arguments from other queries.
Advanced M Functions Advanced
Copy code
(FilePath as text) as table =>
let
Source = Csv.Document (File.Contents (FilePath), [Delimiter = "," , Encoding = 1252 ]),
PromotedHeaders = Table.PromoteHeaders (Source, [PromoteAllScalars = true ])
in
PromotedHeaders
Paste this as the entire content of a new blank query (name it, for example, fnLoadCsv), then call it elsewhere as fnLoadCsv("C:\data\sales.csv").
Requires: A blank query to hold the function definition.
Reads every .xlsx file in a folder and combines a named sheet from each into one table.
File Handling Advanced
Copy code
let
Source = Folder.Files ("C:\Data\SalesFiles" ),
FilteredExcelFiles = Table.SelectRows (Source, each [Extension] = ".xlsx" ),
AddedData = Table.AddColumn (FilteredExcelFiles, "Data" , each Excel.Workbook ([Content], true )),
ExpandedData = Table.ExpandTableColumn (AddedData, "Data" , {"Name" , "Data" }, {"SheetName" , "SheetData" }),
FilteredSheet = Table.SelectRows (ExpandedData, each [SheetName] = "Sheet1" ),
ExpandedSheetData = Table.ExpandTableColumn (FilteredSheet, "SheetData" , {"Column1" , "Column2" }, {"Column1" , "Column2" })
in
ExpandedSheetData
Replace the folder path, sheet name and column list with your own. All files must share the same sheet name and layout.
Requires: A folder containing Excel files with a consistent layout.
Calls a JSON REST API and converts the response into a table.
API & Web Data Advanced
Copy code
let
Source = Json.Document (Web.Contents ("https://api.example.com/v1/sales" , [Headers = [Authorization = "Bearer YOUR_TOKEN" ]])),
ConvertedToTable = Table.FromRecords (Source[data])
in
ConvertedToTable
Replace the URL and Authorization header with your API's actual details, and Source[data] with the field name that holds the array of records in the response.
Requires: An API endpoint that returns JSON, and any required auth token.
Converts a text date stored as a fixed-width string, like "20240115", into a real Date value.
Data Types Advanced
Copy code
let
Source = YourTable,
AddedDate = Table.AddColumn (
Source,
"ParsedDate" ,
each #date (
Number.From (Text.Start ([DateText], 4 )),
Number.From (Text.Middle ([DateText], 4 , 2 )),
Number.From (Text.End ([DateText], 2 ))
),
type date
)
in
AddedDate
Adjust the Text.Start/Middle/End offsets to match your source format's year/month/day positions.
Requires: A text column holding dates in a fixed, non-standard layout that Date.FromText can't parse directly.
Moves a conditional expression into its own function so it can be reused and tested independently of a table step.
Conditional Logic Advanced
Copy code
let
ClassifyAmount = (amount as number) as text =>
if amount >= 10000 then "High"
else if amount >= 1000 then "Medium"
else "Low" ,
Source = YourTable,
AddedCategory = Table.AddColumn (Source, "SalesCategory" , each ClassifyAmount([Amount]), type text)
in
AddedCategory
Once the logic is a named function, it's easier to unit test in isolation or reuse across multiple queries.
Requires: A table with a numeric column to classify.
Defines a function that calls itself using the @ self-reference operator, useful for recursive calculations like factorials.
Advanced M Functions Advanced
Copy code
let
Factorial = (n as number) as number =>
if n <= 1 then 1 else n * @Factorial(n - 1 )
in
Factorial(5 )
The @ before the function name marks a recursive self-reference - without it, M won't let a let-bound value refer back to itself.
Requires: None - this is a standalone example; paste the inner logic into your own function.
Builds a list by repeatedly applying a step, similar to a while loop in imperative languages.
Advanced M Functions Advanced
Copy code
let
Doubles = List.Generate (
() => 1 ,
each _ < 100 ,
each _ * 2
)
in
Doubles
The three functions are, in order: the initial value, the condition to keep going, and how to get the next value from the current one.
Requires: None - this is a standalone example; adapt the initial/condition/next functions to your own loop.
Folds a list down into a single value by applying a function across every item, similar to reduce() in other languages.
Advanced M Functions Advanced
Copy code
let
Numbers = {1 , 2 , 3 , 4 , 5 },
Total = List.Accumulate (Numbers, 0 , (state, current) => state + current)
in
Total
The second argument is the starting value (the seed); the function receives the running total (state) and each item (current) in turn.
Requires: A list of values to reduce to one result.
Looks up and calls a query or function using its name as text, rather than referencing it directly.
Advanced M Functions Advanced
Copy code
let
FunctionName = "fnLoadCsv" ,
Arguments = {"C:\Data\sales.csv" },
ResolvedFunction = Expression.Evaluate (FunctionName, #shared ),
Result = Function.Invoke (ResolvedFunction, Arguments)
in
Result
FunctionName must exactly match an existing query name in this file. Only use this with trusted, hardcoded names - never build FunctionName from external input.
Requires: An existing custom function query to call by name.
Invokes a function using an argument list built at runtime, instead of writing out each parameter by hand.
Advanced M Functions Advanced
Copy code
let
Add = (a as number, b as number) as number => a + b,
Arguments = {5 , 10 },
Result = Function.Invoke (Add, Arguments)
in
Result
Function.Invoke is handy when the number or order of arguments is only known at runtime, such as when building them from a table row.
Requires: A function and a list whose items match its parameters, in order.
Creates a function that, given one argument, returns a new specialized function - a pattern known as currying.
Advanced M Functions Advanced
Copy code
let
MakeMultiplier = (factor as number) as function =>
(value as number) as number => value * factor,
Double = MakeMultiplier(2 ),
Result = Double(21 )
in
Result
MakeMultiplier(2) returns a ready-made "multiply by 2" function that you can reuse or pass around like any other value.
Requires: None - this is a standalone example; adapt the inner logic to your own use case.
Repeatedly calls a paged REST API until it stops returning results, combining every page into one table.
Advanced M Functions Advanced
Copy code
let
GetPage = (pageNumber as number) =>
Json.Document (Web.Contents ("https://api.example.com/v1/orders" , [Query = [page = Text.From (pageNumber)]])),
Pages = List.Generate (
() => [Page = GetPage(1 ), PageNumber = 1 ],
each List.Count (Record.Field (_[Page], "data" )) > 0 ,
each [Page = GetPage([PageNumber] + 1 ), PageNumber = [PageNumber] + 1 ]
),
AllRecords = List.Combine (List.Transform (Pages, each Record.Field (_[Page], "data" ))),
ConvertedToTable = Table.FromRecords (AllRecords)
in
ConvertedToTable
Replace the URL, query parameter name and the "data" field with your API's actual pagination scheme and response shape.
Requires: A REST API that returns results in pages and stops (or returns an empty array) after the last page.
Adds a sequential index that restarts at 1 for every group, instead of numbering the whole table.
Table Transformations Advanced
Copy code
let
Source = YourTable,
Grouped = Table.Group (Source, {"GroupColumn" }, {{"AllRows" , each Table.AddIndexColumn (_, "IndexInGroup" , 1 , 1 , Int64.Type ), type table}}),
Expanded = Table.ExpandTableColumn (Grouped, "AllRows" , Table.ColumnNames (Source) & {"IndexInGroup" })
in
Expanded
IndexInGroup restarts at 1 for each distinct value of GroupColumn.
Requires: A table with a column that defines the groups to index within.
Ranks rows within each group by a numeric column, highest value first.
Table Transformations Advanced
Copy code
let
Source = YourTable,
Grouped = Table.Group (Source, {"GroupColumn" }, {{"AllRows" , each Table.AddIndexColumn (Table.Sort (_, {{"Value" , Order.Descending }}), "Rank" , 1 , 1 , Int64.Type ), type table}}),
Expanded = Table.ExpandTableColumn (Grouped, "AllRows" , Table.ColumnNames (Source) & {"Rank" })
in
Expanded
Change Order.Descending to Order.Ascending to rank lowest-first.
Requires: A table with a column to group by and a numeric column to rank on.
Renames columns by joining their current names against a separate mapping table of old and new names.
Column Operations Advanced
Copy code
let
Source = YourTable,
Mapping = MappingTable,
CurrentColumns = Table.ColumnNames (Source),
RenamePairs = Table.ToRows (Table.SelectRows (Mapping, each List.Contains (CurrentColumns, [OldName]))),
RenamedColumns = Table.RenameColumns (Source, RenamePairs)
in
RenamedColumns
Keep MappingTable as a separate query with OldName/NewName columns; edit it instead of the M code when names change.
Requires: A separate mapping table query with OldName and NewName columns.
Tries several known date formats in order and returns the first one that parses successfully.
Date & Time Advanced
Copy code
let
Source = YourTable,
AddedDate = Table.AddColumn (Source, "ParsedDate" , each
try Date.FromText ([DateText], "en-US" ) otherwise
try Date.FromText ([DateText], "en-GB" ) otherwise
try #date (Number.From (Text.Start ([DateText], 4 )), Number.From (Text.Middle ([DateText], 4 , 2 )), Number.From (Text.End ([DateText], 2 ))) otherwise
null ,
type date)
in
AddedDate
Add or reorder the try/otherwise branches to match the date formats actually present in your source.
Requires: A text column containing dates in more than one format.
Calculates the number of weekdays (Monday-Friday) between two dates, excluding weekends.
Date & Time Advanced
Copy code
let
Source = YourTable,
AddedBusinessDays = Table.AddColumn (Source, "BusinessDays" , each
let
AllDates = List.Dates ([StartDate], Duration.Days ([EndDate] - [StartDate]) + 1 , #duration (1 , 0 , 0 , 0 )),
Weekdays = List.Select (AllDates, each Date.DayOfWeek (_, Day.Monday ) < 5 )
in
List.Count (Weekdays),
Int64.Type )
in
AddedBusinessDays
This doesn't exclude holidays; combine with a holiday table and List.Contains to subtract those too.
Requires: A table with two date columns.
Flags rows whose numeric value falls outside a set number of standard deviations from the column's average.
Conditional Logic Advanced
Copy code
let
Source = YourTable,
Average = List.Average (Source[Value]),
StdDev = List.StandardDeviation (Source[Value]),
AddedIsOutlier = Table.AddColumn (Source, "IsOutlier" , each Number.Abs ([Value] - Average) > (2 * StdDev), type logical)
in
AddedIsOutlier
2 standard deviations is a common default; tighten or loosen the threshold depending on how sensitive the flag should be.
Requires: A table with a numeric column large enough for average/standard deviation to be meaningful.
Connects to a SharePoint document library and combines every file in a folder, the same way Folder.Files works locally.
File Handling Advanced
Copy code
let
Source = SharePoint.Files ("https://yourcompany.sharepoint.com/sites/YourSite" , [ApiVersion = 15 ]),
FilteredFolder = Table.SelectRows (Source, each Text.Contains ([Folder Path], "/Shared Documents/YourFolder" )),
AddedData = Table.AddColumn (FilteredFolder, "Data" , each Excel.Workbook ([Content], true ))
in
AddedData
Replace the site URL and folder path filter with your own; sign in with an account that has access to the site when prompted.
Requires: Access to a SharePoint site and document library.
Detects every record-type column in a table and expands all of them in one pass, without naming them individually.
API & Web Data Advanced
Copy code
let
Source = YourTable,
ExpandAll = List.Accumulate (
List.Select (Table.ColumnNames (Source), each Type.Is (Value.Type (Table.Column (Source, _)), type record)),
Source,
(state, columnName) => Table.ExpandRecordColumn (state, columnName, Record.FieldNames (Table.Column (state, columnName){0 }))
)
in
ExpandAll
Runs once per nested column, so run it again if expanding reveals a second layer of nested records.
Requires: A table with one or more record-type columns, such as parsed JSON with unpredictable nesting.
Passes Power Query parameter values into a native SQL query's WHERE clause for a dynamic date range.
Database & Refresh Advanced
Copy code
let
StartDateText = Date.ToText (StartDateParameter, "yyyy-MM-dd" ),
EndDateText = Date.ToText (EndDateParameter, "yyyy-MM-dd" ),
Source = Sql.Database ("YourServer" , "YourDatabase" , [Query =
"SELECT * FROM Sales.Orders WHERE OrderDate BETWEEN '" & StartDateText & "' AND '" & EndDateText & "'"
])
in
Source
StartDateParameter and EndDateParameter should be Power Query date parameters; building SQL text this way is fine for trusted internal parameters, not for arbitrary user input.
Requires: Two date parameters set up in Power Query, and a SQL database connection.
Filters a table using the RangeStart and RangeEnd parameters required for Power BI incremental refresh.
Database & Refresh Advanced
Copy code
let
Source = YourTable,
FilteredRows = Table.SelectRows (Source, each [DateColumn] >= RangeStart and [DateColumn] < RangeEnd)
in
FilteredRows
RangeStart and RangeEnd must be defined as datetime parameters named exactly that, then configured under the table's Incremental Refresh settings.
Requires: RangeStart and RangeEnd parameters defined in the Power BI Desktop file.
Joins two snapshots of the same table by key and flags rows where a tracked column's value has changed.
Comparing & Lookups Advanced
Copy code
let
Merged = Table.NestedJoin (CurrentTable, {"ID" }, PreviousTable, {"ID" }, "Previous" , JoinKind.LeftOuter ),
Expanded = Table.ExpandTableColumn (Merged, "Previous" , {"Value" }, {"PreviousValue" }),
AddedChangedFlag = Table.AddColumn (Expanded, "HasChanged" , each [Value] <> [PreviousValue], type logical)
in
AddedChangedFlag
Replace Value with the actual column you want to track for changes between snapshots.
Requires: Two tables sharing the same key column: a current snapshot and a previous one.
Matches a value to the correct tier in a lookup table based on a minimum threshold, like Excel's approximate VLOOKUP.
Comparing & Lookups Advanced
Copy code
let
Source = YourTable,
AddedTier = Table.AddColumn (Source, "Tier" , (row) =>
Table.Last (Table.SelectRows (TierTable, each [MinValue] <= row[Value]))[TierName]
)
in
AddedTier
TierTable must be sorted ascending by MinValue; Table.Last picks the highest tier threshold that's still at or below the row's value.
Requires: A tier lookup table with MinValue and TierName columns, sorted ascending by MinValue.
Merges two tables on a text column using fuzzy matching, so close-but-not-exact spellings still match.
Comparing & Lookups Advanced
Copy code
let
Source = Table.FuzzyNestedJoin (
YourTable, {"CompanyName" },
LookupTable, {"CompanyName" },
"Match" , JoinKind.LeftOuter ,
[IgnoreCase = true , IgnoreSpace = true , Threshold = 0.8 ]
),
Expanded = Table.ExpandTableColumn (Source, "Match" , {"CompanyID" }, {"CompanyID" })
in
Expanded
Threshold ranges from 0 (match anything) to 1 (exact match only); lower it gradually if valid matches are being missed.
Requires: Two tables with a text column to fuzzy-match on.
A reusable custom function that returns the number of weekdays between two dates.
Advanced M Functions Advanced
Copy code
(startDate as date, endDate as date) as number =>
let
AllDates = List.Dates (startDate, Duration.Days (endDate - startDate) + 1 , #duration (1 , 0 , 0 , 0 )),
Weekdays = List.Select (AllDates, each Date.DayOfWeek (_, Day.Monday ) < 5 )
in
List.Count (Weekdays)
Save this as its own query named fnBusinessDays, then call it with fnBusinessDays([StartDate], [EndDate]) inside Table.AddColumn.
Requires: None - this is a standalone reusable function; call it from any query in the same file.
Sets column types from a lookup list, skipping any column name that isn't actually present.
Data Types Intermediate
Copy code
let
Source = YourTable,
ColumnTypes = {{"OrderID" , Int64.Type }, {"OrderDate" , type date}, {"Amount" , type number}},
ExistingTypes = List.Select (ColumnTypes, each List.Contains (Table.ColumnNames (Source), _{0 })),
ChangedType = Table.TransformColumnTypes (Source, ExistingTypes)
in
ChangedType
Update the ColumnTypes list with your own column names and target types.
Requires: A table whose columns should always end up with a known type, even if some are missing.
Groups by an ID column and keeps only the most recent row per group, based on a date column.
Row Operations Intermediate
Copy code
let
Source = YourTable,
Sorted = Table.Sort (Source, {{"Date" , Order.Descending }}),
Grouped = Table.Group (Sorted, {"ID" }, {{"Latest" , each Table.FirstN (_, 1 )}}),
Combined = Table.Combine (Grouped[Latest])
in
Combined
Replace ID and Date with your key column and date column names.
Requires: A table with an ID column and a date column to sort by.
Builds a continuous calendar table between two dates with year, month and quarter columns.
Date & Time Intermediate
Copy code
let
StartDate = #date (2020 , 1 , 1 ),
EndDate = #date (2029 , 12 , 31 ),
DayCount = Duration.Days (EndDate - StartDate) + 1 ,
Dates = List.Dates (StartDate, DayCount, #duration (1 , 0 , 0 , 0 )),
ToTable = Table.FromList (Dates, Splitter.SplitByNothing (), {"Date" }),
ChangedType = Table.TransformColumnTypes (ToTable, {{"Date" , type date}}),
AddedYear = Table.AddColumn (ChangedType, "Year" , each Date.Year ([Date]), Int64.Type ),
AddedMonth = Table.AddColumn (AddedYear, "Month" , each Date.Month ([Date]), Int64.Type ),
AddedMonthName = Table.AddColumn (AddedMonth, "MonthName" , each Date.MonthName ([Date]), type text),
AddedQuarter = Table.AddColumn (AddedMonthName, "Quarter" , each Date.QuarterOfYear ([Date]), Int64.Type )
in
AddedQuarter
Update StartDate and EndDate to cover your data's full date range, then mark this query as a date table in Power BI.
Requires: None - this is a standalone query, not a transformation of an existing table.
Keeps only the columns from a list that actually exist, instead of erroring if one is missing.
Column Operations Intermediate
Copy code
let
Source = YourTable,
ColumnsToKeep = {"OrderID" , "OrderDate" , "Amount" },
ExistingColumns = List.Intersect ({Table.ColumnNames (Source), ColumnsToKeep}),
SelectedColumns = Table.SelectColumns (Source, ExistingColumns)
in
SelectedColumns
Update ColumnsToKeep with the columns you want, in any order.
Requires: A table whose column set may vary between refreshes.
Renames columns from a lookup list, skipping any pair whose old name isn't present.
Column Operations Intermediate
Copy code
let
Source = YourTable,
RenameList = {{"col1" , "OrderID" }, {"col2" , "OrderDate" }},
ExistingRenames = List.Select (RenameList, each List.Contains (Table.ColumnNames (Source), _{0 })),
RenamedColumns = Table.RenameColumns (Source, ExistingRenames)
in
RenamedColumns
Each pair is {"OldName", "NewName"}.
Requires: A table whose original column names may vary between refreshes.
Turns unique values from one column into separate columns, aggregating another column.
Table Transformations Intermediate
Copy code
let
Source = YourTable,
PivotedColumn = Table.Pivot (Source, List.Distinct (Source[Month]), "Month" , "Sales" , List.Sum )
in
PivotedColumn
Replace List.Sum with another aggregation (List.Average, List.Max, and so on) if needed.
Requires: A long-format table with a column to pivot and a column to aggregate.
Looks up matching columns from a second table, similar to a SQL left join.
Merging & Appending Intermediate
Copy code
let
Source = Orders,
MergedQueries = Table.NestedJoin (Source, {"CustomerID" }, Customers, {"CustomerID" }, "CustomerDetails" , JoinKind.LeftOuter ),
ExpandedCustomerName = Table.ExpandTableColumn (MergedQueries, "CustomerDetails" , {"CustomerName" }, {"CustomerName" })
in
ExpandedCustomerName
Replace Orders and Customers with the names of your own queries, and CustomerID with your join key.
Requires: Two queries that share a common key column.
Returns a default value instead of an error when an expected record field doesn't exist.
List & Record Operations Intermediate
Copy code
let
Source = YourRecord,
SafeValue = Record.FieldOrDefault (Source, "FieldName" , null )
in
SafeValue
Useful when working with JSON responses where a field may sometimes be missing.
Requires: A record value, such as one row of a table accessed as a record.
Wraps a calculation so an error or missing value falls back to a default instead of failing the query.
Error Handling Intermediate
Copy code
let
Source = YourTable,
AddedSafeValue = Table.AddColumn (Source, "SafeAmount" , each try [Amount] otherwise 0 , type number)
in
AddedSafeValue
Replace [Amount] with the expression that might fail, and 0 with your preferred fallback value.
Requires: A table with a column or calculation that occasionally errors.
Loads a table into memory once with Table.Buffer so it isn't re-evaluated every time it's referenced.
Performance Optimization Intermediate
Copy code
let
Source = YourTable,
BufferedTable = Table.Buffer (Source)
in
BufferedTable
Only buffer tables small enough to comfortably fit in memory - buffering a large table can slow the query down instead of speeding it up.
Requires: A table that's referenced multiple times downstream, such as inside a loop or repeated lookup.
Converts a column to Int64, replacing any value that fails to convert with null instead of erroring the whole query.
Data Types Intermediate
Copy code
let
Source = YourTable,
ChangedType = Table.TransformColumns (
Source,
{{"ColumnName" , each try Int64.From (_) otherwise null , Int64.Type }}
)
in
ChangedType
Use this instead of Table.TransformColumnTypes when the column may contain values that won't convert cleanly, like blanks or stray text.
Requires: A column with mostly-numeric values that may include a few bad entries.
Inspects the actual type of a value or column, useful for debugging unexpected type errors.
Data Types Intermediate
Copy code
let
Source = YourTable,
AddedTypeCheck = Table.AddColumn (Source, "ColumnType" , each Value.Type ([ColumnName]), type type )
in
AddedTypeCheck
Remove this helper column once you've confirmed the type - it's a debugging step, not something to ship in a production query.
Requires: A table with a column whose type you're unsure of.
Converts a Duration value, like the difference between two datetimes, into a decimal number of hours.
Data Types Intermediate
Copy code
let
Source = YourTable,
AddedDuration = Table.AddColumn (Source, "DurationHours" , each Duration.TotalHours ([EndTime] - [StartTime]), type number)
in
AddedDuration
Use Duration.TotalMinutes or Duration.TotalDays instead if you need a different unit.
Requires: A table with two datetime columns to subtract.
Builds a conditional column that checks multiple columns together using and/or with parentheses.
Conditional Logic Intermediate
Copy code
let
Source = YourTable,
AddedFlag = Table.AddColumn (
Source,
"PriorityOrder" ,
each ([Region] = "West" or [Region] = "East" ) and [Amount] > 5000 ,
type logical
)
in
AddedFlag
Parentheses control evaluation order just like in Excel - group the or condition before combining it with and.
Requires: A table with the columns used in the conditions.
Replaces a long if/else if chain with a lookup record, similar to a switch/case statement in other languages.
Conditional Logic Intermediate
Copy code
let
Source = YourTable,
StatusMap = [Y = "Active" , N = "Inactive" , P = "Pending" ],
AddedStatus = Table.AddColumn (
Source,
"StatusLabel" ,
each Record.FieldOrDefault (StatusMap, [StatusCode], "Unknown" ),
type text
)
in
AddedStatus
Add or remove fields in StatusMap to cover every code you expect; anything not listed falls back to "Unknown" instead of erroring.
Requires: A table with a short code column to map to readable labels.
Replaces a column's value only where a condition is true, leaving every other row untouched.
Conditional Logic Intermediate
Copy code
let
Source = YourTable,
AddedCorrected = Table.TransformColumns (
Source,
{{"Amount" , each if _ < 0 then 0 else _, type number}}
)
in
AddedCorrected
The underscore (_) refers to the current cell's value inside Table.TransformColumns.
Requires: A table with a column that needs a targeted correction.
Explicitly tests for null before comparing, since comparing a value to null directly can behave unexpectedly in M.
Conditional Logic Intermediate
Copy code
let
Source = YourTable,
AddedStatus = Table.AddColumn (
Source,
"HasDiscount" ,
each if [DiscountPercent] = null then false else [DiscountPercent] > 0 ,
type logical
)
in
AddedStatus
Checking = null explicitly, rather than assuming a missing value acts as 0 or false, avoids surprises when the column mixes real values and nulls.
Requires: A table with a column that may contain null values.
Creates a custom function where some parameters are optional and fall back to a default when omitted.
Advanced M Functions Intermediate
Copy code
let
GetDiscountedPrice = (price as number, optional discountPercent as number) as number =>
price * (1 - (if discountPercent = null then 0.1 else discountPercent)),
Result = GetDiscountedPrice(100 )
in
Result
Optional parameters that are omitted come through as null, so check for null rather than assuming a default value.
Requires: None - this is a standalone function example; paste it into your own query.
Returns a table describing every column's name, data type and other metadata - useful for debugging type mismatches.
Advanced M Functions Intermediate
Copy code
let
Source = YourTable,
Schema = Table.Schema (Source)
in
Schema
Look at the TypeName column in the result to see each column's actual type, including nullability.
Requires: A table whose column types you want to inspect.
Builds a calendar table where the fiscal year and fiscal month are calculated from a custom fiscal year start month instead of January.
Date & Time Intermediate
Copy code
let
StartDate = #date (2020 , 1 , 1 ),
EndDate = #date (2029 , 12 , 31 ),
FiscalYearStartMonth = 4 ,
DayCount = Duration.Days (EndDate - StartDate) + 1 ,
Dates = List.Dates (StartDate, DayCount, #duration (1 , 0 , 0 , 0 )),
ToTable = Table.FromList (Dates, Splitter.SplitByNothing (), {"Date" }),
ChangedType = Table.TransformColumnTypes (ToTable, {{"Date" , type date}}),
AddedFiscalMonth = Table.AddColumn (ChangedType, "FiscalMonth" , each Number.Mod (Date.Month ([Date]) - FiscalYearStartMonth, 12 ) + 1 , Int64.Type ),
AddedFiscalYear = Table.AddColumn (AddedFiscalMonth, "FiscalYear" , each if Date.Month ([Date]) >= FiscalYearStartMonth then Date.Year ([Date]) + 1 else Date.Year ([Date]), Int64.Type )
in
AddedFiscalYear
Set FiscalYearStartMonth to the month your fiscal year begins (4 = April). FiscalYear reports the year the fiscal period ends in.
Requires: None - this is a standalone query; adjust the date range to match your data.
Flags dates in a date table that match a manually maintained list of holiday dates.
Date & Time Intermediate
Copy code
let
Source = YourDateTable,
Holidays = {#date (2026 , 1 , 1 ), #date (2026 , 7 , 4 ), #date (2026 , 12 , 25 )},
AddedIsHoliday = Table.AddColumn (Source, "IsHoliday" , each List.Contains (Holidays, [Date]), type logical)
in
AddedIsHoliday
Update the Holidays list with your own dates, or point it at a separate holiday table using List.Contains(HolidayTable[Date], [Date]).
Requires: A date table with a Date column.
Generates a standalone table of every minute in a day, useful for time-of-day analysis and slicers.
Date & Time Intermediate
Copy code
let
Times = List.Times (#time (0 , 0 , 0 ), 1440 , #duration (0 , 0 , 1 , 0 )),
ToTable = Table.FromList (Times, Splitter.SplitByNothing (), {"Time" }),
ChangedType = Table.TransformColumnTypes (ToTable, {{"Time" , type time}}),
AddedHour = Table.AddColumn (ChangedType, "Hour" , each Time.Hour ([Time]), Int64.Type ),
AddedMinute = Table.AddColumn (AddedHour, "Minute" , each Time.Minute ([Time]), Int64.Type ),
AddedHourBucket = Table.AddColumn (AddedMinute, "HourBucket" , each Text.PadStart (Text.From ([Hour]), 2 , "0" ) & ":00" , type text)
in
AddedHourBucket
This produces one row per minute (1,440 rows); group HourBucket for hourly analysis.
Requires: None - this is a standalone query.
Adds a text column that labels each date relative to today, such as Today, Yesterday or Last 7 Days.
Date & Time Intermediate
Copy code
let
Source = YourTable,
AddedLabel = Table.AddColumn (Source, "RelativeLabel" , each
let
DaysAgo = Duration.Days (DateTime.Date (DateTime.LocalNow ()) - [DateColumn])
in
if DaysAgo = 0 then "Today"
else if DaysAgo = 1 then "Yesterday"
else if DaysAgo <= 7 then "Last 7 Days"
else if DaysAgo <= 30 then "Last 30 Days"
else "Older" ,
type text)
in
AddedLabel
Labels are recalculated every refresh relative to the refresh date, not a fixed date.
Requires: A table with a date column.
Applies trim and clean to every text column in a table at once, without naming columns individually.
Data Cleaning Intermediate
Copy code
let
Source = YourTable,
TextColumns = List.Select (Table.ColumnNames (Source), each Type.Is (Value.Type (Table.Column (Source, _)), type text)),
Cleaned = Table.TransformColumns (Source, List.Transform (TextColumns, each {_, each Text.Clean (Text.Trim (_)), type text}))
in
Cleaned
This detects text columns automatically, so it keeps working if columns are added or removed.
Requires: A table with one or more text columns.
Capitalizes the first letter of each word across every text column in a table at once.
Data Cleaning Intermediate
Copy code
let
Source = YourTable,
TextColumns = List.Select (Table.ColumnNames (Source), each Type.Is (Value.Type (Table.Column (Source, _)), type text)),
ProperCased = Table.TransformColumns (Source, List.Transform (TextColumns, each {_, Text.Proper , type text}))
in
ProperCased
Skip this on columns like codes or IDs where mixed case is intentional.
Requires: A table with one or more text columns.
Strips punctuation and symbols from a text column, keeping only letters, numbers and spaces.
Text Transformations Intermediate
Copy code
let
Source = YourTable,
Cleaned = Table.TransformColumns (Source, {{"ColumnName" , each Text.Select (_, {"a" .."z" , "A" .."Z" , "0" .."9" , " " }), type text}})
in
Cleaned
Text.Select keeps only the listed character ranges; add characters like "-" or "_" to the list if you want to keep them.
Requires: A table with a text column containing unwanted punctuation or symbols.
Replaces accented characters (é, ü, ñ, etc.) with their plain ASCII equivalents.
Text Transformations Intermediate
Copy code
let
Source = YourTable,
From = "áàâäãåéèêëíìîïóòôöõúùûüçñÁÀÂÄÃÅÉÈÊËÍÌÎÏÓÒÔÖÕÚÙÛÜÇÑ" ,
To = "aaaaaaeeeeiiiiooooouuuucnAAAAAAEEEEIIIIOOOOOUUUUCN" ,
Cleaned = Table.TransformColumns (Source, {{"ColumnName" , each Text.Combine (List.Transform (Text.ToList (_), each let p = Text.PositionOf (From, _) in if p = -1 then _ else Text.At (To, p)), "" ), type text}})
in
Cleaned
Extend the From/To pairs if your data uses accented characters not listed here.
Requires: A table with a text column containing accented characters.
Strips formatting from phone numbers and reformats the digits into a consistent (XXX) XXX-XXXX pattern.
Text Transformations Intermediate
Copy code
let
Source = YourTable,
Formatted = Table.TransformColumns (Source, {{"Phone" , each
let
Digits = Text.Select (_, {"0" .."9" }),
LastTen = Text.End (Digits, 10 )
in
"(" & Text.Start (LastTen, 3 ) & ") " & Text.Middle (LastTen, 3 , 3 ) & "-" & Text.End (LastTen, 4 ), type text}})
in
Formatted
Assumes 10-digit North American numbers; adjust the format string for other countries.
Requires: A table with a phone number column containing at least 10 digits.
Replaces many old values with new ones in one pass by joining against a mapping table instead of chaining Table.ReplaceValue.
Data Cleaning Intermediate
Copy code
let
Source = YourTable,
Mapping = MappingTable,
Merged = Table.NestedJoin (Source, {"ColumnName" }, Mapping, {"OldValue" }, "MapMatch" , JoinKind.LeftOuter ),
Expanded = Table.ExpandTableColumn (Merged, "MapMatch" , {"NewValue" }),
FinalColumn = Table.AddColumn (Expanded, "ColumnNameClean" , each if [NewValue] <> null then [NewValue] else [ColumnName], type text)
in
FinalColumn
MappingTable needs one row per old/new pair with OldValue and NewValue columns; rows with no match keep their original value via ColumnNameClean.
Requires: A separate mapping table with OldValue and NewValue columns.
Fills null values with 0 across every numeric column in a table at once.
Data Cleaning Intermediate
Copy code
let
Source = YourTable,
NumberColumns = List.Select (Table.ColumnNames (Source), each Type.Is (Value.Type (Table.Column (Source, _)), type number)),
Replaced = Table.ReplaceValue (Source, null , 0 , Replacer.ReplaceValue , NumberColumns)
in
Replaced
Detects numeric columns automatically, so it adapts if columns are added or removed.
Requires: A table with one or more numeric columns.
Keeps only the rows that have at least one other duplicate, for reviewing data quality before removing duplicates.
Row Operations Intermediate
Copy code
let
Source = YourTable,
Grouped = Table.Group (Source, {"KeyColumn" }, {{"Count" , each Table.RowCount (_), Int64.Type }, {"Rows" , each _, type table}}),
OnlyDuplicates = Table.SelectRows (Grouped, each [Count] > 1 ),
Expanded = Table.ExpandTableColumn (OnlyDuplicates, "Rows" , Table.ColumnNames (Source))
in
Expanded
Change KeyColumn to the column, or add more columns to the group-by list, that defines a duplicate in your data.
Requires: A table where duplicates should be defined by one or more key columns.
Replaces error values with null across every column in a table, without listing columns individually.
Error Handling Intermediate
Copy code
let
Source = YourTable,
ColumnNames = Table.ColumnNames (Source),
ReplacedErrors = Table.ReplaceErrorValues (Source, List.Transform (ColumnNames, each {_, null }))
in
ReplacedErrors
This adapts automatically if columns are added or removed, unlike listing each column by name.
Requires: A table with any number of columns that may contain row-level errors.
Builds a summary table showing how many null or blank values each column contains.
Data Cleaning Intermediate
Copy code
let
Source = YourTable,
ColumnNames = Table.ColumnNames (Source),
Profile = Table.FromRecords (List.Transform (ColumnNames, each [
ColumnName = _,
BlankCount = Table.RowCount (Table.SelectRows (Source, (row) => Record.Field (row, _) = null or Record.Field (row, _) = "" ))
]))
in
Profile
Load this as its own query to get a one-glance data quality summary alongside your main table.
Requires: Any table you want to profile for missing values.
Unpivots only the columns that look like month names, leaving key columns like ID or Region untouched.
Table Transformations Intermediate
Copy code
let
Source = YourTable,
MonthNames = {"Jan" , "Feb" , "Mar" , "Apr" , "May" , "Jun" , "Jul" , "Aug" , "Sep" , "Oct" , "Nov" , "Dec" },
MonthColumns = List.Select (Table.ColumnNames (Source), each List.Contains (MonthNames, _)),
Unpivoted = Table.UnpivotOtherColumns (Source, List.RemoveItems (Table.ColumnNames (Source), MonthColumns), "Month" , "Value" )
in
Unpivoted
Adjust MonthNames to match how the month columns are labeled in your source (e.g. "January" or "2026-01").
Requires: A table with one column per month and one or more identifier columns.
Pivots a column into new columns while keeping the raw values, for cases where each group only has one row per pivoted category.
Table Transformations Intermediate
Copy code
let
Source = YourTable,
Pivoted = Table.Pivot (Source, List.Distinct (Source[Category]), "Category" , "Value" , List.Max )
in
Pivoted
List.Max is only used to pick a value when a cell would otherwise be blank; with one row per group/category it simply returns that row's value.
Requires: A table with exactly one row per group/category combination.
Groups rows and concatenates a column's values from each group into one comma-separated text value.
Table Transformations Intermediate
Copy code
let
Source = YourTable,
Grouped = Table.Group (Source, {"GroupColumn" }, {{"CombinedValues" , each Text.Combine ([ValueColumn], ", " ), type text}})
in
Grouped
ValueColumn must already be text; wrap it in Text.From(_) inside Text.Combine if it's numeric.
Requires: A table with a column to group by and a text column to combine.
Splits a delimited text value in one column into multiple rows, duplicating the rest of the row's values.
Table Transformations Intermediate
Copy code
let
Source = YourTable,
SplitColumn = Table.TransformColumns (Source, {{"ColumnName" , each Text.Split (_, "," ), type list}}),
ExpandedRows = Table.ExpandListColumn (SplitColumn, "ColumnName" )
in
ExpandedRows
Change "," to whatever delimiter separates the values packed into ColumnName.
Requires: A table with a column containing delimiter-separated values that should become one row each.
Groups rows and computes several aggregations (sum, average, count) on different columns in a single step.
Table Transformations Intermediate
Copy code
let
Source = YourTable,
Grouped = Table.Group (Source, {"GroupColumn" }, {
{"TotalSales" , each List.Sum ([Sales]), type number},
{"AverageSales" , each List.Average ([Sales]), type number},
{"OrderCount" , each Table.RowCount (_), Int64.Type }
})
in
Grouped
Add or remove aggregation records in the list to change which measures are calculated per group.
Requires: A table with a column to group by and one or more numeric columns to aggregate.
Adds a running total column that accumulates a numeric column's values in row order.
Table Transformations Intermediate
Copy code
let
Source = YourTable,
Indexed = Table.AddIndexColumn (Source, "Index" , 0 , 1 , Int64.Type ),
AddedRunningTotal = Table.AddColumn (Indexed, "RunningTotal" , each List.Sum (List.FirstN (Indexed[Amount], [Index] + 1 )), type number)
in
AddedRunningTotal
This re-sums everything up to each row (O(n^2) on large tables); for big datasets, calculate the running total as a DAX measure instead.
Requires: A table already sorted in the order the running total should accumulate in.
Adds columns showing the previous row's and next row's value for a given column, in row order.
Table Transformations Intermediate
Copy code
let
Source = YourTable,
Indexed = Table.AddIndexColumn (Source, "Index" , 0 , 1 , Int64.Type ),
ValuesList = Indexed[Value],
AddedPrevious = Table.AddColumn (Indexed, "PreviousValue" , each if [Index] = 0 then null else ValuesList{[Index] - 1 }),
AddedNext = Table.AddColumn (AddedPrevious, "NextValue" , each if [Index] = List.Count (ValuesList) - 1 then null else ValuesList{[Index] + 1 })
in
AddedNext
The table must already be sorted in the order you want lag/lead calculated in - Power Query has no inherent row order.
Requires: A table already sorted in a meaningful order, with a column to shift.
Removes every column whose name contains a given text pattern, instead of listing columns by exact name.
Column Operations Intermediate
Copy code
let
Source = YourTable,
ColumnsToRemove = List.Select (Table.ColumnNames (Source), each Text.Contains (_, "_temp" )),
Removed = Table.RemoveColumns (Source, ColumnsToRemove)
in
Removed
Change "_temp" to whatever substring identifies the columns you want dropped.
Requires: A table whose disposable columns share a common naming pattern.
Keeps only the columns whose name contains a given text pattern, removing everything else.
Column Operations Intermediate
Copy code
let
Source = YourTable,
ColumnsToKeep = List.Select (Table.ColumnNames (Source), each Text.Contains (_, "Sales" )),
Selected = Table.SelectColumns (Source, ColumnsToKeep)
in
Selected
Change "Sales" to whatever substring identifies the columns you want to keep.
Requires: A table whose wanted columns share a common naming pattern.
Calculates a person's current age in whole years from a date of birth column.
Date & Time Intermediate
Copy code
let
Source = YourTable,
AddedAge = Table.AddColumn (Source, "Age" , each
let
Today = DateTime.Date (DateTime.LocalNow ()),
RawAge = Date.Year (Today) - Date.Year ([DateOfBirth]),
HasHadBirthday = (Date.Month (Today) > Date.Month ([DateOfBirth])) or (Date.Month (Today) = Date.Month ([DateOfBirth]) and Date.Day (Today) >= Date.Day ([DateOfBirth]))
in
if HasHadBirthday then RawAge else RawAge - 1 ,
Int64.Type )
in
AddedAge
Age is calculated relative to the refresh date, so it will change on each refresh as time passes.
Requires: A table with a Date of Birth column.
Strips currency symbols and thousands separators from a text value before converting it to a proper number.
Data Types Intermediate
Copy code
let
Source = YourTable,
Cleaned = Table.TransformColumns (Source, {{"ColumnName" , each Number.From (Text.Select (_, {"0" .."9" , "." })), type number}})
in
Cleaned
This removes everything except digits and the decimal point; use Convert Parentheses to Negative Numbers first if negatives are shown as ($100).
Requires: A text column containing currency-formatted values.
Converts accounting-style values shown in parentheses, like (100), into a proper negative number.
Data Types Intermediate
Copy code
let
Source = YourTable,
Converted = Table.TransformColumns (Source, {{"ColumnName" , each
if Text.StartsWith (Text.Trim (_), "(" ) then
-Number.From (Text.Select (_, {"0" .."9" , "." }))
else
Number.From (Text.Select (_, {"0" .."9" , "." })),
type number}})
in
Converted
Run this before general currency cleanup if a column mixes plain numbers with parenthesized negatives.
Requires: A text column where negative values are wrapped in parentheses.
Reads every .csv file in a folder and stacks them into one combined table.
File Handling Intermediate
Copy code
let
Source = Folder.Files ("C:\Data\CsvFiles" ),
FilteredCsvFiles = Table.SelectRows (Source, each [Extension] = ".csv" ),
AddedData = Table.AddColumn (FilteredCsvFiles, "Data" , each Csv.Document ([Content], [Delimiter = "," , Encoding = 65001 , QuoteStyle = QuoteStyle.None ])),
Combined = Table.Combine (AddedData[Data]),
PromotedHeaders = Table.PromoteHeaders (Combined, [PromoteAllScalars = true ])
in
PromotedHeaders
All files must share the same column layout; adjust the delimiter/encoding if your CSVs use different settings.
Requires: A folder containing CSV files with a consistent layout.
Reads every worksheet in a single Excel workbook and stacks them into one combined table.
File Handling Intermediate
Copy code
let
Source = Excel.Workbook (File.Contents ("C:\Data\Workbook.xlsx" ), true ),
OnlySheets = Table.SelectRows (Source, each [Kind] = "Sheet" ),
CombinedData = Table.Combine (List.Transform (OnlySheets[Data], each Table.PromoteHeaders (_, [PromoteAllScalars = true ])))
in
CombinedData
Table.Combine unions columns across sheets even if their layouts differ slightly; filter OnlySheets further if you only want specific sheets.
Requires: A single Excel workbook with multiple sheets sharing a similar layout.
Loads only the most recently modified file from a folder, instead of every file in it.
File Handling Intermediate
Copy code
let
Source = Folder.Files ("C:\Data\Reports" ),
SortedByDate = Table.Sort (Source, {{"Date modified" , Order.Descending }}),
LatestFile = Table.FirstN (SortedByDate, 1 ),
Content = LatestFile{0 }[Content],
Imported = Csv.Document (Content, [Delimiter = "," , Encoding = 65001 ])
in
Imported
Swap Csv.Document for Excel.Workbook if the latest file is a workbook instead of a CSV.
Requires: A folder where only the newest file should be loaded each refresh.
Adds the source file's name and last-modified date as columns, when combining multiple files from a folder.
File Handling Intermediate
Copy code
let
Source = Folder.Files ("C:\Data\SalesFiles" ),
AddedData = Table.AddColumn (Source, "Data" , each Csv.Document ([Content], [Delimiter = "," ])),
ExpandedData = Table.ExpandTableColumn (AddedData, "Data" , {"Column1" , "Column2" }),
KeptMetadata = Table.SelectColumns (ExpandedData, {"Name" , "Date modified" , "Column1" , "Column2" })
in
KeptMetadata
Rename Name and Date modified to SourceFile and SourceModified if you want clearer column names.
Requires: A folder of files being combined, where you also need to know which file each row came from.
Connects to a SharePoint list and loads its items as a table.
File Handling Intermediate
Copy code
let
Source = SharePoint.Tables ("https://yourcompany.sharepoint.com/sites/YourSite" , [ApiVersion = 15 ]),
YourList = Source{[Id = "YourListName" ]}[Items]
in
YourList
Replace the site URL and list name/ID with your own; SharePoint.Tables lists all available lists if you're not sure of the exact ID.
Requires: Access to a SharePoint list on the target site.
Extracts tables from a PDF file using Power Query's built-in PDF connector.
File Handling Intermediate
Copy code
let
Source = Pdf.Tables (File.Contents ("C:\Data\Report.pdf" ), [Implementation = "1.3" ]),
Page1Table1 = Source{[Id = "Table001" , Page = 1 ]}[Data]
in
Page1Table1
Pdf.Tables returns one row per detected table; browse Source in the Navigator to find the right Id/Page for your document.
Requires: A PDF file containing tables the connector can detect.
Extracts an HTML table from a web page's URL using Power Query's Web connector.
File Handling Intermediate
Copy code
let
Source = Web.Page (Web.Contents ("https://example.com/data-page" )),
TargetTable = Source{0 }[Data]
in
TargetTable
Change the index in Source{0} to pick a different table if the page contains more than one.
Requires: A public web page containing an HTML table.
Parses a column that holds a JSON string (not yet nested records) into structured data.
API & Web Data Intermediate
Copy code
let
Source = YourTable,
AddedParsedJson = Table.AddColumn (Source, "ParsedJson" , each Json.Document ([JsonTextColumn]))
in
AddedParsedJson
ParsedJson will contain records or lists depending on the JSON's shape; expand it the same way you would after Json.Document(Web.Contents(...)).
Requires: A text column containing valid JSON strings.
Loads and parses an XML file or string into a navigable table structure.
API & Web Data Intermediate
Copy code
let
Source = Xml.Tables (File.Contents ("C:\Data\Export.xml" ))
in
Source
Xml.Tables returns nested tables mirroring the XML structure; drill into and expand the relevant nested table columns.
Requires: An XML file or binary source to parse.
Runs a hand-written SQL query against a database instead of letting Power Query build the query visually.
Database & Refresh Intermediate
Copy code
let
Source = Sql.Database ("YourServer" , "YourDatabase" , [Query = "SELECT OrderID, OrderDate, Amount FROM Sales.Orders WHERE Amount > 100" ])
in
Source
Native SQL queries can fold better for complex logic, but Power BI Desktop will warn about native database queries for security review before running them.
Requires: A SQL Server (or compatible) database connection.
Builds a data source path from a parameter so the same query can point at dev or prod without editing the M code.
Database & Refresh Intermediate
Copy code
let
ServerName = if Environment = "Prod" then "prod-sql-server" else "dev-sql-server" ,
Source = Sql.Database (ServerName, "YourDatabase" )
in
Source
Environment should be a Power Query text parameter with a list of values like Dev and Prod, switched before publishing.
Requires: An Environment parameter, and matching dev/prod data sources.
Compares two versions of a table by key and returns which rows are new and which have been deleted.
Comparing & Lookups Intermediate
Copy code
let
NewRows = Table.SelectRows (Table.NestedJoin (CurrentTable, {"ID" }, PreviousTable, {"ID" }, "Match" , JoinKind.LeftOuter ), each Table.IsEmpty ([Match])),
DeletedRows = Table.SelectRows (Table.NestedJoin (PreviousTable, {"ID" }, CurrentTable, {"ID" }, "Match" , JoinKind.LeftOuter ), each Table.IsEmpty ([Match]))
in
NewRows
Load NewRows and DeletedRows as two separate queries; both use the same pattern with CurrentTable/PreviousTable swapped.
Requires: Two tables sharing the same key column: a current snapshot and a previous one.
A reusable custom function that trims, cleans and proper-cases any text value passed to it.
Advanced M Functions Intermediate
Copy code
(inputText as nullable text) as nullable text =>
if inputText = null then null else Text.Proper (Text.Clean (Text.Trim (inputText)))
Save this as its own query named fnCleanText, then call it from other queries with Table.TransformColumns(Source, {{ColumnName, fnCleanText, type text}}).
Requires: None - this is a standalone reusable function; call it from any query in the same file.
Remove Blank Rows
Removes rows where every column is null or an empty string.
Data Cleaning Beginner
Copy code
let
Source = YourTable,
RemovedBlankRows = Table.SelectRows (
Source,
each not List.IsEmpty (List.RemoveMatchingItems (Record.FieldValues (_), {"" , null }))
)
in
RemovedBlankRows
Replace YourTable with the name of the previous step in your query.
Requires: A table to check for fully blank rows.
Trim and Clean Text
Removes leading/trailing spaces and non-printable characters from a text column.
Data Cleaning Beginner
Copy code
let
Source = YourTable,
Cleaned = Table.TransformColumns (Source, {{"ColumnName" , each Text.Clean (Text.Trim (_)), type text}})
in
Cleaned
Replace ColumnName with your text column's name.
Requires: A table with a text column.
Convert Text to Proper Case
Capitalizes the first letter of each word in a text column.
Text Transformations Beginner
Copy code
let
Source = YourTable,
ProperCased = Table.TransformColumns (Source, {{"ColumnName" , Text.Proper , type text}})
in
ProperCased
Replace ColumnName with your text column's name.
Requires: A table with a text column.
Replace Null Values
Replaces null values in a column with a default value.
Data Cleaning Beginner
Copy code
let
Source = YourTable,
ReplacedNulls = Table.ReplaceValue (Source, null , 0 , Replacer.ReplaceValue , {"ColumnName" })
in
ReplacedNulls
Use 0 as the default for numeric columns, or "" for text columns.
Requires: A table with a column that may contain nulls.
Catches error values in a column and replaces them with null instead of failing the query.
Error Handling Beginner
Copy code
let
Source = YourTable,
ReplacedErrors = Table.ReplaceErrorValues (Source, {{"ColumnName" , null }})
in
ReplacedErrors
List every column you want error-checked as an extra {"ColumnName", null} pair.
Requires: A table with a column that may produce row-level errors.
Keeps only one copy of each identical row.
Row Operations Beginner
Copy code
let
Source = YourTable,
RemovedDuplicates = Table.Distinct (Source)
in
RemovedDuplicates
To de-duplicate by specific columns only, use Table.Distinct(Source, {"ColumnA", "ColumnB"}).
Requires: A table that may contain exact duplicate rows.
Calculate Month Start and Month End
Adds columns for the first and last day of the month for each date.
Date & Time Beginner
Copy code
let
Source = YourTable,
AddedMonthStart = Table.AddColumn (Source, "MonthStart" , each Date.StartOfMonth ([DateColumn]), type date),
AddedMonthEnd = Table.AddColumn (AddedMonthStart, "MonthEnd" , each Date.EndOfMonth ([DateColumn]), type date)
in
AddedMonthEnd
Replace DateColumn with your date column's name.
Requires: A table with a date column.
Adds a column whose value depends on an if/then/else condition.
Conditional Logic Beginner
Copy code
let
Source = YourTable,
AddedCategory = Table.AddColumn (
Source,
"SalesCategory" ,
each if [Amount] >= 10000 then "High" else if [Amount] >= 1000 then "Medium" else "Low" ,
type text
)
in
AddedCategory
Replace Amount and the thresholds with your own column and business rules.
Requires: A table with a numeric column to branch on.
Create a Custom Column
Adds a calculated column from two existing columns.
Column Operations Beginner
Copy code
let
Source = YourTable,
AddedTotal = Table.AddColumn (Source, "Total" , each [Quantity] * [UnitPrice], type number)
in
AddedTotal
Replace Quantity and UnitPrice with your own column names.
Requires: A table with the columns used in the calculation.
Splits a text column into separate columns using a delimiter.
Text Transformations Beginner
Copy code
let
Source = YourTable,
SplitColumn = Table.SplitColumn (
Source, "FullName" , Splitter.SplitTextByDelimiter (" " , QuoteStyle.Csv ), {"FirstName" , "LastName" }
)
in
SplitColumn
Replace " " with your delimiter, and name the resulting columns as needed.
Requires: A table with a text column that contains the delimiter.
Combine Text Columns
Joins two or more text columns into one, with a separator.
Text Transformations Beginner
Copy code
let
Source = YourTable,
AddedFullName = Table.AddColumn (Source, "FullName" , each Text.Combine ({[FirstName], [LastName]}, " " ), type text)
in
AddedFullName
Add more fields to the list to combine more columns.
Requires: A table with the text columns to combine.
Filter Rows Using Multiple Conditions
Keeps only rows that satisfy more than one condition at once.
Row Operations Beginner
Copy code
let
Source = YourTable,
FilteredRows = Table.SelectRows (Source, each [Region] = "West" and [Amount] > 1000 )
in
FilteredRows
Swap and for or to match either condition instead of both.
Requires: A table with the columns used in the conditions.
Keep Top N Rows
Sorts a table and keeps only the top N rows.
Row Operations Beginner
Copy code
let
Source = YourTable,
Sorted = Table.Sort (Source, {{"Amount" , Order.Descending }}),
TopRows = Table.FirstN (Sorted, 10 )
in
TopRows
Change 10 to however many rows you need.
Requires: A table with a column to sort by.
Turns wide columns (like one column per month) into Attribute/Value pairs.
Table Transformations Beginner
Copy code
let
Source = YourTable,
UnpivotedColumns = Table.UnpivotOtherColumns (Source, {"ProductID" , "ProductName" }, "Month" , "Sales" )
in
UnpivotedColumns
List the columns to keep as-is; every other column gets unpivoted into Month/Sales pairs.
Requires: A wide table with one column per category you want to unpivot.
Expands a column that contains nested tables into regular columns.
Table Transformations Beginner
Copy code
let
Source = YourTable,
ExpandedTable = Table.ExpandTableColumn (Source, "NestedColumn" , {"Field1" , "Field2" }, {"Field1" , "Field2" })
in
ExpandedTable
Replace NestedColumn and the field list with your actual column and field names.
Requires: A table with a column whose values are themselves tables.
Convert a List into a Table
Turns a plain list of values into a one-column table.
List & Record Operations Beginner
Copy code
let
Source = {"Apples" , "Bananas" , "Cherries" },
ConvertedToTable = Table.FromList (Source, Splitter.SplitByNothing (), {"Fruit" })
in
ConvertedToTable
Replace the sample list and column name with your own.
Requires: A list value, such as one produced by another step.
Drops columns you don't need as the very first step, so later transformations run on a smaller table.
Performance Optimization Beginner
Copy code
let
Source = YourTable,
RemovedOtherColumns = Table.SelectColumns (Source, {"OrderID" , "OrderDate" , "Amount" })
in
RemovedOtherColumns
List only the columns you actually need for the rest of the query.
Requires: A source table with more columns than you actually need.
Converts a text column containing numeric values into a proper Number type.
Data Types Beginner
Copy code
let
Source = YourTable,
ChangedType = Table.TransformColumnTypes (Source, {{"ColumnName" , type number}})
in
ChangedType
If the text uses a different decimal separator, pass a locale as the third argument, e.g. Table.TransformColumnTypes(Source, {{"ColumnName", type number}}, "en-US").
Requires: A text column containing numbers.
Converts a text column containing dates into a proper Date type, using an explicit locale to avoid misreads.
Data Types Beginner
Copy code
let
Source = YourTable,
ChangedType = Table.TransformColumnTypes (Source, {{"ColumnName" , type date}}, "en-US" )
in
ChangedType
Setting the locale ("en-US" for MM/DD/YYYY, "en-GB" for DD/MM/YYYY, and so on) prevents Power Query from misreading ambiguous dates.
Requires: A text column containing date-like values.
Changes the type of every column in a table to text in one step, useful before exporting or comparing rows.
Data Types Beginner
Copy code
let
Source = YourTable,
ColumnList = Table.ColumnNames (Source),
ChangedType = Table.TransformColumnTypes (Source, List.Transform (ColumnList, each {_, type text}))
in
ChangedType
Table.ColumnNames reads the table's current columns automatically, so this keeps working even if columns are added or removed later.
Requires: Any table.
Turns a numeric column into a formatted currency string, such as $1,234.50.
Data Types Beginner
Copy code
let
Source = YourTable,
AddedFormatted = Table.AddColumn (Source, "AmountFormatted" , each Number.ToText ([Amount], "C" , "en-US" ), type text)
in
AddedFormatted
Swap "C" for "P" (percentage) or "N" (plain number with thousands separator), and change the locale to match your currency.
Requires: A table with a numeric column.
Maps text values like "Yes"/"No" to a proper logical (Boolean) column.
Data Types Beginner
Copy code
let
Source = YourTable,
AddedBoolean = Table.AddColumn (Source, "IsActive" , each [Status] = "Yes" , type logical)
in
AddedBoolean
Adjust the comparison to match your actual text values, such as [Status] = "Active" or Text.Upper([Status]) = "Y".
Requires: A table with a text column that represents a true/false state.
Flags rows whose value falls between two thresholds, using Power Query's between-style comparison.
Conditional Logic Beginner
Copy code
let
Source = YourTable,
AddedInRange = Table.AddColumn (
Source,
"InTargetRange" ,
each [Amount] >= 100 and [Amount] <= 500 ,
type logical
)
in
AddedInRange
M has no built-in BETWEEN keyword - chaining >= and <= with and is the standard way to express a range.
Requires: A table with a numeric column to test against a range.
Flags rows where a column's value matches any item in a predefined list.
Conditional Logic Beginner
Copy code
let
Source = YourTable,
TargetRegions = {"West" , "East" , "Central" },
AddedFlag = Table.AddColumn (Source, "IsTargetRegion" , each List.Contains (TargetRegions, [Region]), type logical)
in
AddedFlag
Update TargetRegions with the values you want to match against - this scales better than a long chain of or conditions.
Requires: A table with a column to check against a fixed set of values.
Assigns a letter grade or tier based on several ordered thresholds.
Conditional Logic Beginner
Copy code
let
Source = YourTable,
AddedGrade = Table.AddColumn (
Source,
"Grade" ,
each if [Score] >= 90 then "A"
else if [Score] >= 80 then "B"
else if [Score] >= 70 then "C"
else "F" ,
type text
)
in
AddedGrade
Check thresholds from highest to lowest - the first condition that matches wins, so order matters.
Requires: A table with a numeric column to grade.
Adds a simple true/false column instead of text, which is cheaper to filter and aggregate on later.
Conditional Logic Beginner
Copy code
let
Source = YourTable,
AddedFlag = Table.AddColumn (Source, "IsOverdue" , each [DueDate] < DateTime.LocalNow (), type logical)
in
AddedFlag
A logical column is smaller and faster to filter on than an equivalent text column like "Yes"/"No".
Requires: A table with a date or datetime column to compare.
Adds a boolean column to a date table marking Saturdays and Sundays as non-working days.
Date & Time Beginner
Copy code
let
Source = YourDateTable,
AddedIsWeekend = Table.AddColumn (Source, "IsWeekend" , each Date.DayOfWeek ([Date], Day.Monday ) >= 5 , type logical),
AddedIsWorkingDay = Table.AddColumn (AddedIsWeekend, "IsWorkingDay" , each not [IsWeekend], type logical)
in
AddedIsWorkingDay
Replace YourDateTable and [Date] with your existing date table and its date column.
Requires: A date table with a Date column.
Adds a sortable text column combining year and month so month names sort chronologically instead of alphabetically.
Date & Time Beginner
Copy code
let
Source = YourTable,
AddedSortColumn = Table.AddColumn (Source, "MonthYearSort" , each Date.ToText ([DateColumn], "yyyy-MM" ), type text)
in
AddedSortColumn
Use MonthYearSort as the 'Sort by column' for a MonthName or MonthYear label column in the model.
Requires: A table with a date column.
Adds columns for the Monday (week start) and Sunday (week end) of the week each date falls in.
Date & Time Beginner
Copy code
let
Source = YourTable,
AddedWeekStart = Table.AddColumn (Source, "WeekStart" , each Date.StartOfWeek ([DateColumn], Day.Monday ), type date),
AddedWeekEnd = Table.AddColumn (AddedWeekStart, "WeekEnd" , each Date.EndOfWeek ([DateColumn], Day.Monday ), type date)
in
AddedWeekEnd
Change Day.Monday to Day.Sunday if your week starts on Sunday.
Requires: A table with a date column.
Splits a single Full Name column into separate First Name and Last Name columns at the first space.
Text Transformations Beginner
Copy code
let
Source = YourTable,
AddedFirstName = Table.AddColumn (Source, "FirstName" , each Text.BeforeDelimiter ([FullName], " " ), type text),
AddedLastName = Table.AddColumn (AddedFirstName, "LastName" , each Text.AfterDelimiter ([FullName], " " ), type text)
in
AddedLastName
Names with a middle name will put everything after the first space into LastName; adjust if you need smarter handling.
Requires: A table with a single Full Name column separated by a space.
Adds a boolean column flagging whether a text value looks like a valid email address.
Text Transformations Beginner
Copy code
let
Source = YourTable,
AddedIsValidEmail = Table.AddColumn (Source, "IsValidEmail" , each
Text.Contains ([Email], "@" ) and
Text.Contains (Text.AfterDelimiter ([Email], "@" ), "." ) and
Text.Length ([Email]) > 5 ,
type logical)
in
AddedIsValidEmail
This is a lightweight sanity check, not full RFC validation - it catches obviously malformed addresses.
Requires: A table with an Email text column.
Converts empty-string values in a table to true nulls, which Power Query treats differently from blanks.
Data Cleaning Beginner
Copy code
let
Source = YourTable,
Replaced = Table.ReplaceValue (Source, "" , null , Replacer.ReplaceValue , Table.ColumnNames (Source))
in
Replaced
Run this before type conversion - empty strings often fail to convert to number or date, while null converts cleanly.
Requires: A table where missing values are stored as empty strings instead of null.
Removes any row where a given column contains an error value, instead of replacing the error.
Error Handling Beginner
Copy code
let
Source = YourTable,
RemovedErrors = Table.SelectRows (Source, each (try [ColumnName])[HasError] = false )
in
RemovedErrors
Each row is evaluated with try, which returns HasError = true only for rows where ColumnName is an error value.
Requires: A table with a column that may contain row-level errors.
Keeps only the rows where a given column contains an error, to inspect and fix bad source data.
Error Handling Beginner
Copy code
let
Source = YourTable,
OnlyErrors = Table.SelectRows (Source, each (try [ColumnName])[HasError] = true )
in
OnlyErrors
Combine with Table.AddColumn to add an ErrorReason column using (try [ColumnName])[Error][Message] for more detail.
Requires: A table with a column that may contain row-level errors.
Wraps a calculation in try...otherwise so the query keeps running with a fallback value instead of failing on the first error.
Error Handling Beginner
Copy code
let
Source = YourTable,
AddedSafeColumn = Table.AddColumn (Source, "SafeResult" , each try [Numerator] / [Denominator] otherwise 0 , type number)
in
AddedSafeColumn
Replace the expression after try with your own calculation, and the value after otherwise with whatever fallback makes sense (0, null, "N/A").
Requires: A table where a calculation may fail for some rows (e.g. division by zero).
Unpivots every column except a named list of key columns, useful when the columns to unpivot vary between refreshes.
Table Transformations Beginner
Copy code
let
Source = YourTable,
KeyColumns = {"ID" , "Region" },
Unpivoted = Table.UnpivotOtherColumns (Source, KeyColumns, "Attribute" , "Value" )
in
Unpivoted
List every column that should stay as-is in KeyColumns; everything else becomes Attribute/Value pairs.
Requires: A table with one or more identifier columns and any number of value columns.
Moves a specific set of columns to the front, leaving the rest in their existing order.
Column Operations Beginner
Copy code
let
Source = YourTable,
PriorityColumns = {"ID" , "Name" , "Date" },
RemainingColumns = List.RemoveItems (Table.ColumnNames (Source), PriorityColumns),
Reordered = Table.ReorderColumns (Source, PriorityColumns & RemainingColumns)
in
Reordered
Everything not listed in PriorityColumns keeps its original relative order after the priority columns.
Requires: A table where a few key columns should always appear first.
Adds a numeric sort-order column for a text category so it sorts by business meaning instead of alphabetically.
Column Operations Beginner
Copy code
let
Source = YourTable,
SortOrder = [High = 1 , Medium = 2 , Low = 3 ],
AddedSortOrder = Table.AddColumn (Source, "SortOrder" , each Record.Field (SortOrder, [Priority]), Int64.Type )
in
AddedSortOrder
Set SortOrder as the 'Sort by column' for Priority in the Power BI model.
Requires: A table with a text category column whose values match the keys in SortOrder.
Converts an Excel date serial number (like 45000) stored as a number into a real Date value.
Date & Time Beginner
Copy code
let
Source = YourTable,
AddedDate = Table.AddColumn (Source, "ConvertedDate" , each Date.From ([SerialNumber]), type date)
in
AddedDate
Date.From treats a plain number as an Excel-style serial date automatically, using the same epoch Excel uses.
Requires: A number column holding Excel date serial numbers.
Converts a Unix epoch timestamp (seconds since 1970-01-01) into a proper DateTime value.
Date & Time Beginner
Copy code
let
Source = YourTable,
AddedDateTime = Table.AddColumn (Source, "ConvertedDateTime" , each #datetime (1970 , 1 , 1 , 0 , 0 , 0 ) + #duration (0 , 0 , 0 , [UnixTimestamp]), type datetime)
in
AddedDateTime
If your timestamps are in milliseconds instead of seconds, divide [UnixTimestamp] by 1000 first.
Requires: A number column holding Unix epoch timestamps in seconds.
Shifts a UTC datetime value by a fixed offset to display it in a specific local time zone.
Date & Time Beginner
Copy code
let
Source = YourTable,
AddedLocalTime = Table.AddColumn (Source, "LocalTime" , each [UtcDateTime] + #duration (0 , -5 , 0 , 0 ), type datetime)
in
AddedLocalTime
Change the -5 hour offset to match your target time zone; this doesn't automatically adjust for daylight saving.
Requires: A datetime column stored in UTC.
Calculates the number of whole days between two date columns.
Date & Time Beginner
Copy code
let
Source = YourTable,
AddedDaysBetween = Table.AddColumn (Source, "DaysBetween" , each Duration.Days ([EndDate] - [StartDate]), Int64.Type )
in
AddedDaysBetween
Both columns must be type date (or datetime) for the subtraction to produce a duration.
Requires: A table with two date columns.
Converts a YYYY-MM text value into the first day of that month as a real Date.
Date & Time Beginner
Copy code
let
Source = YourTable,
AddedDate = Table.AddColumn (Source, "MonthDate" , each #date (Number.From (Text.Start ([YearMonth], 4 )), Number.From (Text.End ([YearMonth], 2 )), 1 ), type date)
in
AddedDate
Assumes YearMonth is formatted like "2026-03"; adjust the Text.Start/End offsets for other formats.
Requires: A text column formatted as YYYY-MM.
Creates a one-row table capturing the exact date and time the query last refreshed, for display on a report.
Date & Time Beginner
Copy code
let
Source = #table ({"LastRefreshed" }, {{DateTime.LocalNow ()}})
in
Source
DateTime.LocalNow() reflects the machine or service running the refresh - use DateTimeZone.FixedLocalNow() if you need a specific offset.
Requires: None - this is a standalone query.
Converts a text value like 45% into its decimal equivalent (0.45) as a proper number.
Data Types Beginner
Copy code
let
Source = YourTable,
Converted = Table.TransformColumns (Source, {{"ColumnName" , each Number.From (Text.Replace (_, "%" , "" )) / 100 , type number}})
in
Converted
If the column is already typed as text with a trailing % sign, this both strips the symbol and rescales to a decimal fraction.
Requires: A text column containing percentage values with a % sign.
Adds a category column that buckets a numeric value into labeled ranges, such as age groups.
Conditional Logic Beginner
Copy code
let
Source = YourTable,
AddedBand = Table.AddColumn (Source, "AgeGroup" , each
if [Age] < 18 then "Under 18"
else if [Age] <= 34 then "18-34"
else if [Age] <= 54 then "35-54"
else "55+" ,
type text)
in
AddedBand
Adjust the thresholds and labels to match the bands your report actually needs.
Requires: A table with a numeric column to bucket.
Loads data from a Google Sheet that's been published to the web as a CSV, without needing an API connection.
File Handling Beginner
Copy code
let
Source = Csv.Document (Web.Contents ("https://docs.google.com/spreadsheets/d/YOUR_SHEET_ID/pub?output=csv" ), [Delimiter = "," , Encoding = 65001 ]),
PromotedHeaders = Table.PromoteHeaders (Source, [PromoteAllScalars = true ])
in
PromotedHeaders
In Google Sheets, use File > Share > Publish to web, choose CSV, and paste that URL here.
Requires: A Google Sheet published to the web as CSV.
Expands a column of nested record values, common after parsing JSON from an API, into individual columns.
API & Web Data Beginner
Copy code
let
Source = YourTable,
ExpandedRecord = Table.ExpandRecordColumn (Source, "NestedRecordColumn" , {"Field1" , "Field2" }, {"Field1" , "Field2" })
in
ExpandedRecord
Use Table.ExpandRecordColumn for record-type columns (typical of parsed JSON objects); use Table.ExpandTableColumn instead for columns that hold nested tables.
Requires: A table with a column whose values are records, such as from Json.Document.
Filters a source to only the most recent N days, to keep refreshes fast during development.
Database & Refresh Beginner
Copy code
let
Source = YourTable,
CutoffDate = Date.AddDays (DateTime.Date (DateTime.LocalNow ()), -30 ),
FilteredRows = Table.SelectRows (Source, each [DateColumn] >= CutoffDate)
in
FilteredRows
Change -30 to however many days back you want to load; remove this filter before publishing if the report needs full history.
Requires: A table with a date column.
Looks up a value from a second table by key and returns only the first matching value, like Excel's VLOOKUP.
Comparing & Lookups Beginner
Copy code
let
Source = YourTable,
Merged = Table.NestedJoin (Source, {"ID" }, LookupTable, {"ID" }, "LookupMatch" , JoinKind.LeftOuter ),
Expanded = Table.ExpandTableColumn (Merged, "LookupMatch" , {"LookupValue" }, {"LookupValue" })
in
Expanded
Table.NestedJoin with ExpandTableColumn naturally returns just the first match's value per row, matching VLOOKUP's default behavior.
Requires: A lookup table with a matching key column.
Generates a table of sequential numbers, useful as dummy test data or a base table to join against.
Advanced M Functions Beginner
Copy code
let
Source = List.Numbers (1 , 1000 ),
ToTable = Table.FromList (Source, Splitter.SplitByNothing (), {"Number" }),
ChangedType = Table.TransformColumnTypes (ToTable, {{"Number" , Int64.Type }})
in
ChangedType
List.Numbers(1, 1000) generates 1 through 1000; use List.Numbers(1, 1000, 1) for a custom step.
Requires: None - this is a standalone query.