Skip to main content
SimilarExport ZIP
100%

Get current username in Power Query via LocalDB or SQL Server

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

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.

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

Power Query CurrentUser via a lightweight PowerShell HTTP API

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.

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

Get Power Query data source Connection String

This example shows how to extract the connection string from a Power Query data source using a Power Query M script. The code first retrieves the name of the embedded database catalog via the `AnalysisServices.Databases` function, then queries the Tabular Model Scripting (TMS) schema using the `AnalysisServices.Database` function with a DMV query against `$system.TMSCHEMA_DATA_SOURCES`. Finally, it returns the `ConnectionString` value from the resulting table, revealing the underlying data source details. The script relies solely on Power Query’s built-in Analysis Services connectors, not external libraries.

HHugoberry
59% match
Loading thumbnail…

Exporting data out of Power Query into SQL Server via OleDb route

This example demonstrates a direct method for inserting data from Power Query into a SQL Server database by leveraging an OleDb connection. The code constructs a dynamic INSERT statement using `Text.Format`, embedding a numeric value and a string literal into the SQL query. It connects via the `OleDb.DataSource` function with a provider string for SQLOLEDB, targeting a local server and the `QueryPower` database. The approach shows how to bypass more complex data flow layers and write raw SQL directly through the OleDb interface, though it relies on string concatenation for parameter values.

HHugoberry
58% 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
58% match