[PowerBI/PowerQuery/PowerShell] Retrieve the documentation that has been nicely packaged by dox query into the tabular Model

100%

[PowerBI/PowerQuery/PowerShell] Retrieve the documentation that has been nicely packaged by dox query into the tabular Model

✓ Published0🌍 Public

Last edited Mar 7, 2018

Created on Feb 15, 2018

This example retrieves documentation from Power BI’s embedded tabular model and writes it into markdown files organized by module and subcategory. It shows how to extract metadata from the model’s internal `dox` table using an XMLA query, then sorts and groups the results by module and name. The script uses PowerShell with the `Invoke-ASCmd` cmdlet from the SQLServer module, reading the localhost port from `msmdsrv.port.txt`, and writes each documentation row’s `doc` value to a `.md` file, escaping `{{` and `}}` for template compatibility. The output folder structure mirrors the module and subcategory hierarchy, with folders created on demand.

AI-generated description

Similar vizzes

Loading thumbnail…

[PowerBI/PowerQuery] This script works in combination with doc2markdown(fn) to load the documentation from #shared functions into the Model

This example shows how to generate Markdown documentation for all functions defined in Power Query’s `#shared` namespace, organizing them by category and module. It uses the `doc2markdown` helper to produce a Markdown table of functions, excluding the helper itself and the `Signature` function. The script leverages `Record.ToTable` to enumerate shared items, `Table.SelectRows` with `Type.Is` to filter for functions, and `Value.Metadata` to extract `Documentation.Category` attributes. It then adds columns for category, module, and subcategory using `Text.BeforeDelimiter` and `Text.AfterDelimiter`, culminating in a `dox` table with function names and their generated documentation.

HHugoberry

81% match

Loading thumbnail…

[PowerBI/PowerQuery] Function that translates the documentation attached to a function definition into Markdown text

This example demonstrates a Power Query function that converts the documentation embedded in a function’s type metadata into a Markdown-formatted text block. It first retrieves the function from Power BI’s shared namespace, then extracts fields such as description, long description, category, and examples from the `Value.Metadata` of the function’s type. The code handles missing fields with `try`/`otherwise`, and uses `Text.Trim`, `Lines.ToText`, and `List.Transform` to assemble a template with headers and code fences. The final output is a Markdown document suitable for gallery display, relying solely on the function’s own metadata as the data source.

HHugoberry

70% match

Loading thumbnail…

Gist 8e3363becb45788f796a2b31aaa4c6b3

This example shows how to programmatically generate Markdown documentation from Power Query’s metadata system, specifically pulling the name, description, category, and examples for the `SqlExpression.SchemaFrom` type. It works by reading the type’s metadata via `Value.Metadata` and `Value.Type`, then formatting the results into a structured document with headers, code blocks, and separators. The code uses the `Lines.ToText` and `List.Transform` functions to assemble the output, while `Documentation.Examples` iterates over sample entries to embed their code and results. The final output is a plain-text Markdown string, ready for use in a gallery or README.

HHugoberry

70% match

Loading thumbnail…

Extracting Power Query queries

This example shows how to extract Power Query query definitions from an embedded Power BI tabular model by reading the model’s in-memory connection string. The PowerShell script first locates the dynamic port number for the local Analysis Services instance by scanning workspace files for `msmdsrv.port.txt`. It then uses the `Invoke-ASCmd` cmdlet to query the `$SYSTEM.TMSCHEMA_DATA_SOURCES` schema, revealing the connection string. The script decodes the base64-encoded `Mashup` portion of that string, writes it to a binary ZIP archive, and uses `System.IO.Compression.ZipFile` to extract `Section1.m`, which contains the readable Power Query formulas. This approach exposes the model’s underlying data-transformation logic without any direct UI interaction.

HHugoberry

69% match

Loading thumbnail…

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.

HHugoberry

67% match

Loading thumbnail…

Gist 44e95c5ae2e212c1b41485ea7e31e0c4

This example shows a Power Query function that connects to an embedded Power BI Desktop tabular model and retrieves session information from the `$SYSTEM.DISCOVER_SESSIONS` dynamic management view. The function first locates the most recently accessed user profile folder, reads the `msmdsrv.port.txt` file to obtain the local port number, then uses that port to list available databases via the `AnalysisServices.Databases` API. It selects the first database and executes the provided DAX query using `AnalysisServices.Database`. The code demonstrates dynamic file system traversal, port discovery, and parameterized Analysis Services queries within the Power Query M language.

HHugoberry

66% match

Loading thumbnail…

Gist 1ebcc44e40d3ea92b4063ad981cac31f

This example shows how to extract and prepare query log data from a local Power BI Tabular model for further analysis. It demonstrates reading the most recent `msmdsrv.port.txt` file from the Power BI Desktop Analysis Services workspace folder to obtain the active port number. The code then uses the `AnalysisServices.Database` function to connect to that port, retrieves the database catalog name, and evaluates the `QueryLog` table. Finally, it renames two columns—`SESSION_LAST_COMMAND_START_TIME` and `SESSION_LAST_COMMAND`—to clean the schema for downstream use in Power Query.

HHugoberry

65% match

Loading thumbnail…

Gist f90e0dcc12099646652c5dcbc8400a24

This example visualizes the live query log of a Power BI tabular model by monitoring the Analysis Services workspace’s in-memory engine. It shows the most recent command and its start time, appending each new entry to a running history. The code uses the Power Query M language, calling `AnalysisServices.Database` to query the `$SYSTEM.DISCOVER_SESSIONS` DMV and `Folder.Files` to locate the `msmdsrv.port.txt` file that defines the dynamic port. The rendered output is a table combining previous and new log records via `Table.Combine`, with no external charting library involved.

HHugoberry

64% match