Get a list of expandable columns from a table input in Power Query

100%

Get a list of expandable columns from a table input in Power Query

✓ Published0🌍 Public

Last edited Feb 23, 2017

Created on Feb 23, 2017

This example shows how to detect which columns in a Power Query table are expandable—meaning their values are records or lists—by returning a list of those column names. The code uses two custom functions in the M language: `expandableColumns` iterates over all column names and checks the type of the first row’s value in each column, while `columnHasConsistentType` verifies that a column’s type is uniform across all rows by comparing the record field structures of each value. The implementation relies on core M APIs like `Table.ColumnNames`, `Table.Column`, `Value.Type`, and `Record.Field`, and uses `List.Accumulate` for iteration and accumulation. This is a helper snippet intended for use inside Power Query, not a standalone visualization.

AI-generated description

Similar vizzes

Loading thumbnail…

JSON to Table in Power Query M

This example demonstrates a Power Query M function that converts arbitrary JSON data into a structured table. It shows the step-by-step process of detecting expandable record and list columns, then iteratively expanding them until all nested objects are flattened. The code uses core M APIs such as `Table.ExpandRecordColumn`, `Table.ExpandListColumn`, `List.Accumulate`, and `List.Generate` to handle consistent and inconsistent column types. The source is a JSON object or list, which is transformed into a final normalized table through a loop that checks column types and applies the appropriate expansion method.

HHugoberry

72% match

Loading thumbnail…

JSON expansion in Power Query M

This example demonstrates how to flatten or expand nested JSON structures using Power Query’s M language, specifically through the `List.Generate` function. It shows the step-by-step traversal of a record hierarchy, where each iteration checks whether the current value is a record, tracks the nesting level, and builds an output record that appends the field names discovered at each depth. The code relies on core M functions like `Record.FieldNames`, `Record.FieldValues`, and `Record.AddField`, along with list concatenation, to construct the expanded output. The visualization highlights a programmatic approach to transforming hierarchical JSON into a flat, tabular-friendly format, without external libraries.

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

64% match

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

57% match

Loading thumbnail…

Power Query - get signature of a function as text. Use Signature(Table.Buffer) equals "function (table as table) as table". This is an M script

This example demonstrates how to extract and display the signature of a Power Query function as readable text, using the M language. It defines a custom function that takes a function as input and returns its type signature, such as `function (table as table) as table`. The code serializes type information into text form using the `Type.Is` and `Type.IsNullable` functions, then iterates over parameter names and their types via `Type.FunctionParameters` and `Record.FieldNames`. It assembles the final signature with `Text.Combine`, handling optional parameters and nullable types, and appends the return type using `Type.FunctionReturn`. The result is a string representation of the function's contract.

HHugoberry

57% match

Loading thumbnail…

PowerQuery list functions cheat sheet

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.

HHugoberry

56% match

Loading thumbnail…

Matrix Multiplication in Power Query M. Test example = MatrixMultiplication(#table({"A","B"},{{1,2},{3,4}}), #table({"A","B"},{{1,2},{3,4}}))

This example demonstrates how to perform square matrix multiplication entirely within Power Query M, using two 2x2 tables with the values {1,2} and {3,4} as both inputs. It shows a custom function that multiplies two matrices and returns the resulting table. The implementation relies on core M language features, including `Table.ToRows`, `Table.ToColumns`, `List.Accumulate`, and `List.Zip`, to compute dot products for each row-column pair. The `dotProduct` helper iterates over zipped row and column vectors, accumulating the sum of element-wise products. The output is constructed via `Table.FromRows`, preserving the visual table format native to Power Query.

HHugoberry

56% match

Loading thumbnail…

Power Query \Microsoft.Mashup.Document\Host\Document\Analysis\AddPartitionMetaDocumentAnalyzer.cs => BeginAnalyzeDocumentPartitions

This visualization shows how Power Query’s folding engine decides whether a query can be pushed to a data source, using a generated `Expression.m` file. The code inspects a table’s structure and metadata, removes columns of complex types, and then evaluates whether the resulting expression is a native query invocation. It relies on the Power Query M language’s `Table.FromValue`, `Table.RemoveColumns`, and `Value.ResourceExpression` functions to build and analyze the expression tree. The output is a record with `IsFolded` and `HasNativeQuery` flags, revealing whether the query is foldable and whether it uses a native query path. The data source is implicit, but the logic centers on the M expression’s `Kind` and `Arguments`.

HHugoberry

55% match