A proof-of-concept for smart type resolvers was done using Table.AddColumn. The goal is to implement a resolver for most Table/Record/List functions. This is to track the functions found in the docs.

Information

• Table.ColumnCount
• Table.IsEmpty
• Table.Profile
• Table.RowCount
• Table.Schema
• Tables.GetRelationships

A proof-of-concept for smart type resolvers was done using Table.AddColumn. The goal is to implement a resolver for most Table/Record/List functions. This is to track the functions found in the docs.

Column operations

• Table.Column
• Table.ColumnNames
• Table.ColumnsOfType
• Table.DemoteHeaders
• Table.DuplicateColumn
• Table.HasColumns
• Table.Pivot
• Table.PrefixColumns
• Table.PromoteHeaders
• Table.RemoveColumns
• Table.ReorderColumns
• Table.RenameColumns
• Table.SelectColumns
• Table.TransformColumnNames
• Table.Unpivot
• Table.UnpivotOtherColumns

Power Query includes a hidden null coalescing operator: ??

This syntax is accepted:
Value??OtherValue

It would be nice if this was accepted by the parser, which throws the problem "Expected to find a right parenthesis ... but a question mark was found instead powerquery(Error.Parse) [7,70]"

See more here

Description

Hovering over function signatures has an error when the identifier is not correct.

Expected Behavior

Actual Behavior

It doesn't silently log like other errors. You get a popup:

[Trace - 5:12:28 PM] Received response 'textDocument/hover - (40)' in 0ms. Request failed: Request textDocument/hover failed with message: InvariantError: should never be reached (-32603).
[Error - 5:12:28 PM] Request textDocument/hover failed.
  Message: Request textDocument/hover failed with message: InvariantError: should never be reached
  Code: -32603 

Steps to reproduce

Create a new file, then hover over the Numbers function.

let
   #"Stuff" = List.Numbers(1, 1, 1)
in
    #"Stuff

Fix

Close the quote on the final identifier.

let
   #"Stuff" = List.Numbers(1, 1, 1)
in
    #"Stuff"

error log - short.log

I tried updating powerquery-parser to 0.1.22, but ended up with a number of test failures.

case: |Table.AddColumns

selecting an item from the completion item list should not replace Table.AddColumns.

Repro steps:

1. Create a new table where the first argument is a list of columns such as {"Column1", "Column2"} and complete the rest of the code
2. Note how the first argument of the constructor appears to be incorrect according to the editor

Expected behavior

I'm expecting the selected value will escaped quotes, converting it to a raw string.
I thought it would simplify adding the Code part of function's metadata ( Documentation.Examples )

For example this

let
    x  = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcszNr8hMzszJycxT0EgsVjBUcFTQVIrVwSbjjFNGAY8uhEwBskRxfkppLhYpoFkwWaBkLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Raw Name" = _t])
in 
    x

Expected Output

let
    x  = "Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(""i45WcszNr8hMzszJycxT0EgsVjBUcFTQVIrVwSbjjFNGAY8uhEwBskRxfkppLhYpoFkwWaBkLAA="", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#""Raw Name"" = _t])"
in 
    x

Actual behavior

