[Enhancement] Support M symbols for connectors that ship with Power Query

[mattmasson]

Current Behavior

We include two sets of symbols with the vscode-powerquery extension

• standard: functions that are built into the M Engine
• sdk: standard symbols + symbols that are available through an extension context (i.e. connector built with the PQ SDK)

The powerquery.general.mode setting controls which symbol library to use for validation.

We also have a way for another extension (like the PQ SDK) to push in new symbols.
https://github.com/microsoft/vscode-powerquery/blob/2ade2e1dec0835622eb2bc3cce0c481f0eb14ca6/client/src/vscode-powerquery.api.d.ts

Suggested Enhancements

Allow multiple symbol libraries to be selected

1. Enable configuration option that lets you select multiple symbol libraries and the order in which they should be loaded
2. Modify sdk library to only contain extensibility related symbols
3. Use a "Last In" model for handling duplicate symbols (i.e. new symbol definition overwrites previous symbol definition)

We would most likely want the "Standard" list to always be selected... in which case, we could refer to this feature as selecting "optional symbol libraries".

Provide additional symbol libraries for connectors

The most common request in this area is support for specific connector functions that aren't built into the M Engine but are typically Power Query environments. There are a few ways we could break this down:

Completeness

• Power BI Connectors

This approach would be the easiest for us to automate, but you might end up with a lot more symbols than you actually need. This would include all of the connectors available in Power BI Desktop (including first party and certified connectors).

By Environment

• Excel
• Power BI Desktop
• Power BI / Fabric Dataflows
• Power Platform Dataflows

Users would likely appreciate the granularity, but it would take a bit more work to automate. It would also be hard to guarantee 100% accuracy as the list of connectors in each environment often changes.

Add symbol library metadata

Allow each symbol library file to include additional metadata, such as:

• timestamp of when the symbols were created
• version of the mashup engine used to create the symbol dump

Allow additional symbols to be loaded from disk

There are two scenarios I'd like to support:

1. Let users customize their list of available symbols
2. Allow the SDK to dump symbols to a directory

With this functionality we could retire (or at least revisit) the current contract in which the SDK feeds new symbols to the language service (which seems to have a number of issues).

Localized symbols

We currently generate symbol files for the en-us locale only. The majority of our 3rd party connectors do not provide localized symbols, but we should have full localization for our Standard symbols.

Challenges

1. Ensuring we have a process that regularly updates the symbol libraries
2. Do we want to have 35+ symbol library files in the repo? (one for each locale)
3. Should we focus on allowing (advanced) users to create an up to date symbol dump using the tools that come with the PQ SDK?

[bgribaudo]

In conjunction with this, it might be nice if a template script were provided that a user could run in their preferred Power Query environment (Excel, Power BI, etc.) that would dump out its current symbols in the correct format so that they could be used as a symbol library for use with this extension.

Example: Say I want to use the exact symbols available in my current Power BI Desktop (including any symbols from non-certified custom connectors I may have present). I'd run this script, which would output either all symbols, or all non-standard symbols, then save its output in the appropriate location so that this extension can find it.

[dotnetCarpenter]

Version 0.1.61 of powerquery.vscode-powerquery, last updated 2025-02-18 still does not recognise Excel.CurrentWorkbook as stated in #180. It's been a year since this issue was created. @mattmasson any updates?

Merging #181 seems like a viable stop-gap solution while work on this continues (or is halted).