Cmdlet

Add-OfficeExcelPowerQueryMetadata

Aliases: ExcelPowerQueryMetadata, ExcelQueryMetadata

Module PSWriteOffice

Aliases

ExcelPowerQueryMetadata ExcelQueryMetadata

Inputs

OfficeIMO.Excel.ExcelDocument

Outputs

System.Management.Automation.PSObject

Source

AddOfficeExcelPowerQueryMetadataCommand.cs:24 Edit on GitHub

Adds safe Power Query/connection metadata for Excel-compatible applications to own and refresh.

Syntax

Add-OfficeExcelPowerQueryMetadata [-CommandText <String>] [-Description <String>] -Name <String> [-PassThru] [-QueryTableName <String>] [-RefreshOnOpen] [-WorksheetName <String>] [<CommonParameters>]

Parameter set: Context

Add-OfficeExcelPowerQueryMetadata [-CommandText <String>] [-Description <String>] -Name <String> [-PassThru] [-Path] <String> [-QueryTableName <String>] [-RefreshOnOpen] [-WorksheetName <String>] [<CommonParameters>]

Parameter set: Path

Add-OfficeExcelPowerQueryMetadata [-CommandText <String>] [-Description <String>] -Document <ExcelDocument> -Name <String> [-PassThru] [-QueryTableName <String>] [-RefreshOnOpen] [-WorksheetName <String>] [<CommonParameters>]

Parameter set: Document

Examples

Authored help example

Add a workbook connection and worksheet query-table metadata.

Add-OfficeExcelPowerQueryMetadata -Path .\Report.xlsx `
                -Name SalesQuery `
                -WorksheetName Data `
                -CommandText 'let Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content] in Source' `
                -Description 'Sales query metadata authored by automation' `
                -RefreshOnOpen `
                -PassThru

Writes package metadata only. OfficeIMO does not execute Power Query M; Excel-compatible applications perform refresh when opened.

Parameters

-CommandText String optional: Power Query M expression stored as metadata.
-Description String optional: Connection description.
-Name String required: Connection name stored in workbook metadata.
-PassThru SwitchParameter optional: Emit metadata about the authored package parts.
-QueryTableName String optional: Optional query-table name.
-RefreshOnOpen SwitchParameter optional: Request refresh-on-open metadata.
-WorksheetName String optionalaliases: Sheet, SheetName, Worksheet: Worksheet that should own query-table metadata. Defaults to the current DSL sheet when used inside New-OfficeExcel.
-Path String requiredposition: 0aliases: FilePath, InputPathsets: Path: Workbook path to update.
-Document ExcelDocument requiredpipeline: True (ByValue)sets: Document: Workbook document to update.

Common Parameters

This command supports the common parameters: -Debug, -ErrorAction, -ErrorVariable, -InformationAction, -InformationVariable, -OutVariable, -OutBuffer, -PipelineVariable, -Verbose, -WarningAction, and -WarningVariable.

For more information, see about_CommonParameters.