let
    x  = 
Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(""i45WcszNr8hMzszJycxT0EgsVjBUcFTQVIrVwSbjjFNGAY8uhEwBskRxfkppLhYpoFkwWaBkLAA="", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#(#)""Raw Name"" = _t])
in 
    x

To Reproduce

Environment

Pwsh>
    code.cmd --show-versions --list-extensions | sls 'powerquery'
    code.cmd --version | Join-String -sep ', ' -op 'vscode: '

Solution?

A naive replace appears to be working, at least for this sample

1. Replace " with ""
2. Surround that with ""

Pwsh> $Content -replace '"', '""'
    | Join-String -op '"' -os '"'

PROBLEM

I wrote a test query that contained a record that I passed to my main function and found that if the record contains a date data type you will get a credential error.

STEPS TO RECREATE

1. Use the Hello World example outlined here
2. In the query file, use something like this:

let
  MyRecord =  [ StartDate = Date.FromText("2022-01-02") ],
  Result = MyConnector.Contents( MyRecord )
in
  Result

3. In the pq file, alter the Hello World example as follows:

...
shared MyConnector.Contents = ( MyRecord as record ) =>
let
    Result = MyRecord[StartDate]
in
    Result;
...

4. Run the "Set credential" task
5. With the query file open, run the "Evaluate current file" task

EXPECTED OUTCOME

The query should run with the date being outputted

ACTUAL OUTCOME

The PQTest fails with the following error:

Credentials are required to connect to the PQExtensionSample2 source. (Source at {"MyRecord":{"StartDate":"2022-01-02"}}.)

WORK AROUND

If you set the data type to text, it query will run as expected.

e.g. MyRecord = [ StartDate = "2022-01-02" ],

When typing up the following expression:

section Section1;

shared A = @A; // for simplicity's sake; for a real life example, pretend that A is a recursive function 
shared B = A;

IntelliSense for B's expression correctly includes identifier A in the list of options provided; however, the IntelliSense for A's expression does not include itself, even when using an inclusive identifier reference (@A).

Seems like A's IntelliSence should include itself, at least when an @-reference is being used.

Expected behavior
To not delete comments when formatting.

let y = 1 in y
// stuff

Actual behavior
Formatting deletes comments at the end of the file.

let y = 1 in y

To Reproduce

Using any saved or unsaved files: add comments to the end, then format.

let
    y = 1
in
    y
    // stuff

I tested all combinations of these:

• saved file
• unsaved file
• "powerquery.general.experimental": true,
• "powerquery.general.experimental": false,
• "powerquery.general.mode": "Power Query",
• "powerquery.general.mode": "SDK",

Environment

I don't think there's anything useful in the log, attached anyway.
powerquery.log

Expected behavior
Function 'PowerPlatform.Dataflows([])' should not be reported as a problem

Actual behavior
The function is reported as a problem with following message:
Cannot find the name 'PowerPlatform.Dataflows'.

To Reproduce
Please include the following:

• (Required) The Power Query script that triggers the issue.
  let Source = PowerPlatform.Dataflows([]){[Id = "Workspaces"]}[Data], Workspace = Source{[workspaceId=WS_DATA_CONVERSION]}[Data], Dataflow = Workspace{[dataflowId=DF_PM_1_EXTRACT]}[Data], Entity = Dataflow{[entity="SGDOCMP"]}[Data] in Entity

• (Required) Any non-default settings used in the API call(s) which trigger the issue.
  Not an API call as far as I'm concerned

Additional context
Add any other context about the problem here.

👋 Hi there, Martin here, from the VS Code team.

Recently we've announced the Remote Repository feature that lets you browse and edit files and folders directly on GitHub.

Open Remote Repository... opens VSCode on a folder or workspace located on a virtual file system. We call this a virtual workspace. We observed that not all extension support this well, either because they can not, or they haven't thought about it.

It would be fantastic if you could test whether your extension can handle virtual workspaces:

Check out the Virtual Workspaces Extension Author Guide on how to do that.

When done, set the new virtualWorkspaces capability in your 'package.json'.

{
  "capabilities": {
    "virtualWorkspaces": true | false
  }
}

• Use "virtualWorkspaces": true if your extension is prepared for virtual workspaces
• Use "virtualWorkspaces": false if your extension should be disabled when a virtual workspace is opened

For questions and comments please use the Virtual Workspaces Tracking Issue.

Thanks for the support and the great work! ❤️

There are important files that Microsoft projects should all have that are not present in this repository. A pull request has been opened to add the missing file(s). When the pr is merged this issue will be closed automatically.

Microsoft teams can learn more about this effort and share feedback within the open source guidance available internally.

Merge this pull request

There are a few operations that fire due to document changes such as validation, fetching document symbols, and fetching semantic tokens. We should have (centralized/shared) logic to control how often these events are run and ensure we stop processing the previous operation if a new one is triggered.

Repro
Open the Power Query output window with message tracing enabled.
Hold down a key.
See the events being fired.

Suggestion

• Debounce: delay the operations so that they aren't triggered for each key stroke
• Cancel: in progress operations should be cancelled if a new one is triggered

I believe this could be done at either the client or server level, but it might be easier to centralize in the server code.

Hi - Intellisense/auto complete is not working with my power query extension in VS code.

Syntax highlighting is working, but it won't autocomplete for me. Also, it won't do any error checking.

Steps I have tried:

• Uninstalling/reinstalling extension
• Rebooting VS code
• Trying other PQ files

My system:
Windows 10, 64 bit, VS Code 1.60.1

Any suggestions?
Thanks

Hi,

In the Q&A section for the current Visual Studio Power Query SDK, there's a response from Microsoft dated 2021-05-07 saying:

We aren't planning on making further updates to the Visual Studio based Power Query SDK. All future development efforts are focused on enabling a similar experience in VS Code.

This is exciting! :-)

