JSON expansion in Power Query M

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.

87% match

Exporting GeoJSON file to MyJson API in Power Query

This example demonstrates how to export a dynamically generated GeoJSON FeatureCollection to the MyJson API for persistent hosting. The visualization builds a 10-by-100 grid of polygon features using nested `List.Accumulate` loops, with each polygon’s coordinates computed by the `sq` function. It sends the data as a JSON payload via `Web.Contents` with a `Content-Type` header, then returns the generated API endpoint URI from the response. The code relies on Power Query’s M language functions like `Json.FromValue` and `Json.Document` to serialize and parse the JSON, showcasing a programmatic pipeline for publishing tiled geospatial data.

Searializing a table into and XML string in Power Query

This example demonstrates how to flatten a tabular dataset into a single XML string using Power Query’s M language. It shows the transformation of a table’s records into nested `<row>` elements wrapped in a top-level `<table>` tag. The code relies on `Table.ToRecords`, `List.Accumulate`, and `Record.FieldNames` to iterate over rows and columns, dynamically generating tags with `Text.Format`. Field names and values are inserted into `<#0>` and `</#0>` placeholders, producing a structured XML output. The approach is purely functional, avoiding loops, and directly serializes the table’s content into a text-based hierarchical format.

RowExpression.From as JSON

This example demonstrates a Power Query M script that converts a `RowExpression` result into JSON text, revealing the internal structure of row and function type metadata. It shows how the script recursively normalizes values—turning tables, records, and lists into JSON-compatible forms while replacing functions and type values with descriptive strings. The code relies on the M standard library functions `Json.FromValue`, `Text.FromBinary`, and `Type.Is`/`Type.IsNullable` to map primitive and complex types to their JSON representations. It also uses table and record manipulation functions like `Table.TransformColumns`, `Record.FromList`, and `Type.FunctionParameters` to expose parameter details, required flags, and nullable status. The final output is a serialized JSON string generated from the evaluated expression.

Gist fad134c9dcebee3dc1c56a9cb22de435

This example demonstrates a Power Query M function that serializes a table into a JSON object and then passes that JSON to a SQL Server stored procedure, `usp_InsertDemoJSON`, which unwraps it as a recordset and performs an INSERT operation. The code uses `Text.FromBinary` and `Json.FromValue` to convert the table, then `Value.NativeQuery` to execute the stored procedure with the JSON payload. It shows a data pipeline from a Power BI or Excel data source into a SQL database, relying on the M language’s native JSON handling and SQL Server’s JSON support.

Creating a GeoJSON file with a square 100x100 in Power Query

This example generates a GeoJSON FeatureCollection containing a 100x100 grid of square polygons entirely within Power Query. It demonstrates constructing polygon coordinates for each cell using nested list functions and the `List.Accumulate` API to iterate over rows and columns. Each feature carries an `ID` property and a `Polygon` geometry with a closed ring of five coordinate pairs. The final `Json.FromValue` call serializes the entire nested list structure into valid GeoJSON, producing a downloadable file without external GIS tools. The code visibly uses Power Query’s `let` and `in` syntax, list literals, and the `Json` functions.

66% match

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

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.

65% match

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.