Power Query finding port number of the running embeded tabular instance

100%

Power Query finding port number of the running embeded tabular instance

✓ Published0🌍 Public

Last edited Mar 21, 2017

Created on Mar 15, 2017

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.

AI-generated description

Similar vizzes

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

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

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

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

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

72% match

Loading thumbnail…

Getting $Embedded$ connection details in Power Query via R

This example shows how to extract connection details for the Power Query engine by querying system processes and network ports. It retrieves the process ID of `msmdsrv.exe` from a filtered `tasklist` command and then cross-references it with TCP port information obtained via `netstat`. The R script parses the command-line output into fixed-width tables using base R functions (`read.fwf`, `textConnection`, `system2`). No external visualization libraries are used; the focus is on data wrangling through R's base I/O and text-processing capabilities, making the connection details available for later analysis.

HHugoberry

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

68% match

Loading thumbnail…

TODAY() function in Power Query

This example demonstrates how to retrieve the current date from Power Query by leveraging the local Analysis Services instance that Power BI Desktop uses internally. The visualization shows the TODAY() function’s result fetched through a DAX query executed against a dynamic port number. The code uses the Folder.Contents and Folder.Files functions to locate the msmdsrv.port.txt file, which stores the active port, then passes that port to AnalysisServices.Database to run the query. The output is a table with a single row containing the current date, reflecting the system’s local date at query time.

HHugoberry

67% match