Do you have any sense on the timeline as to when the similar VSCode experience will be available? From the outside perspective, tooling for custom connector development currently seems to be in limbo--for example, the existing SDK hasn't been updated since August 2019, so is using an out-of-date M engine version. It would be neat to have some sense for when a currently-updated tool will be available.

Thanks,
Ben

A string literal with the sequence backslash+double-quote gets interpreted by the syntax highlighter as a single double quote rather than treating the backslash literally, but the M language treats the backslash literally.

This breaks syntax highlighting for all code after the quote, and interprets the rest of the code as a string literal.

It would be great if the VS Code extension would support custom M-extensions with own functions as well.

Summary

This is minor: field access without quoted-identifiers highlight as separate tokens.
ex: The textmate scope of in in [Records in Range] is a language keyword

I wasn't sure whether this is at the extension level or microsoft/powerquery-parser I put it here because even though textmate's scope is a keyword -- the parser seems to format correctly. I tried using a strange generalized-identifier to see if I could break the formatting, but it did not.

Power BI's editor has the same behaviour

Test Query

let
    Source = ...,
    #"Total Records1" = Table.AddColumn(
        Source, "Count 1",
        each Table.RowCount( [#"Records in Range"] ),
        type number
    ),
    #"Total Records2" = Table.AddColumn(
        Source, "Count 2",
        each Table.RowCount( [Records in Range] ),
        type number
    )

in
    #"Total Records2"

Hello 👋 I'm from the VS Code team.

Recently, we have been exploring a security feature we refer to as Workspace Trust. This feature is intended to centralize and unify a security conscious decision required by a variety of VS Code features. With workspace trust, the user will be able to declare whether or not they trust the folder that is opened in VS Code before these features are executed.

Why you should care

Your extension is incredibly popular with VS Code users! We want to make sure that those users have a delightful experience with workspace trust and that includes extension authors deciding how much of their extension is supported in an untrusted workspace.

Workspace Trust experience

You can enable the feature with the following setting security.workspace.trust.enabled. Once enabled, you will see the following dialog when opening folders in VS Code.

This dialog is important for allowing the user to make a decision early and understand the impact of their decision. Once you understand the feature, you may want to customize when to display the dialog using the setting security.workspace.trust.startupPrompt.

You can follow the development of Workspace Trust and provide feedback in issue #106488.

Workspace trust API

First off, all of what I am about to say can be found in issue #120251. That issue will include discussion of the feature and any updates to the feature.

The Workspace Trust extension API is now in stable. This allowed us to release the first cut of our guide for onboarding your extension to Workspace Trust. The API is small, so here is a quick look.

You can declare your extension to provide complete, partial or no support in untrusted workspaces using the untrustedWorkspaces capability in package.json.

The following example declares that the extension is supported completely in untrusted workspaces. In this case, the extension is enabled in untrusted workspaces.

"capabilities": {
  "untrustedWorkspaces": {
    "supported": true
  }
}

