Power Query Table to XML

100%

Power Query Table to XML

✓ Published0🌍 Public

Last edited Apr 6, 2017

Created on Apr 5, 2017

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.

AI-generated description

Similar vizzes

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

84% match

Loading thumbnail…

[Power Query] return the table at the end of the path eg. xmlPath(XML,{"auditfile","customersSuppliers","customerSupplier"})

This example demonstrates a Power Query function that extracts a table from nested XML by following a specified path, such as pulling customerSupplier records from an auditfile. The code shows how to traverse XML nodes using list manipulation and table operations, leveraging Power Query’s M language functions like `Table.SelectRows`, `List.Accumulate`, and `Table.FromRecords`. It handles empty tables by converting them to null values and consolidates child records into a flat table structure. The visualization highlights the query logic rather than graphical output, focusing on data-shaping mechanics within Power Query’s native environment.

HHugoberry

74% match

Loading thumbnail…

Dummy table serialized as XML binary

This example shows a Power Query routine that serializes a two-column table into a single binary XML document. The code transforms each row into a `<row>` element containing `<key>` and `<value>` tags, then wraps all rows in a root `<table>` element. It relies on the `Table.TransformRows` function to iterate over the input table, while `Binary.Combine` and `Text.ToBinary` concatenate the textual XML fragments into a single binary output. The data source is an in-memory table named `dummy`, and the result is a well-formed XML representation of that table.

HHugoberry

73% match

Loading thumbnail…

Gist ba39cf5b6349b2131c577e2e046e8654

This example demonstrates a data-driven approach to XML serialization within the Power Query language, showing how database queries can be wrapped in a custom function. The code defines a let expression that calls the `Value.NativeQuery` function, targeting a SQL Server database named "QueryPower" and executing a stored procedure called `usp_InsertDemoXML` with a binary parameter. The procedure receives an XML payload stored in a variable named `dummy2XML`, which is passed as the `xmlBin` argument. This illustrates a pattern for embedding XML data into SQL Server stored procedures through Power Query, highlighting how native database queries can be integrated into data transformation workflows. The example focuses on the parameter-passing mechanism rather than visual rendering.

HHugoberry

68% match

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

65% 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

62% match

Loading thumbnail…

Sequence of adding a table (Query4/2/1) to Power BI desktop tabular model

This example visualizes the step-by-step process of adding a new table to a Power BI Desktop tabular model, using a sequence of XMLA commands captured via a gist. It documents the workflow from initial discovery through to relationship creation and verification, with each step represented as a distinct stage in the sequence. The visualization highlights the XMLA command types (create, refresh, discover) and the DAX queries used to validate the model, showing the interplay between table creation, annotation, data refresh, and relationship management in the tabular model lifecycle.

HHugoberry

61% match

Loading thumbnail…

Gist 8ddacfa41ce888f458863185ecfc400f

This example demonstrates a stored procedure that converts XML data into relational rows for insertion into a database table. The code defines `usp_InsertDemoXML`, which accepts a `varbinary(max)` parameter, casts it to XML, and then uses XQuery’s `nodes()` and `value()` methods to extract `key` and `value` pairs from each `<row>` element. It shows a data-ingestion pattern for parsing structured XML payloads into a normalized key-value table structure. The procedure relies on T-SQL’s built-in XML support rather than external visualization libraries, focusing on server-side data transformation.

HHugoberry

61% match