Gist 47d998760fa7271ac3157ce7d9555612

100%

Gist 47d998760fa7271ac3157ce7d9555612

✓ Published0🌍 Public

Last edited Jan 29, 2018

Created on Jan 29, 2018

This example demonstrates how to query and load data from external sources using Power Query's OleDb.Query function, specifically connecting to a local CSV file and an XML catalog. The code shows two approaches: one using Microsoft's ACE OLEDB provider to read a delimited text file, and another using the MSDAOSP provider with MSXML2.DSOControl to access XML data. The visualization highlights the raw query definitions rather than rendering graphical output, focusing on the data-acquisition layer. Both queries reference file paths on the local system, with the CSV query specifying the file directory and the XML query pointing to a catalog file.

AI-generated description

Similar vizzes

Loading thumbnail…

PowerQuery an alternative to opening XML files

This example demonstrates how PowerQuery can serve as an alternative to manually opening and parsing XML files. It shows a direct query that uses the `OleDb.Query` function with the `MSDAOSP` provider and the `MSXML2.DSOControl` data source to access an XML catalog. The code specifies a file path, `c:\code\catalog.xml`, and executes the query to load its contents. This approach highlights a practical way to integrate XML data into data-analysis workflows without a separate parser. The visualization makes the data-access method visible, emphasizing the tool's role in bridging XML files and PowerQuery’s querying capabilities.

HHugoberry

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

72% match

Loading thumbnail…

Gist ba39cf5b6349b2131c577e2e046e8654

This example demonstrates a data-driven approach to XML serialization within the Power Query language, showing how database queries can be wrapped in a custom function. The code defines a let expression that calls the `Value.NativeQuery` function, targeting a SQL Server database named "QueryPower" and executing a stored procedure called `usp_InsertDemoXML` with a binary parameter. The procedure receives an XML payload stored in a variable named `dummy2XML`, which is passed as the `xmlBin` argument. This illustrates a pattern for embedding XML data into SQL Server stored procedures through Power Query, highlighting how native database queries can be integrated into data transformation workflows. The example focuses on the parameter-passing mechanism rather than visual rendering.

HHugoberry

71% match

Loading thumbnail…

Power Query embedded in an excel odc connection file

This example shows how a Power Query formula is embedded inside an Excel ODBC connection file, specifically within a Mashup.xml document. It demonstrates the structure of a single query item that generates a simple list of numbers from 1 to 20 using the `let` and `in` syntax. The code, written in Power Query’s M language, defines a source as a list range and then outputs that list. The file includes metadata such as client type (EXCEL), version, and culture settings, and the query is stored as a CDATA block within the XML, allowing for direct execution by Excel’s data mashup engine.

HHugoberry

70% match

Loading thumbnail…

Exporting data out of Power Query via AdoDb route

This example demonstrates how to export data out of Power Query by directly inserting rows into a SQL Server table through an ADO.NET connection. It uses the `Sql.Database` function to connect to a local server instance and the `Value.NativeQuery` API to execute a parameterized `INSERT` statement, passing literal values for the `@key` and `@value` parameters. The code shows a concrete way to push query results into an external database, bypassing Excel’s native output options. The visible data source is a SQL table named `[dbo].[Demo]`, and the approach highlights ADO.NET’s role in bridging Power Query transformations to persistent storage.

HHugoberry

69% match

Loading thumbnail…

Gist d2f55e96546318a1e8e393d423ea36e9

This example shows a SQL query that transfers data from one Excel sheet to another, creating a new table in a separate workbook file. The visualization demonstrates how data can be exported or copied between spreadsheet locations using an embedded query. The code uses the `SELECT * INTO` syntax with an `[Excel 8.0;DATABASE=...]` connection string, specifying the destination file path and sheet name. The query pulls all records from the source sheet and writes them to the target workbook, illustrating a straightforward data-migration pattern. The rendering approach relies on the database driver’s handling of the Excel file format, with no additional charting or graphics libraries involved.

HHugoberry

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

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

67% match