The next example declares that the extension is not supported in untrusted workspaces. In this case, the extension is disabled in untrusted workspaces.

"capabilities": {
  "untrustedWorkspaces": {
    "supported": false
  }
}

The third option is to declared limited support. There are three tools provided to you when you select the limited option.

First, if you have a setting that can be configured in the workspace but requires the workspace to be trusted in order to apply the workspace value, then you can include the setting using restrictedConfigurations array property in untrustedWorkspaces object. Doing so, VS Code will ignore the workspace value of these restricted settings when your extension reads these settings values using the VS Code Workspace Configuration API.

The following example declares the settings that are restricted in untrusted workspaces.

"capabilities": {
  "untrustedWorkspaces": {
    "supported": "limited",
    "restrictedConfigurations": [
      "markdown.styles"
    ]
  }
}

Next, you can also check and listen if the current workspace is trusted or not programmatically using the following API:

export namespace workspace {
  /**
   * When true, the user has explicitly trusted the contents of the workspace.
   */
  export const isTrusted: boolean;
  /**
   * Event that fires when the current workspace has been trusted.
   */
  export const onDidGrantWorkspaceTrust: Event<void>;
}

Lastly, you can hide commands or views declaratively with the isWorkspaceTrusted context key in your when clauses.

A far more detailed guide on how to onboard which will be updated as we receive feedback can be found in issue #120251.

Rollout plan

Workspace Trust will remain disabled for the month of May, but we are planning on enabling this by default in the future. To prepare for that day, we would love for you to try it out and provide feedback.

We'd love your feedback

Since this issue was created in an automated fashion, we won't be monitoring the responses in this issue (our notifications would explode!). Instead we ask you to drop questions, and feedback in issue #120251 as we've mentioned above.

We're excited to see what you do with workspace trust!

1. Start a new PQ editor window
2. type # (and Ctrl+Space to force suggestions)
3. Receive no suggstions

About

Tooltips showing the current method signature sticks on page 1

When Table.AddColumn includes the optional parameter columnType it underlines the 4th parameter correctly.
But it's hidden because the tooltip should be on page 2. (I thought a previous version swapped pages)

Behavior

The video shows when the 4th parameter exists:

1. It doesn't switch to page 2
2. If you manually set it to page 2, then move the cursor left -- it jumps back to page 1

Environments

I noticed releases is on v0.1.16, I'm using the regular marketplace version

Code

let
    Source = ...,
    #"Select Active" = Table.SelectRows( Source, each [Active] = true),
    #"Add Col Date" = Table.AddColumn(
        #"Select Active", "Date", each DateTime.Date( [Datetime] ), type date )

in
    #"Add Col Date"

Hello!

Is it possible to run PowerQuery programs from within VS Code? That is, is there some open source engine that we could use and integrate with the editor for the kind of rich live editing experience we get in Excel but with the comforts of the other tooling that VS Code provides (Git, Vim bindings, etc. etc.)? This would truly be the best of both worlds as far as I'm concerned.

Thanks!

Cannot find the name 'x'.powerquery(Error.UnknownIdentifier)

#"x" and x are treated as different variables, so it is throwing Error.UnknownIdentifier

Extension Version: [email protected]

Some M functions and language features are only available or relevant in certain scenarios: for example, many M functions can only be used in custom connectors. It would be useful to provide some kind of switch that would allow a user to specify whether they were writing M for Power BI Desktop, Excel, dataflows or custom connectors etc. Setting this would stop users from seeing functions and language features that are not relevant for what they are doing.

Running vsce on the current repo has the following problems:

1. Too many files are included
2. Language and client are bundled, but server doesn't run correctly

Is your feature request related to a problem? Please describe.
I try to work with Python script into VS Editor, but is not supported
Describe the solution you'd like
I think that basic, the best solution is add as Function into dictionary

