Skip to main content
SimilarExport ZIP
100%

Incremental load in Power Query querypower.com

✓ Published0🌍 Public
HHugoberry
Last edited Mar 28, 2017
Created on Mar 24, 2017

This example demonstrates an incremental data-loading pattern in Power Query, showing how to append only new records to an existing dataset. It first loads a full Excel transaction table, then uses a custom function to retrieve the port number of the embedded Tabular model from Power BI Desktop’s workspace files. From that model, it queries the previous transactions via the Analysis Services API, renames the returned columns to match the source, and finds the latest timestamp. Finally, it filters the Excel table for rows newer than that timestamp and combines them with the prior data, ensuring only delta changes are appended.

AI-generated description

Similar vizzes

Loading thumbnail…

Incremental load in Power Query querypower.com

This example shows how incremental data loading works in Power Query by connecting to a local Excel spreadsheet, reading the “Transactions” table, and casting its “Time” column to datetime and “Value” to a 64-bit integer. The Power Query M code uses `Excel.Workbook` and `File.Contents` to access the file, then `Table.TransformColumnTypes` to enforce data types. It demonstrates a straightforward refresh pattern where only the source file’s contents are pulled in on each load, rather than transforming data in memory. The visualization highlights the step-by-step M formula structure, making the query logic explicit and reproducible.

HHugoberry
89% match
Loading thumbnail…

Power Query incremental load example for querypower.com

This example demonstrates a Power Query incremental load setup for querypower.com, showing how a simple list source is transformed step by step. The code defines a query named `Query1` that generates a numeric range from 0 to 9, serving as the foundational data source. It uses the Power Query M language’s `let` and `in` syntax to structure the transformation pipeline, with `Source` assigned the output of `{0..9}`. The visualization highlights the initial stage of an incremental refresh pattern, where only new or changed rows would be processed in subsequent runs. The example focuses on the query definition itself, not on external libraries or rendering, as the M code contains no visual output or advanced APIs.

HHugoberry
79% 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…

Concept for inserting data into SQL Server

This example demonstrates a concept for inserting data into SQL Server by using Power Query’s M language. It shows a table creation script for a demo table with an identity column, a key, and a value, followed by an M query that iterates over a list of key-value pairs. The code uses `List.Accumulate` to loop through the pairs and calls `OleDb.DataSource` with a SQL `INSERT` statement for each iteration, referencing the SQLOLEDB provider to connect to a local database. The result is the total number of affected records from the inserts, though the actual inserted rows are not visualized or returned in the output.

HHugoberry
63% 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
62% 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
62% 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
61% 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