Searializing a table into and XML string in Power Query

100%

Searializing a table into and XML string in Power Query

✓ Published0🌍 Public

Last edited Apr 10, 2017

Created on Apr 6, 2017

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.

AI-generated description

Similar vizzes

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

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

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

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

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

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

64% match

Loading thumbnail…

PowerQuery an alternative to opening XML files

This example demonstrates how PowerQuery can serve as an alternative to manually opening and parsing XML files. It shows a direct query that uses the `OleDb.Query` function with the `MSDAOSP` provider and the `MSXML2.DSOControl` data source to access an XML catalog. The code specifies a file path, `c:\code\catalog.xml`, and executes the query to load its contents. This approach highlights a practical way to integrate XML data into data-analysis workflows without a separate parser. The visualization makes the data-access method visible, emphasizing the tool's role in bridging XML files and PowerQuery’s querying capabilities.

HHugoberry

63% match