Gist f90e0dcc12099646652c5dcbc8400a24

100%

Gist f90e0dcc12099646652c5dcbc8400a24

✓ Published0🌍 Public

Last edited Mar 24, 2017

Created on Mar 24, 2017

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.

AI-generated description

Similar vizzes

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

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

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

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

75% match

Loading thumbnail…

Power Query incremental load from querypower.com

This example shows a minimal Power Query incremental-load setup derived from a querypower.com gist. It defines a table named `QueryLog.m` with two columns—`SESSION_LAST_COMMAND_START_TIME` and `SESSION_LAST_COMMAND`—both initially empty. The code uses the Power Query M language’s `let` and `#table` functions to declare the schema and seed an empty table. No external JavaScript libraries or Canvas/WebGL APIs are involved; the visualization is purely a static representation of the query structure. The rendering approach is not shown, only the M code that establishes the log table for tracking session command timestamps and actions.

HHugoberry

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

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

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

67% match