Describe alternatives you've considered
A great feature will be show the code python into this fuction of a beauty format like editor in PowerBI. (#(lf) is the char of \n into code expresed into a custom python script embebed into a string)

Additional context
Here an example:

  let
      Origen = Python.Execute("import pandas as pd#(lf)data = [['Alex',10],['Bob',12],['Clarke',13]]#(lf)df = pd.DataFrame(data,columns=['Name','Age'],dtype=float)#(lf)print (df)"),
      df1 = Origen{[Name="df"]}[Value],
      #"Tipo cambiado" = Table.TransformColumnTypes(df1,{{"Name", type text}, {"Age", Int64.Type}})
  in
      #"Tipo cambiado"

Expected behavior
Outline treeview should show document symbols for pq files.

Actual behavior
"No symbols found in document 'test.pq'

To Reproduce
Open a section document and expand the OUTLINE tree view.

This also seems to be the cause of one of the UI test failures.

Is your feature request related to a problem? Please describe.
When typing a list of key value pairs and a comma is left off of a preceding one, the current message is "Expected to find a keyword <'in'>...". However I have more to write before the 'in' statement.

Describe the solution you'd like
Could this message read something like "Expected to find a keyword <'in'> or token comma, but an identifier was found instead."

Describe alternatives you've considered
not sure

Additional context

Thanks for awesome work

I'm trying to get started with Power Query / M Language, I installed extension via visual studio code market place but unfortunately, it doesn't work, no autocomplete, no formatting...

is there any other configuration I must do... ? the npm steps are necessary?

Problem

Autocomplete isn't completing unless case matches

Expected Behavior

table.c should return Table.Count, Table.Column, etc...

Actual Behavior

Tip

It seems to require the very first character to be capitalized, but the rest of it is case-insensitive. ex:

Version

Tested on:

code = 1.54.3, 2b9aebd5354a3629c3aba0a5f5df49f43d6689f8, x64
[email protected]

code = 1.56.0-insider, 550035808407c15252e581e8abe1b820b9d311ee, x64
[email protected]

Entire function should be selected - we should not break on the dot.

aka, support copy-pasting the whole solution stack from a PBI dataset/dataflow

This would allow for a better diff between multi-statment stacks that might have ended up internally sorted differently in their local storage (e.g., due to a pbi dataflow save process autosort)

#135

Could you just briefly elaborate of the possibilities to use the Power Query CS Code plugin server from other tools. I'm thinking mostly about the Tabular Editor: https://github.com/otykier/TabularEditor

A proof-of-concept for smart type resolvers was done using Table.AddColumn. The goal is to implement a resolver for most Table/Record/List functions. This is to track the functions found in the docs.

Conversions

• Table.ToColumns
• Table.ToList
• Table.ToRecords
• Table.ToRows

We would like to eventually want to port the SDK from Visual Studio to this extension. Currently it's an internally tracked work item but have no dedicated dev cycles for the feature. As such it might take some time for the SDK to make an appearance, but it's certainly something we're aiming for (eventually).

Expected behavior
We added a way for the SDK to set symbols for the current connector project. However, they don't seem to work with Hover, Signature Help, or Completion Items.

Actual behavior
The connector symbols only work with Validation operations.

To Reproduce
Create an SDK project.
Build your connector.
Open your connector.query.pq file.

• d isn't throwing an error
• when d is enabled, it hides errors b would raise

The textmate scope is constant.numeric.integer.powerquery for the section before the .

Environment

powerquery.vscode-powerquery 0.1.19

To Reproduce

[<number>].<char><anything>

• It's . followed by a single text character that triggers it. The rest can be anything.
• the prefix is optional
• It occurs with values outside the hex literal range (non-a-f)

let   
    a = .z
in
    a

Expected Behavior

PBI returns an Token Comma Expected error

A proof-of-concept for smart type resolvers was done using Table.AddColumn. The goal is to implement a resolver for most Table/Record/List functions. This is to track the functions found in the docs.

Remaining

• Table.ReplaceKeys
• Table.CombineColumns
• Table.CombineColumnsToRecord
• Table.ExpandListColumn
• Table.ExpandRecordColumn
• Table.ExpandTableColumn
• Table.Group
• Table.Keys
• Table.ReplaceErrorValues
• Table.ReplaceKeys
• Table.ReplaceValue
• Table.TransformColumns
• Table.TransformColumnTypes
• Table.TransformRows

Completed

• Table.AddColumn

Not planned

• Table.AddFuzzyClusterColumn
• Table.AddIndexColumn
• Table.AddJoinColumn
• Table.AddKey
• Table.AggregateTableColumn
• Table.ConformToPageReader
• Table.FillDown
• Table.FillUp
• Table.FilterWithDataTable
• Table.Join
• Table.NestedJoin
• Table.ReplaceRelationshipIdentity
• Table.SplitColumn
• Table.Transpose

A proof-of-concept for smart type resolvers was done using Table.AddColumn. The goal is to implement a resolver for most Table/Record/List functions. This is to track the functions found in the docs.

Membership

• Table.Contains
• Table.ContainsAll
• Table.ContainsAny
• Table.Distinct
• Table.IsDistinct
• Table.PositionOf
• Table.PositionOfAny
• Table.RemoveMatchingRows
• Table.ReplaceMatchingRows

The parser supports the new catch keyword, but the grammar file needs to be updated to highlight it as a keyword.

It would be great to have an option for compressed code formatting.

Especially the line breaks for the value pairs for automatically generated code like in "Table.TransformColumnTypes" or "Table.RenameColumns" (after the Table.PromoteHeaders) make the code very lengthy.

Expected behavior
Excel.CurrentWorkbook should be recognized as a valid identifier.

Actual behavior
Using Excel.CurrentWorkbook raises the following error:
Cannot find the name 'Excel.CurrentWorkbook', did you mean 'Excel.Workbook'? powerquery(Error.UnknownIdentifier)

To Reproduce
Example script:

let
    foo = Excel.CurrentWorkbook()
in
    foo

Additional context
This function is documented at https://learn.microsoft.com/en-us/powerquery-m/excel-currentworkbook

Expected behavior
When passing a value of a specific type (e.g., number, text, record) to a function which expects a value with a type of any, it should be accepted without raising an Error.InvokeArgumentTypeMismatch error.

Actual behavior
An Error.InvokeArgumentTypeMismatch error is raised when a value is passed to a function which expects a value of type any unless the passed value is specifically cast to type any with ... as any. This occurs with both literals and variables.

Note: The function expects two values of type any. To illustrate the issue, I have explicitly cast one of the two to any.

To Reproduce
Insert the following query in Visual Studio Code running this Power Query extension, version 0.1.20:

let
    typeError = #table({"Column 1", "Column 2"}, {{1, 2}} as any)
in
    typeError

Additional context
I believe this bug was introduced with version 0.1.20, released July 14. I do not believe this bug existed when I first began using this extension in June.

Hello,
in the syntax definition of powerquery - scope selector for library functions is absent.
Parts of a function(List.Generate for example)
"List" part shows "entity.name" scope
".Generate" part shows "no scope selector"

There are numerous ways to fix that - from hardcoding function names ending to adding a proper regex into syntax file.

Please do something. It is important for me and lots of other PBI developers(I asked =))

Intellisense should not be restricted to M functions - for example, when I'm writing M code I frequently try to write a let expression and end up getting the Lines.ToText function instead, which is pretty frustrating. Things like let expressions, try/otherwise etc need to be supported properly too.

A proof-of-concept for smart type resolvers was done using Table.AddColumn. The goal is to implement a resolver for most Table/Record/List functions. This is to track the functions found in the docs.

Constructors

• Table.FromColumns
• Table.FromList
• Table.FromRecords
• Table.FromRows
• Table.FromValue

The following will not be implemented (at least for now):

• ItemExpression.From
• ItemExpression.Item
• RowExpression.Column
• RowExpression.From
• RowExpression.Row
• Table.FuzzyGroup
• Table.FuzzyJoin
• Table.FuzzyNestedJoin
• Table.Split
• Table.View
• Table.ViewFunction

case: Table.AddColumns(|value.....