TODAY() function in Power Query

100%

TODAY() function in Power Query

✓ Published0🌍 Public

Last edited Mar 22, 2017

Created on Mar 22, 2017

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.

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

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

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

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

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

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

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

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

57% match