Dummy table serialized as XML binary

100%

Dummy table serialized as XML binary

✓ Published0🌍 Public

Last edited Apr 11, 2017

Created on Apr 10, 2017

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.

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

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

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

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

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

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

61% match

Loading thumbnail…

Converting a Power BI tabular connection string to Binary

This example demonstrates a Power Query (M) function that converts a Power BI tabular connection string into a binary format. It shows how to extract and decode the embedded Mashup component from the semicolon-delimited string. The function first splits the connection string using `Splitter.SplitTextByDelimiter`, isolates the last segment, and removes the "Mashup=" prefix. It then uses `Binary.FromText` to decode the remaining Base64-encoded text into binary data. The rendering relies entirely on the M language’s standard library, with no external libraries. This approach is useful for scenarios where a connection string’s embedded model needs to be accessed programmatically.

HHugoberry

61% match

Loading thumbnail…

Gist 8abd0d0a72b91b4d72ce79ac9d8ab6c4

This example demonstrates how Power Query’s `BinaryFormat.List` function reads binary data as a sequence of items, returning a list. It shows three distinct modes: reading until the data ends, reading a fixed number of items, and using a condition function to stop based on item values. The code constructs sample binary data with `#binary`, then applies `BinaryFormat.List` with `BinaryFormat.Byte` as the item format. The condition variant passes a lambda `(x) => x < 2` that continues reading while true, including the first failing item. The output lists (e.g., `{1, 2, 3}`) directly display the parsed bytes.

HHugoberry

59% match