PowerQuery list functions cheat sheet

100%

PowerQuery list functions cheat sheet

✓ Published0🌍 Public

Last edited Jun 1, 2021

Created on Jun 1, 2021

This example is a cheat sheet for Power Query's list functions, organized as a reference card with sections for addition, averages, generators, information, membership, numerics, ordering, and selection. It shows concise code snippets with sample outputs, such as List.Sum({1, 2, 3}) ⇒ 6 and List.Generate(() => 10, each _ > 0, each _ - 1) ⇒ {10, 9, 8, 7, 6, 5, 4, 3, 2, 1}. The source is a gist by Hugoberry.

AI-generated description

Similar vizzes

Loading thumbnail…

PowerQuery List functions with unicode examples

This cheat sheet visually maps Power Query's list functions using geometric unicode symbols as both data and output markers. It organizes functions into categories like Generators, Selection, and Ordering, with each entry showing a call and its returned list. The examples highlight type-specific behaviors, such as `List.Zip` combining symbols into pairs, `List.Sort` deduplicating while ordering, and generator functions producing date, time, and numeric sequences. Unicode shapes make the transformations immediately legible, turning the snippet into a compact reference for list operations.

HHugoberry

79% match

Loading thumbnail…

Gist a25bf43fbc2ada9a1cc29f874f509134

This example shows a Power Query custom function that extracts list items by their numeric positions, returning a new list with only the selected elements. The function `List.SelectPositions` takes a source list and a list of indices, with an optional parameter to shift the starting index from zero-based to one-based numbering. It uses `List.Transform` with a buffer for performance, applying `_{_ + StartPosition}?` to safely handle out-of-range positions by returning null. The implementation relies on the standard Power Query `List` and `Value` APIs, including `Value.ReplaceType` and `Value.ReplaceMetadata` to attach documentation and usage examples.

HHugoberry

65% match

Loading thumbnail…

Power Query navigate the documentation of #shared functions. [M code]

This example visualizes the complete set of functions exposed by Power Query’s `#shared` namespace, organized into a navigable table. It shows each function’s name, module, category, example code, description, and signature. The M code uses `Record.ToTable` and `Table.SelectRow` to filter functions, then leverages `Value.Metadata` to extract documentation fields like `Documentation.Category` and `Documentation.Examples`. It also parses module names via `Text.Split` and builds signatures dynamically. The result is a self-documenting inventory, enabling users to browse and understand the entire Power Query function library directly from the environment.

HHugoberry

65% match

Loading thumbnail…

Power Query incremental load example for querypower.com

This example demonstrates a Power Query incremental load setup for querypower.com, showing how a simple list source is transformed step by step. The code defines a query named `Query1` that generates a numeric range from 0 to 9, serving as the foundational data source. It uses the Power Query M language’s `let` and `in` syntax to structure the transformation pipeline, with `Source` assigned the output of `{0..9}`. The visualization highlights the initial stage of an incremental refresh pattern, where only new or changed rows would be processed in subsequent runs. The example focuses on the query definition itself, not on external libraries or rendering, as the M code contains no visual output or advanced APIs.

HHugoberry

65% match

Loading thumbnail…

Power Query attempt at decompiling a function call

This example shows a Power Query (M) custom function that decompiles a `List.Skip` call into its component parts—the list, the offset, and an optional count. It demonstrates how M’s standard library functions `List.Skip` and `List.FirstN` are composed to recreate the logic of a function call, with `skippedInput` holding the intermediate result after skipping a given number of elements. The code reveals the underlying structure of the operation by explicitly handling a null count fallback, which would otherwise be implicit in the original function. This visualization highlights the metaprogramming aspect of Power Query, where code itself is treated as data for inspection.

HHugoberry

64% match

Loading thumbnail…

Gist 10da47bd381918d303c5db903afe5886

This example demonstrates how to generate all non-empty combinations of a set of strings using bitmask arithmetic. It shows three separate Power Query (M) implementations, each taking a hardcoded list like {"B", "F", "T"} and producing every possible subset as concatenated text. The primary approach uses `Number.BitwiseAnd` and `Number.Power` to iterate through binary masks, while `List.Transform`, `List.Select`, and `List.Positions` filter and combine elements. A recursive alternative using `@fnCombinations` is also included. The visualizations highlight different algorithmic strategies—iterative bitwise versus recursive decomposition—for the same combinatorial output, with no external libraries beyond Power Query’s built-in list functions.

HHugoberry

62% match

Loading thumbnail…

Power Query embedded in an excel odc connection file

This example shows how a Power Query formula is embedded inside an Excel ODBC connection file, specifically within a Mashup.xml document. It demonstrates the structure of a single query item that generates a simple list of numbers from 1 to 20 using the `let` and `in` syntax. The code, written in Power Query’s M language, defines a source as a list range and then outputs that list. The file includes metadata such as client type (EXCEL), version, and culture settings, and the query is stored as a CDATA block within the XML, allowing for direct execution by Excel’s data mashup engine.

HHugoberry

61% match

Loading thumbnail…

Gist 8abd0d0a72b91b4d72ce79ac9d8ab6c4

This example demonstrates how Power Query’s `BinaryFormat.List` function reads binary data as a sequence of items, returning a list. It shows three distinct modes: reading until the data ends, reading a fixed number of items, and using a condition function to stop based on item values. The code constructs sample binary data with `#binary`, then applies `BinaryFormat.List` with `BinaryFormat.Byte` as the item format. The condition variant passes a lambda `(x) => x < 2` that continues reading while true, including the first failing item. The output lists (e.g., `{1, 2, 3}`) directly display the parsed bytes.

HHugoberry

61% match