Skip to main content
SimilarExport ZIP
100%

Power Query CurrentUser via a lightweight PowerShell HTTP API

✓ Published0🌍 Public
HHugoberry
Last edited Apr 2, 2017
Created on Apr 1, 2017

This example demonstrates how Power Query can obtain the current Windows user by querying a lightweight PowerShell HTTP API. It shows the result of a `whoami` command wrapped in a JSON response. The Power Query code uses `Web.Contents` to call the local HTTP endpoint at `http://localhost:8888/?command=whoami`, and then parses the returned JSON with `Json.Document`. The underlying HTTP API service is built from a PowerShell script, based on the referenced blog post, which executes commands and returns their output. This approach provides a simple bridge between Power Query’s data-fetching capabilities and PowerShell’s system introspection.

AI-generated description

Similar vizzes

Loading thumbnail…

Power Query get current USERNAME via NTFS route

This example obtains the current Windows username by querying the file system rather than using environment variables. The Power Query M script lists the contents of the `C:\Users` directory with `Folder.Contents`, sorts the resulting table by the `Date accessed` column in descending order, and then selects the name of the first row. This assumes the current user is the one whose profile folder was accessed most recently, providing a NTFS-metadata-based fallback for identity detection.

HHugoberry
74% match
Loading thumbnail…

STDOUT as data source in Power Query M

This example demonstrates how to capture a command-line program's standard output as a data source in Power Query M. It shows a minimal query that retrieves the current user name by running the `whoami` command, then feeds that result into a table. The implementation relies on the `R.Execute` function to invoke an R script, which uses `system2` with `stdout=TRUE` to capture the command's output, and `read.table` to parse it into a data frame. The resulting table is then returned as the query's output. This approach highlights a practical bridge between operating system commands and Power BI's data transformation layer.

HHugoberry
68% match
Loading thumbnail…

Get current username in Power Query via LocalDB or SQL Server

This example shows three Power Query functions that retrieve the current username from a SQL Server environment using the `SYSTEM_USER` SQL function. It demonstrates how to query a local SQL Server instance, a LocalDB instance from SQL Server 2016 Express, and a legacy LocalDB v11.0 instance. The code uses the `Sql.Database` API from Power Query’s standard library, passing server names and a query option to execute the `SELECT` statement. Each function returns the username as a single value, illustrating the connection-string differences between LocalDB versions and a default local instance.

HHugoberry
67% match
Loading thumbnail…

R.Execute example without the use of R.Execute() in Power Query [behind the scenes this gets executed bin\x64\Rscript.exe" --encoding=UTF-8 RScriptWrapper.R"]

This example demonstrates how to execute an R script from within Power Query without explicitly invoking the `R.Execute` function, instead relying on the `AdoDotNet.DataSource` with the `R.Provider` to run a native query. The code passes a string containing an `output` variable that captures the result of `system2("whoami", stdout=TRUE)`, which retrieves the current username. The resulting table is then converted from binary R data using `RData.FromBinary`. This approach showcases the underlying use of `Rscript.exe` via the provider, highlighting an alternative integration path for R workflows in Power Query.

HHugoberry
66% 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
64% 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
64% 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
61% 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
60% match