Skip to main content
SimilarExport ZIP
100%

Displays all of the Power Query M scripts from the opened instances of Power BI. (ignore the ts extension, this is an M script:)

✓ Published0🌍 Public
HHugoberry
Last edited Mar 22, 2017
Created on Mar 20, 2017

This visualization displays every Power Query M script extracted from the currently open Power BI Desktop instances. It first locates the active Analysis Services workspace ports by scanning recently accessed files, then retrieves each connection string, decodes the embedded Mashup binary data, and decompresses the ZIP archive to isolate the “Formulas/Section1.m” file. The resulting M code is shown line by line, using the `Folder.Files`, `AnalysisServices.Database`, and `Binary.Decompress` APIs to reconstruct the scripts.

AI-generated description

Similar vizzes

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
76% 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
71% match
Loading thumbnail…

Fetching the M code from a PBIX file. Power BI Desktop.

This example demonstrates how to extract the M code from a Power BI Desktop file’s internal structure. It uses a two-step process: first, the `Unz` function decompresses the PBIX file to access the `DataMashup` folder, then decompresses the `Formulas/Section1.m` file within it. The code calls `File.Contents` on the local PBIX path and chains the `Unz` operations to reveal the raw M language source. The visualization highlights the underlying data model’s query definitions by directly reading the binary package.

HHugoberry
70% 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
67% match
Loading thumbnail…

Power Query finding port number of the running embeded tabular instance

This example shows how to locate the port number of a running embedded tabular instance in Power BI Desktop by reading a specific file from the user’s local workspace. It demonstrates a Power Query M function that dynamically identifies the current user by sorting folders in `C:\Users` by their `Date accessed` property, then navigates to the corresponding `AnalysisServicesWorkspaces` subfolder. The code uses the `Folder.Contents` and `Folder.Files` APIs to traverse the filesystem, filters for the file named `msmdsrv.port.txt`, and extracts its content using `Lines.FromBinary` with a specific code page. The result is the port number, presented as a text value.

HHugoberry
66% match
Loading thumbnail…

Power Query script to get msmdsrv.exe port number via R

This example shows how to retrieve the port number of the msmdsrv.exe process by combining R scripts with Power Query’s M language. It first runs a system command via `system2` to capture the task list and netstat output, then parses the results using `read.fwf` with fixed-width columns. The M code invokes the R script through `R.Execute`, formats the resulting tables with `Table.TransformColumns` and `Table.PromoteHeaders`, and finally joins the process ID from the task list with the matching netstat entry to extract the local address port. The visualization relies on text-based data processing rather than graphical libraries.

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