JSON to Table in Power Query M

100%

JSON to Table in Power Query M

✓ Published0🌍 Public

Last edited Mar 2, 2017

Created on Feb 23, 2017

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.

AI-generated description

Similar vizzes

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

87% match

Loading thumbnail…

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.

HHugoberry

74% match

Loading thumbnail…

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.

HHugoberry

72% match

Loading thumbnail…

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.

HHugoberry

72% match

Loading thumbnail…

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.

HHugoberry

69% match

Loading thumbnail…

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.

HHugoberry

68% match

Loading thumbnail…

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.

HHugoberry

67% match

Loading thumbnail…

Power Query Table to XML

This example demonstrates a Power Query M function that converts a table into a nested XML structure, using a recursive custom function named `tab2xmlREC` to handle tables, records, and scalar values. The initial sample table, created with `#table(null,{{1,2},{3,4}})` and typed via `Value.ReplaceType`, is transformed into a string of XML elements. The implementation relies on `Table.ToRecords`, `List.Accumulate`, and `Record.FieldNames` to iterate over rows and fields, while `Text.Format` builds the XML tags. The output shows a flat representation for simple values, and the recursive call handles nested tables or records, producing a hierarchical XML string.

HHugoberry

65% match