### Example AzureDataExplorer.Contents Usage Source: https://github.com/microsoftdocs/powerquery-docs/blob/main/powerquery-docs/connectors/azure-data-explorer.md This example demonstrates how to connect to an Azure Data Explorer cluster and database, and retrieve data from a specific table. Ensure the cluster, database, and table names are correctly specified. ```powerquery-m Source = AzureDataExplorer.Contents("Help", "Samples", "StormEvents | where State == 'ALABAMA' | take 100", []) ``` -------------------------------- ### SQL Query Example Source: https://github.com/microsoftdocs/powerquery-docs/blob/main/powerquery-docs/native-query-folding.md This is an example of a SQL query that can be executed using Value.NativeQuery. ```sql SELECT DepartmentID, Name FROM HumanResources.Department WHERE GroupName = 'Research and Development' ``` -------------------------------- ### SQL Query Example for DirectQuery Pushdown Source: https://github.com/microsoftdocs/powerquery-docs/blob/main/powerquery-docs/connectors/azure-cosmos-db-v2.md This SQL query example illustrates how aggregate functions can be pushed down to Cosmos DB when using DirectQuery mode with a filter on the Partition Key. Ensure the query includes a WHERE clause on the partition key for optimal performance. ```sql SELECT SUM(ColumnName) FROM TableName where Product = 'SampleValue' ``` -------------------------------- ### Example usage of Table.ToNavigationTable Source: https://github.com/microsoftdocs/powerquery-docs/blob/main/powerquery-docs/helper-functions.md Demonstrates how to use the Table.ToNavigationTable function with a sample table containing various data types and function references. ```powerquery-m shared MyExtension.Contents = () => let objects = #table( {"Name", "Key", "Data", "ItemKind", "ItemName", "IsLeaf"},{ {"Item1", "item1", #table({"Column1"}, {{"Item1"}}), "Table", "Table", true}, {"Item2", "item2", #table({"Column1"}, {{"Item2"}}), "Table", "Table", true}, {"Item3", "item3", FunctionCallThatReturnsATable(), "Table", "Table", true}, {"MyFunction", "myfunction", AnotherFunction.Contents(), "Function", "Function", true} }), NavTable = Table.ToNavigationTable(objects, {"Key"}, "Name", "Data", "ItemKind", "ItemName", "IsLeaf") in NavTable; ``` -------------------------------- ### Get Help for RunPQSDKTestSuites.ps1 Source: https://github.com/microsoftdocs/powerquery-docs/blob/main/powerquery-docs/sdk-testframework/5b-connector-testing-powershell.md Retrieves detailed help information for the RunPQSDKTestSuites.ps1 utility, providing insights into its usage and parameters. ```PowerShell Get-Help .\RunPQSDKTestSuites.ps1 ``` -------------------------------- ### OData Data Source Query Example Source: https://github.com/microsoftdocs/powerquery-docs/blob/main/powerquery-docs/read-query-diagnostics.md This example shows a typical OData request and response structure within query diagnostics. It highlights the 'Request' and 'Response' sections, including headers and content placeholders, to illustrate how data source queries are represented. ```text Request: https://services.odata.org/V4/Northwind/Northwind.svc/Customers?$filter=ContactTitle%20eq%20%27Sales%20Representative%27&$select=CustomerID%2CCountry HTTP/1.1 Content-Type: application/json;odata.metadata=minimal;q=1.0,application/json;odata=minimalmetadata;q=0.9,application/atomsvc+xml;q=0.8,application/atom+xml;q=0.8,application/xml;q=0.7,text/plain;q=0.7 Response: Content-Type: application/json;odata.metadata=minimal;q=1.0,application/json;odata=minimalmetadata;q=0.9,application/atomsvc+xml;q=0.8,application/atom+xml;q=0.8,application/xml;q=0.7,text/plain;q=0.7 Content-Length: 435 ``` -------------------------------- ### Section Document Format Example Source: https://github.com/microsoftdocs/powerquery-docs/blob/main/powerquery-docs/sdk-tools/pqtest-run-compare.md This snippet shows the section document format, useful for tests requiring helper functions or complex setups. ```powerquery-m section Test; shared Helper = (x) => x + 1; shared Query = let Source = Contoso.Contents("TestEndpoint"), Result = Helper(Table.RowCount(Source)) in Result; ``` -------------------------------- ### Example M Script with Applied Steps Source: https://github.com/microsoftdocs/powerquery-docs/blob/main/powerquery-docs/query-folding-basics.md This M script demonstrates a typical query with several applied steps, including Source, Navigation, Removed other columns, Sorted rows, and Kept top rows. It shows how these steps translate into M code. ```powerquery let Source = Sql.Database("myserver.database.windows.net", "AdventureWorks"), Navigation = Source{[Schema="Sales"]}[SalesOrderHeader], "Removed other columns" = Table.SelectColumns(Navigation, {"SalesOrderID", "OrderDate", "CustomerID", "TotalDue"}), "Sorted rows" = Table.Sort("Removed other columns", {{"OrderDate", Order.Descending}}), "Kept top rows" = Table.FirstN("Sorted rows", 20) in in "Kept top rows" ``` -------------------------------- ### Example Settings File (JSON) Source: https://github.com/microsoftdocs/powerquery-docs/blob/main/powerquery-docs/sdk-testframework/4-testformat.md The settings file is a JSON file containing configuration parameters as key-value pairs. It is used to specify settings such as the extension file path, parameter query file path, and whether to fail on missing output files. ```json { 'Extension': 'snowflake.mez', 'ParameterQueryFilePath': 'contoso.parameterquery.pq' 'QueryFilePath': 'contoso.query.pq', 'FailOnMissingOutputFile': true } ``` -------------------------------- ### Set Resource to a Static GUID Source: https://github.com/microsoftdocs/powerquery-docs/blob/main/powerquery-docs/handling-authentication.md Provide a static Microsoft Entra ID resource GUID when your service's identifier is fixed. This is common for services with a well-known GUID. ```powerquery-m Resource = "44445555-eeee-6666-ffff-7777aaaa8888" // Microsoft Entra ID resource value for your service - Guid or URL ``` -------------------------------- ### Verify MySQL Connector/NET Installation Source: https://github.com/microsoftdocs/powerquery-docs/blob/main/powerquery-docs/connectors/includes/mysql-database/mysql-database-prerequisites.md Run this PowerShell command to check if the Oracle MySQL Connector/NET is installed correctly. A successful installation will display the MySQL Data Provider in the resulting dialog. ```powershell [System.Data.Common.DbProviderFactories]::GetFactoryClasses()|ogv ``` -------------------------------- ### Example .testsettings.json Configuration Source: https://github.com/microsoftdocs/powerquery-docs/blob/main/powerquery-docs/sdk-testframework/5a-connector-testing-vscode.md This JSON file configures the test discovery process for the Visual Studio Code Test Explorer. Paths are relative to the settings file location. ```json { "QueryFilePath": "../TestSuites/contoso.query.pq", "ParameterQueryFilePath": "../ParameterQueries/contoso.parameterquery.pq", "DiagnosticsPath": "../Diagnostics", "FailOnMissingOutputFile": true } ``` -------------------------------- ### FHIR Observation Resource Example Source: https://github.com/microsoftdocs/powerquery-docs/blob/main/powerquery-docs/connectors/fhir/fhir-relationships.md Example of a FHIR Observation resource, illustrating a subject reference. ```json { "resourceType": "Observation", "id": "1234", "subject": { "reference": "Patient/456" } // ... Other fields } ``` -------------------------------- ### Pack a .mez file into a .pqx file Source: https://github.com/microsoftdocs/powerquery-docs/blob/main/powerquery-docs/handling-connector-signing.md Use the 'pack' command with the -mz, -t options to convert a .mez file into a .pqx file. This prepares the connector for signing. Ensure the .pfx certificate and its password are provided if signing is intended during packing. ```powershell C:\Users\cpope\Downloads\MakePQX>MakePQX.exe pack -mz "C:\Users\cpope\OneDrive\Documents\Power BI Desktop\Custom Connectors\HelloWorld.mez" -t "C:\Users\cpope\OneDrive\Documents\Power BI Desktop\Custom Connectors\HelloWorldSigned.pqx" ``` -------------------------------- ### Example Output File (.query.pqout) Source: https://github.com/microsoftdocs/powerquery-docs/blob/main/powerquery-docs/sdk-testframework/4-testformat.md An output file contains the generated output resulting from running the 'compare' command for a test. This format is used to store the expected results of a test query. ```powerquery-m #table(type table [TIMESTAMP = datetime], {{#datetime(2014, 1, 1, 16, 0, 0.12345)}}) ``` -------------------------------- ### TripPin API Response Example Source: https://github.com/microsoftdocs/powerquery-docs/blob/main/powerquery-docs/samples/trippin/5-paging/readme.md This is an example of a JSON response from the TripPin API, showing the '@odata.nextLink' field which indicates more data is available. ```json { "@odata.context": "https://services.odata.org/V4/TripPinService/$metadata#People", "@odata.nextLink": "https://services.odata.org/v4/TripPinService/People?%24skiptoken=8", "value": [     { },     { },     { } ] } ``` -------------------------------- ### Parameter Reference Example Source: https://github.com/microsoftdocs/powerquery-docs/blob/main/powerquery-docs/data-privacy-firewall.md Example of a parameter reference within a data source function call that is subject to partition trimming logic. ```powerquery Web.Contents(myUrl) ``` -------------------------------- ### MakePQX Tool Overview Source: https://github.com/microsoftdocs/powerquery-docs/blob/main/powerquery-docs/handling-connector-signing.md The MakePQX tool is used to pack, sign, and verify Power Query connectors. It requires specific libraries to run and can be invoked from the command line. Running the tool without parameters displays help information. ```APIDOC ## MakePQX Tool Usage Usage: **MakePQX [**_options_**] [**_command_**]** Options: | Options | Description | |---------|-------------| | **-?** | **-h** | **--help** | Show help information | Commands: | Command | Description | |---------|-------------| | **pack** | Create a pqx file. | | **sign** | Signs an unsigned pqx, or countersigns if pqx is already signed. Use the **--replace** option to replace the existing signature. | | **verify** | Verify the signature status on a pqx file. Return value will be non-zero if the signature is invalid. | Use **MakePQX [**_command_**] --help** for more information about a specific command. ``` -------------------------------- ### Filter Patients by nested name properties Source: https://github.com/microsoftdocs/powerquery-docs/blob/main/powerquery-docs/connectors/fhir/fhir-query-folding-patterns.md Filters Patient resources where the family name starts with 'John' and the given name starts with 'Paul'. ```M let Patients = Fhir.Contents("https://myfhirserver.azurehealthcareapis.com", null){[Name = "Patient" ]}[Data], // Fold: "family=John&given=Paul" FilteredPatients = Table.SelectRows( Patients, each Table.MatchesAnyRows([name], each Text.StartsWith([family], "John")) and Table.MatchesAnyRows([name], each List.MatchesAny([given], each Text.StartsWith(_, "Paul")))) in FilteredPatients ``` -------------------------------- ### Run compare command for a query file (output file missing) Source: https://github.com/microsoftdocs/powerquery-docs/blob/main/powerquery-docs/sdk-tools/pqtest-compare.md Executes a single query test file using a Power Query extension and generates an output file for comparison. Use this when the .pqout file does not yet exist. ```PowerShell \.\PQTest.exe compare -e contoso.mez -q contoso.query.pq ``` -------------------------------- ### Check IBM Db2 Driver Installation Source: https://github.com/microsoftdocs/powerquery-docs/blob/main/powerquery-docs/connectors/ibm-db2-database.md Use this PowerShell command to verify if the IBM Db2 driver is installed and configured on your machine. Look for 'IBM.Data.DB2' in the InvariantName column. ```powershell [System.Data.Common.DbProviderFactories]::GetFactoryClasses() | ogv ``` -------------------------------- ### Snowflake Query Tag Example Source: https://github.com/microsoftdocs/powerquery-docs/blob/main/powerquery-docs/connectors/snowflake.md This example demonstrates how to add a Snowflake query tag for improved tracing and organization of queries, particularly in Power BI Premium environments. ```json {"PowerQuery":true,"Host":"PBI_SemanticModel_MWC","HostContext":"PowerBIPremium-DirectQuery"} ``` -------------------------------- ### Run run-compare command (Output File Generated) Source: https://github.com/microsoftdocs/powerquery-docs/blob/main/powerquery-docs/sdk-tools/pqtest-run-compare.md Execute a single query test file using PQTest.exe. This command generates an output file if one does not exist. ```PowerShell \PQTest.exe run-compare -e contoso.mez -q contoso.query.pq ``` ```json [ { "Details": "Contoso.Contents(\"TestEndpoint\")", "EndTime": "2025-12-11T18:04:14.8991822+00:00", "Method": "Compare.TestFiles", "Name": "contoso.query.pq", "StartTime": "2025-12-11T18:04:11.1532388+00:00", "Output": [ { "SourceFilePath": "contoso.query.pq", "OutputFilePath": "contoso.query.pqout", "Status": "Output File Generated", "SerializedSource": null, "SourceError": null, "OutputError": null } ], "Status": "Passed", "Type": "PQTest.Expression" } ] ``` -------------------------------- ### Configure Power Query SDK Settings in VS Code Source: https://github.com/microsoftdocs/powerquery-docs/blob/main/powerquery-docs/sdk-testframework/5a-connector-testing-vscode.md Example of a .vscode/settings.json file to configure the Power Query SDK. This includes paths for SDK tools, test settings files, and connector extension files. Use workspace variables like ${workspaceFolder} for relative paths. ```json { "powerquery.sdk.tools.location": "C:\\Users\\\\.vscode\\extensions\\powerquery.vscode-powerquery-sdk-0.7.0-win32-x64\\.nuget\\Microsoft.PowerQuery.SdkTools.2.150.3\\tools", "powerquery.sdk.test.settingsFiles": [ "${workspaceFolder}/Tests/Settings/MyConnector.testsettings.json" ], "powerquery.sdk.test.ExtensionPaths": [ "${workspaceFolder}/bin/AnyCPU/Debug/MyConnector.mez" ] } ``` -------------------------------- ### XML node structure example Source: https://github.com/microsoftdocs/powerquery-docs/blob/main/powerquery-docs/connectors/xml.md Example of an XML node containing mixed text and nested elements to demonstrate how different Power Query functions handle data extraction. ```xml Hello world ``` -------------------------------- ### Sign a .pqx file Source: https://github.com/microsoftdocs/powerquery-docs/blob/main/powerquery-docs/handling-connector-signing.md Use the 'sign' command to sign a .pqx file with a specified certificate and password. This command can also be used to countersign an already signed file, or replace an existing signature using the --replace option. ```powershell C:\Users\cpope\Downloads\MakePQX>MakePQX sign "C:\Users\cpope\OneDrive\Documents\Power BI Desktop\Custom Connectors\HelloWorldSigned.pqx" --certificate ContosoTestCertificate.pfx --password password ``` -------------------------------- ### Data Source Function Signature Example Source: https://github.com/microsoftdocs/powerquery-docs/blob/main/powerquery-docs/handling-resource-path.md This is an example of a data source function signature with a single required text parameter and an optional number parameter. The optional parameter is ignored when determining the data source path. ```powerquery-m HelloWorldWithDocs.Contents = (message as text, optional count as number) as table => ... ``` -------------------------------- ### Settings file for PQTest compare command Source: https://github.com/microsoftdocs/powerquery-docs/blob/main/powerquery-docs/sdk-tools/pqtest-compare.md This JSON file defines settings for the PQTest compare command, including extension paths, query file path, and fail on missing output file option. ```json { "ExtensionPaths": ["contoso.mez"], "QueryFilePath": "contoso.query.pq", "FailOnMissingOutputFile": true } ``` -------------------------------- ### Example Parameter Query File (.parameterquery.pq) Source: https://github.com/microsoftdocs/powerquery-docs/blob/main/powerquery-docs/sdk-testframework/4-testformat.md A parameter query file is used to retrieve test data from the data source, making test cases independent of the data source itself. This file needs to be updated to point to a specific data source when running tests for a different connector. ```powerquery-m let Source = Snowflake.Databases("POWERBI.snowflakecomputing.com", "DEMO_WH"), Database = Source{[Name="NYCTAXIDB", Kind="Database"]}[Data], Schema = Database{[Name="PUBLIC", Kind="Schema"]}[Data], Table = Schema{[Name="NYCTAXIDATA", Kind="Table"]}[Data] in Table ```