Power Query M Code Snippets

Reusable Power Query M code snippets.

Showing 145 of 145 snippets

Append Multiple Tables Dynamically

Combines every query name in a list into one table, useful when the set of source tables can change.

Merging & AppendingAdvanced
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.

Create a Reusable Custom Function

Defines a query as a function so it can be called with different arguments from other queries.

Advanced M FunctionsAdvanced
(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.

Import Multiple Excel Files from a Folder

Reads every .xlsx file in a folder and combines a named sheet from each into one table.

File HandlingAdvanced
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.

Retrieve Data from a REST API

Calls a JSON REST API and converts the response into a table.

API & Web DataAdvanced
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.

Parse a Non-Standard Date Format

Converts a text date stored as a fixed-width string, like "20240115", into a real Date value.

Data TypesAdvanced
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.

Wrap Conditional Logic in a Reusable Function

Moves a conditional expression into its own function so it can be reused and tested independently of a table step.

Conditional LogicAdvanced
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.

Write a Recursive Function with @

Defines a function that calls itself using the @ self-reference operator, useful for recursive calculations like factorials.

Advanced M FunctionsAdvanced
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.

Iterate with List.Generate (Custom Loop)

Builds a list by repeatedly applying a step, similar to a while loop in imperative languages.

Advanced M FunctionsAdvanced
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.

Reduce a List with List.Accumulate

Folds a list down into a single value by applying a function across every item, similar to reduce() in other languages.

Advanced M FunctionsAdvanced
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.

Dynamically Invoke a Function by Name

Looks up and calls a query or function using its name as text, rather than referencing it directly.

Advanced M FunctionsAdvanced
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.

Call a Function with a List of Arguments

Invokes a function using an argument list built at runtime, instead of writing out each parameter by hand.

Advanced M FunctionsAdvanced
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.

Build a Function That Returns Another Function

Creates a function that, given one argument, returns a new specialized function - a pattern known as currying.

Advanced M FunctionsAdvanced
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.

Paginate Through an API with List.Generate

Repeatedly calls a paged REST API until it stops returning results, combining every page into one table.

Advanced M FunctionsAdvanced
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.

Add Index Column Within Each Group

Adds a sequential index that restarts at 1 for every group, instead of numbering the whole table.

Table TransformationsAdvanced
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.

Rank Within Group

Ranks rows within each group by a numeric column, highest value first.

Table TransformationsAdvanced
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.

Rename Columns Dynamically from a Mapping Table

Renames columns by joining their current names against a separate mapping table of old and new names.

Column OperationsAdvanced
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.

Convert Text to Date (Multiple Formats)

Tries several known date formats in order and returns the first one that parses successfully.

Date & TimeAdvanced
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.

Business Days Between Two Dates

Calculates the number of weekdays (Monday-Friday) between two dates, excluding weekends.

Date & TimeAdvanced
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.

Flag Outliers

Flags rows whose numeric value falls outside a set number of standard deviations from the column's average.

Conditional LogicAdvanced
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.

Load Files from a SharePoint Folder

Connects to a SharePoint document library and combines every file in a folder, the same way Folder.Files works locally.

File HandlingAdvanced
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.

Expand All Nested Columns Dynamically

Detects every record-type column in a table and expands all of them in one pass, without naming them individually.

API & Web DataAdvanced
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.

SQL Query with Date Range Parameters

Passes Power Query parameter values into a native SQL query's WHERE clause for a dynamic date range.

Database & RefreshAdvanced
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.

Incremental Refresh Filter (RangeStart / RangeEnd)

Filters a table using the RangeStart and RangeEnd parameters required for Power BI incremental refresh.

Database & RefreshAdvanced
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.

Detect Changed Values Between Two Snapshots

Joins two snapshots of the same table by key and flags rows where a tracked column's value has changed.

Comparing & LookupsAdvanced
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.

Range Lookup for Tiers (Approximate Match)

Matches a value to the correct tier in a lookup table based on a minimum threshold, like Excel's approximate VLOOKUP.

Comparing & LookupsAdvanced
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.

Fuzzy Merge for Misspelled Names

Merges two tables on a text column using fuzzy matching, so close-but-not-exact spellings still match.

Comparing & LookupsAdvanced
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.

fnBusinessDays

A reusable custom function that returns the number of weekdays between two dates.

Advanced M FunctionsAdvanced
(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.

Dynamically Change Column Types

Sets column types from a lookup list, skipping any column name that isn't actually present.

Data TypesIntermediate
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.

Keep the Latest Record per ID

Groups by an ID column and keeps only the most recent row per group, based on a date column.

Row OperationsIntermediate
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.

Generate a Date Table

Builds a continuous calendar table between two dates with year, month and quarter columns.

Date & TimeIntermediate
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.

Extract Text Between Delimiters

Pulls out the text found between two delimiters, such as text inside parentheses.

Text TransformationsIntermediate
let
    Source = YourTable,
    AddedExtract = Table.AddColumn(Source, "Extracted", each Text.BetweenDelimiters([ColumnName], "(", ")"), type text)
in
    AddedExtract

Returns an empty string for rows where the delimiters aren't found.

Requires: A table with a text column containing the delimiters.

Dynamically Select Columns

Keeps only the columns from a list that actually exist, instead of erroring if one is missing.

Column OperationsIntermediate
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.

Rename Columns Dynamically

Renames columns from a lookup list, skipping any pair whose old name isn't present.

Column OperationsIntermediate
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.

Pivot Columns

Turns unique values from one column into separate columns, aggregating another column.

Table TransformationsIntermediate
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.

Merge Tables

Looks up matching columns from a second table, similar to a SQL left join.

Merging & AppendingIntermediate
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.

Safely Access a Record Value

Returns a default value instead of an error when an expected record field doesn't exist.

List & Record OperationsIntermediate
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.

Handle Missing or Invalid Data Safely

Wraps a calculation so an error or missing value falls back to a default instead of failing the query.

Error HandlingIntermediate
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.

Buffer a Table for Performance

Loads a table into memory once with Table.Buffer so it isn't re-evaluated every time it's referenced.

Performance OptimizationIntermediate
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.

Convert a Column to Whole Number Safely

Converts a column to Int64, replacing any value that fails to convert with null instead of erroring the whole query.

Data TypesIntermediate
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.

Check a Value's Data Type at Runtime

Inspects the actual type of a value or column, useful for debugging unexpected type errors.

Data TypesIntermediate
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.

Convert a Duration to Total Hours

Converts a Duration value, like the difference between two datetimes, into a decimal number of hours.

Data TypesIntermediate
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.

Combine AND/OR Logic in a Conditional Column

Builds a conditional column that checks multiple columns together using and/or with parentheses.

Conditional LogicIntermediate
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.

Simulate a Switch Statement with a Lookup Record

Replaces a long if/else if chain with a lookup record, similar to a switch/case statement in other languages.

Conditional LogicIntermediate
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.

Conditionally Replace Values in Specific Rows Only

Replaces a column's value only where a condition is true, leaving every other row untouched.

Conditional LogicIntermediate
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.

Check for Null Safely Inside a Condition

Explicitly tests for null before comparing, since comparing a value to null directly can behave unexpectedly in M.

Conditional LogicIntermediate
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.

Define a Function with Optional Parameters

Creates a custom function where some parameters are optional and fall back to a default when omitted.

Advanced M FunctionsIntermediate
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.

Inspect a Table's Column Types with Table.Schema

Returns a table describing every column's name, data type and other metadata - useful for debugging type mismatches.

Advanced M FunctionsIntermediate
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.

Fiscal Calendar with Custom Start Month

Builds a calendar table where the fiscal year and fiscal month are calculated from a custom fiscal year start month instead of January.

Date & TimeIntermediate
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.

Holiday Table Generator

Flags dates in a date table that match a manually maintained list of holiday dates.

Date & TimeIntermediate
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.

Time Table (Hour and Minute Buckets)

Generates a standalone table of every minute in a day, useful for time-of-day analysis and slicers.

Date & TimeIntermediate
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.

Relative Date Labels (Today, Yesterday, Last 7 Days)

Adds a text column that labels each date relative to today, such as Today, Yesterday or Last 7 Days.

Date & TimeIntermediate
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.

Trim and Clean All Text Columns

Applies trim and clean to every text column in a table at once, without naming columns individually.

Data CleaningIntermediate
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.

Proper Case All Text Columns

Capitalizes the first letter of each word across every text column in a table at once.

Data CleaningIntermediate
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.

Remove Special Characters

Strips punctuation and symbols from a text column, keeping only letters, numbers and spaces.

Text TransformationsIntermediate
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.

Remove Accents from Text

Replaces accented characters (é, ü, ñ, etc.) with their plain ASCII equivalents.

Text TransformationsIntermediate
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.

Standardize Phone Numbers

Strips formatting from phone numbers and reformats the digits into a consistent (XXX) XXX-XXXX pattern.

Text TransformationsIntermediate
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.

Replace Multiple Values Using a Mapping Table

Replaces many old values with new ones in one pass by joining against a mapping table instead of chaining Table.ReplaceValue.

Data CleaningIntermediate
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.

Replace Nulls with Zero in All Number Columns

Fills null values with 0 across every numeric column in a table at once.

Data CleaningIntermediate
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.

Keep Only Duplicate Rows (for auditing)

Keeps only the rows that have at least one other duplicate, for reviewing data quality before removing duplicates.

Row OperationsIntermediate
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.

Replace Errors in All Columns

Replaces error values with null across every column in a table, without listing columns individually.

Error HandlingIntermediate
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.

Column Blank Count Profile

Builds a summary table showing how many null or blank values each column contains.

Data CleaningIntermediate
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.

Unpivot Month Columns

Unpivots only the columns that look like month names, leaving key columns like ID or Region untouched.

Table TransformationsIntermediate
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.

Pivot Without Aggregation

Pivots a column into new columns while keeping the raw values, for cases where each group only has one row per pivoted category.

Table TransformationsIntermediate
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.

Transpose Table and Keep Headers

Flips rows and columns while preserving the original column names as a new first row.

Table TransformationsIntermediate
let
    Source = YourTable,
    ColumnNamesAsRow = #table(Table.ColumnNames(Source), {Table.ColumnNames(Source)}),
    Combined = Table.Combine({ColumnNamesAsRow, Source}),
    Transposed = Table.Transpose(Combined)
in
    Transposed

Table.Transpose alone discards column names; this inserts them as a row first so they end up in the transposed output.

Requires: A table you want to flip into a rows-become-columns layout.

Combine Rows into One Cell (Comma-Separated List)

Groups rows and concatenates a column's values from each group into one comma-separated text value.

Table TransformationsIntermediate
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.

Split One Row into Many by Delimiter

Splits a delimited text value in one column into multiple rows, duplicating the rest of the row's values.

Table TransformationsIntermediate
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.

Group By with Multiple Aggregations

Groups rows and computes several aggregations (sum, average, count) on different columns in a single step.

Table TransformationsIntermediate
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.

Running Total

Adds a running total column that accumulates a numeric column's values in row order.

Table TransformationsIntermediate
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.

Previous Row Value (Lag) and Next Row Value (Lead)

Adds columns showing the previous row's and next row's value for a given column, in row order.

Table TransformationsIntermediate
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.

Remove Columns by Name Pattern

Removes every column whose name contains a given text pattern, instead of listing columns by exact name.

Column OperationsIntermediate
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.

Keep Only Columns by Name Pattern

Keeps only the columns whose name contains a given text pattern, removing everything else.

Column OperationsIntermediate
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.

Calculate Age from Date of Birth

Calculates a person's current age in whole years from a date of birth column.

Date & TimeIntermediate
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.

Convert Currency Text to Number ($1,200.50)

Strips currency symbols and thousands separators from a text value before converting it to a proper number.

Data TypesIntermediate
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.

Convert Parentheses to Negative Numbers

Converts accounting-style values shown in parentheses, like (100), into a proper negative number.

Data TypesIntermediate
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.

Combine All CSV Files in a Folder

Reads every .csv file in a folder and stacks them into one combined table.

File HandlingIntermediate
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.

Combine All Sheets in One Excel File

Reads every worksheet in a single Excel workbook and stacks them into one combined table.

File HandlingIntermediate
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.

Load the Latest File in a Folder

Loads only the most recently modified file from a folder, instead of every file in it.

File HandlingIntermediate
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.

Add File Name and Modified Date Column

Adds the source file's name and last-modified date as columns, when combining multiple files from a folder.

File HandlingIntermediate
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.

Load a SharePoint List

Connects to a SharePoint list and loads its items as a table.

File HandlingIntermediate
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.

Read Tables from PDF

Extracts tables from a PDF file using Power Query's built-in PDF connector.

File HandlingIntermediate
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.

Scrape a Table from a Web Page

Extracts an HTML table from a web page's URL using Power Query's Web connector.

File HandlingIntermediate
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.

API with Bearer Token Header

Calls a REST API that requires an Authorization: Bearer token header for authentication.

API & Web DataIntermediate
let
    Token = "YOUR_ACCESS_TOKEN",
    Source = Json.Document(Web.Contents("https://api.example.com/v1/data", [
        Headers = [Authorization = "Bearer " & Token]
    ]))
in
    Source

Store the token as a Power Query parameter instead of hardcoding it, so it's easy to rotate without editing the query.

Requires: An API that authenticates via a bearer token in the Authorization header.

Parse a JSON String Column

Parses a column that holds a JSON string (not yet nested records) into structured data.

API & Web DataIntermediate
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.

Parse XML Data

Loads and parses an XML file or string into a navigable table structure.

API & Web DataIntermediate
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.

SQL Query with Native Query

Runs a hand-written SQL query against a database instead of letting Power Query build the query visually.

Database & RefreshIntermediate
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.

Parameter-Driven Source Path (Dev vs Prod)

Builds a data source path from a parameter so the same query can point at dev or prod without editing the M code.

Database & RefreshIntermediate
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.

Find New and Deleted Rows Between Two Tables

Compares two versions of a table by key and returns which rows are new and which have been deleted.

Comparing & LookupsIntermediate
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.

fnCleanText

A reusable custom function that trims, cleans and proper-cases any text value passed to it.

Advanced M FunctionsIntermediate
(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 CleaningBeginner
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 CleaningBeginner
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 TransformationsBeginner
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 CleaningBeginner
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.

Replace Errors with Null

Catches error values in a column and replaces them with null instead of failing the query.

Error HandlingBeginner
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.

Remove Duplicate Rows

Keeps only one copy of each identical row.

Row OperationsBeginner
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.

Extract Year, Month and Day

Adds separate Year, Month and Day columns from a date column.

Date & TimeBeginner
let
    Source = YourTable,
    AddedYear = Table.AddColumn(Source, "Year", each Date.Year([DateColumn]), Int64.Type),
    AddedMonth = Table.AddColumn(AddedYear, "Month", each Date.Month([DateColumn]), Int64.Type),
    AddedDay = Table.AddColumn(AddedMonth, "Day", each Date.Day([DateColumn]), Int64.Type)
in
    AddedDay

Replace DateColumn with your date column's name.

Requires: A table with a date or datetime column.

Calculate Month Start and Month End

Adds columns for the first and last day of the month for each date.

Date & TimeBeginner
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.

Add a Conditional Column

Adds a column whose value depends on an if/then/else condition.

Conditional LogicBeginner
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 OperationsBeginner
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.

Split Text by Delimiter

Splits a text column into separate columns using a delimiter.

Text TransformationsBeginner
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 TransformationsBeginner
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 OperationsBeginner
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 OperationsBeginner
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.

Unpivot Columns

Turns wide columns (like one column per month) into Attribute/Value pairs.

Table TransformationsBeginner
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.

Expand Nested Tables

Expands a column that contains nested tables into regular columns.

Table TransformationsBeginner
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 OperationsBeginner
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.

Remove Unused Columns Early

Drops columns you don't need as the very first step, so later transformations run on a smaller table.

Performance OptimizationBeginner
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.

Convert a Text Column to Number

Converts a text column containing numeric values into a proper Number type.

Data TypesBeginner
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.

Convert a Text Column to Date

Converts a text column containing dates into a proper Date type, using an explicit locale to avoid misreads.

Data TypesBeginner
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.

Convert Every Column to Text

Changes the type of every column in a table to text in one step, useful before exporting or comparing rows.

Data TypesBeginner
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.

Format a Number as Currency Text

Turns a numeric column into a formatted currency string, such as $1,234.50.

Data TypesBeginner
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.

Convert Text Values to True/False

Maps text values like "Yes"/"No" to a proper logical (Boolean) column.

Data TypesBeginner
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.

Conditional Column Based on a Value Range

Flags rows whose value falls between two thresholds, using Power Query's between-style comparison.

Conditional LogicBeginner
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.

Conditional Column Based on List Membership

Flags rows where a column's value matches any item in a predefined list.

Conditional LogicBeginner
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.

Nested If for Multi-Tier Grading Logic

Assigns a letter grade or tier based on several ordered thresholds.

Conditional LogicBeginner
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.

Add a Boolean Flag Column from a Condition

Adds a simple true/false column instead of text, which is cheaper to filter and aggregate on later.

Conditional LogicBeginner
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.

Date Table with Working Day / Weekend Flag

Adds a boolean column to a date table marking Saturdays and Sundays as non-working days.

Date & TimeBeginner
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.

Month-Year Sort Column (e.g., 2026-03)

Adds a sortable text column combining year and month so month names sort chronologically instead of alphabetically.

Date & TimeBeginner
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.

Week Start and Week End Dates

Adds columns for the Monday (week start) and Sunday (week end) of the week each date falls in.

Date & TimeBeginner
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.

Remove Extra Spaces Between Words

Collapses repeated spaces inside a text value down to a single space, in addition to trimming the ends.

Text TransformationsBeginner
let
    Source = YourTable,
    Cleaned = Table.TransformColumns(Source, {{"ColumnName", each Text.Combine(List.Select(Text.Split(Text.Trim(_), " "), each _ <> ""), " "), type text}})
in
    Cleaned

Text.Trim only removes leading/trailing spaces; this also fixes double spaces in the middle of the text.

Requires: A table with a text column that may contain repeated spaces.

Extract Digits Only from Text

Removes every non-numeric character from a text column, leaving only the digits.

Text TransformationsBeginner
let
    Source = YourTable,
    DigitsOnly = Table.TransformColumns(Source, {{"ColumnName", each Text.Select(_, {"0".."9"}), type text}})
in
    DigitsOnly

Useful for cleaning phone numbers or codes mixed with formatting characters before converting to a number.

Requires: A table with a text column containing digits mixed with other characters.

Extract Text Before / After a Delimiter

Splits a text value at the first occurrence of a delimiter and keeps only the part before or after it.

Text TransformationsBeginner
let
    Source = YourTable,
    AddedBefore = Table.AddColumn(Source, "Before", each Text.BeforeDelimiter([ColumnName], "@"), type text),
    AddedAfter = Table.AddColumn(AddedBefore, "After", each Text.AfterDelimiter([ColumnName], "@"), type text)
in
    AddedAfter

Replace "@" with the delimiter you need; Text.BeforeDelimiter/AfterDelimiter also accept an index for the Nth occurrence.

Requires: A table with a text column containing a consistent delimiter.

Split Full Name into First and Last Name

Splits a single Full Name column into separate First Name and Last Name columns at the first space.

Text TransformationsBeginner
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.

Email Format Validation Flag

Adds a boolean column flagging whether a text value looks like a valid email address.

Text TransformationsBeginner
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.

Replace Blank Strings with Null

Converts empty-string values in a table to true nulls, which Power Query treats differently from blanks.

Data CleaningBeginner
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.

Fill Down Group Headers

Fills blank cells in a group-header column with the value above them, common in exports where a header only appears once per group.

Data CleaningBeginner
let
    Source = YourTable,
    FilledDown = Table.FillDown(Source, {"GroupColumn"})
in
    FilledDown

Make sure blank cells are actual nulls, not empty strings, before running Table.FillDown - use the Replace Blank Strings with Null snippet first if needed.

Requires: A table with a column where group values are only entered once and left blank for following rows.

Remove Error Rows

Removes any row where a given column contains an error value, instead of replacing the error.

Error HandlingBeginner
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.

Keep Only Error Rows (for debugging)

Keeps only the rows where a given column contains an error, to inspect and fix bad source data.

Error HandlingBeginner
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.

Try / Otherwise Error Handling

Wraps a calculation in try...otherwise so the query keeps running with a fallback value instead of failing on the first error.

Error HandlingBeginner
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).

Unpivot All Columns Except Key Columns

Unpivots every column except a named list of key columns, useful when the columns to unpivot vary between refreshes.

Table TransformationsBeginner
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.

Promote Headers and Remove Top N Rows

Skips a fixed number of junk rows at the top of a source (common in exported reports) before promoting the real header row.

Table TransformationsBeginner
let
    Source = YourTable,
    RemovedTopRows = Table.Skip(Source, 3),
    PromotedHeaders = Table.PromoteHeaders(RemovedTopRows, [PromoteAllScalars = true])
in
    PromotedHeaders

Change 3 to however many junk rows sit above the real header row in your source file.

Requires: A source table where the real headers aren't on the first row.

Reorder Columns

Moves a specific set of columns to the front, leaving the rest in their existing order.

Column OperationsBeginner
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.

Custom Sort Order Column (e.g., High/Medium/Low)

Adds a numeric sort-order column for a text category so it sorts by business meaning instead of alphabetically.

Column OperationsBeginner
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.

Convert Excel Serial Number to Date

Converts an Excel date serial number (like 45000) stored as a number into a real Date value.

Date & TimeBeginner
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.

Convert Unix Timestamp to DateTime

Converts a Unix epoch timestamp (seconds since 1970-01-01) into a proper DateTime value.

Date & TimeBeginner
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.

Convert UTC to Local Time

Shifts a UTC datetime value by a fixed offset to display it in a specific local time zone.

Date & TimeBeginner
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.

Days Between Two Dates

Calculates the number of whole days between two date columns.

Date & TimeBeginner
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.

Date from Year-Month Text

Converts a YYYY-MM text value into the first day of that month as a real Date.

Date & TimeBeginner
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.

Last Refresh Timestamp Table

Creates a one-row table capturing the exact date and time the query last refreshed, for display on a report.

Date & TimeBeginner
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.

Convert Percent Text to Decimal

Converts a text value like 45% into its decimal equivalent (0.45) as a proper number.

Data TypesBeginner
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.

Age Group / Number Banding (Buckets)

Adds a category column that buckets a numeric value into labeled ranges, such as age groups.

Conditional LogicBeginner
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.

Expand Nested JSON Records

Expands a column of nested record values, common after parsing JSON from an API, into individual columns.

API & Web DataBeginner
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.

Load Only the Last N Days of Data

Filters a source to only the most recent N days, to keep refreshes fast during development.

Database & RefreshBeginner
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.

VLOOKUP Equivalent (First Match)

Looks up a value from a second table by key and returns only the first matching value, like Excel's VLOOKUP.

Comparing & LookupsBeginner
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.

Number Sequence / Dummy Data Table Generator

Generates a table of sequential numbers, useful as dummy test data or a base table to join against.

Advanced M FunctionsBeginner
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.

What's in the library

145 snippets across 17 categories: 57 beginner, 61 intermediate, 27 advanced. Each one has its own page with the full code, notes and requirements: 134 snippets currently have a dedicated page.

About this library

Every snippet here is a small, working piece of Power Query M - the language behind every query in Power BI, Excel and Power Automate's data flows. Paste one into the Advanced Editor, swap the placeholder names (table, column and file names) for your own, and adjust it to fit your query.

Snippets marked Advanced usually need a bit more context about your data before they'll run as-is; check the notes and requirements under each one first.

Related tools

Once you've written or adapted a query, run it through the Power Query M Formatter to clean up the formatting. Need a date table? The Power BI Date Table Generator outputs Power Query M.