Skip to main content
SimilarExport ZIP
100%

Gist 44e95c5ae2e212c1b41485ea7e31e0c4

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

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.

AI-generated description

Similar vizzes

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
82% 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
82% 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
78% 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
77% match
Loading thumbnail…

Gist ed4042b20f9bcfc3ee801039049ad47f

This example shows the port number on which the SQL Server Analysis Services process (msmdsrv.exe) is listening by extracting it from live TCP connection data. It first identifies the process ID using the Windows `tasklist` command, then filters `netstat -ano` output for established connections matching that PID, and finally parses the local address to isolate the port. The visualization is implemented in Power Query’s M language, which invokes an embedded R script via the `R.Execute` function to perform the system calls and string processing. The result is a single value displayed as a table cell, demonstrating a system-monitoring query rendered through the Power BI data pipeline.

HHugoberry
72% match
Loading thumbnail…

Gist ed5d1681970062ebe8088ea3fdbb896f

This example shows a live query log for a Power BI or Analysis Services session by displaying the most recent command timestamps and command texts. It starts with an empty table, loads prior log rows, then queries the current session’s last command data using the `MetaQuery` and `SELECT` statements against the `$SYSTEM.DISCOVER_SESSIONS` schema. The code filters out its own log entries, compares timestamps to keep only new rows since the last refresh, and appends them to the existing log. The rendering relies on Power Query’s table operations (`Table.FromRows`, `Table.SelectRows`, `Table.Combine`) rather than a visual JavaScript library.

HHugoberry
72% match
Loading thumbnail…

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

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.

HHugoberry
71% 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
70% match