gsimardnet / powerquerynet Goto Github PK

I have a problem with running basic HelloWorld example:

var q = new Query { Formula = "let hw = \"Hello World\" in hw" };
var pq = new PowerQueryCommand();
var result = pq.Execute(q);

Exception message from result gives me following info:

Method not found: 'System.Threading.Tasks.Task`1<Microsoft.Mashup.Tools.QueryExecutionResults> Microsoft.Mashup.Tools.QueryExecutor.CreateExecution(Microsoft.Data.Mashup.MashupConnectionStringBuilder, Microsoft.Mashup.Tools.CredentialStore, Boolean)'.

I suspect that this is related due to PowerQuerySdk.vsix 1.0.0.16 dependency.

I cannot find v1.0.016 anymore to test against this version, so i'm using current version 1.0.0.25

Is this known error?

When trying to load a CSV file in a .pq file via pqnet.exe, I get the following:

Credentials are required to connect to the File source.

I notice that pqnet has a credentials parameter, and in the samples download I see a credentials file that looks like this:

<Credentials xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <Credential xsi:type="CredentialSQL"> <SQL>.\sql2016;AdventureWorks2012</SQL> </Credential> <Credential xsi:type="CredentialFile"> <Path>Z:\TMP\eConnect\Archive\PM_Simplex_201702061100.xml</Path> </Credential> <Credential xsi:type="CredentialFile"> <Path>Z:\Desktop\PowerQueryApp\MyExcel\List1.xls</Path> </Credential> </Credentials>

Where can I find documentation on the credential format? What are the credentials that are needed for accessing a CSV file? Will I need a separate credential for each CSV file I'm accessing?

I didn't see any other issues around this, so maybe I am missing something obvious.

Thanks in advance.

In my opinion, the problem is that in the file " extension.vsixmanifest". It clearly indicates the versions of Visual Studio that are supported. However, I fixed these inconsistencies and everything works on VS2019.
Just in case, I attach logs from the installer.

log from installer
01.06.2021 17:17:50 - Microsoft VSIX Installer
01.06.2021 17:17:50 - -------------------------------------------
01.06.2021 17:17:50 - vsixinstaller.exe version:
01.06.2021 17:17:50 - 16.9.1050
01.06.2021 17:17:50 - -------------------------------------------
01.06.2021 17:17:50 - Command line parameters:
01.06.2021 17:17:50 - C:\Program Files (x86)\Microsoft Visual Studio\Installer\resources\app\ServiceHub\Services\Microsoft.VisualStudio.Setup.Service\VSIXInstaller.exe,J:\Code\PQ\PowerQuerySdk.vsix
01.06.2021 17:17:50 - -------------------------------------------
01.06.2021 17:17:50 - Microsoft VSIX Installer
01.06.2021 17:17:50 - -------------------------------------------
01.06.2021 17:17:50 - Initializing Install...
01.06.2021 17:17:50 - Подробности расширения...
01.06.2021 17:17:50 - Identifier : PowerQuerySDK.Microsoft.30831070-f420-4649-a031-6f679996b182
01.06.2021 17:17:50 - Name : Power Query SDK
01.06.2021 17:17:50 - Author : Microsoft
01.06.2021 17:17:50 - Version : 1.0.0.16
01.06.2021 17:17:50 - Description : A Power Query language service for Visual Studio
01.06.2021 17:17:50 - Locale : en-US
01.06.2021 17:17:50 - MoreInfoURL :
01.06.2021 17:17:50 - InstalledByMSI : False
01.06.2021 17:17:50 - SupportedFrameworkVersionRange : [4.5,)
01.06.2021 17:17:50 -
01.06.2021 17:17:50 - SignatureState : InvalidSignature
01.06.2021 17:17:50 - SignedBy : Microsoft Corporation
01.06.2021 17:17:50 - Certificate Info :
01.06.2021 17:17:50 - -------------------------------------------------------
01.06.2021 17:17:50 - [Subject] : CN=Microsoft Corporation, OU=OPC, O=Microsoft Corporation, L=Redmond, S=Washington, C=US
01.06.2021 17:17:50 - [Issuer] : CN=Microsoft Code Signing PCA 2010, O=Microsoft Corporation, L=Redmond, S=Washington, C=US
01.06.2021 17:17:50 - [Serial Number] : 33000001FC5DB8191027A666C10000000001FC
01.06.2021 17:17:50 - [Not Before] : 05.10.2017 21:26:08
01.06.2021 17:17:50 - [Not After] : 05.10.2018 21:26:08
01.06.2021 17:17:50 - [Thumbprint] : 5E940B33DEAE5945F506ADAB946A997867A40FEF
01.06.2021 17:17:50 -
01.06.2021 17:17:50 - Поддерживаемые продукты :
01.06.2021 17:17:50 - Microsoft.VisualStudio.Community
01.06.2021 17:17:50 - Version : [14.0,15.0)
01.06.2021 17:17:50 - Microsoft.VisualStudio.Pro
01.06.2021 17:17:50 - Version : [14.0,15.0)
01.06.2021 17:17:50 - Microsoft.VisualStudio.Enterprise
01.06.2021 17:17:50 - Version : [14.0,15.0)
01.06.2021 17:17:50 -
01.06.2021 17:17:50 - Ссылки :
01.06.2021 17:17:50 - Предварительные требования :
01.06.2021 17:17:50 - -------------------------------------------------------
01.06.2021 17:17:50 - Identifier : Microsoft.VisualStudio.Component.CoreEditor
01.06.2021 17:17:50 - Name : Visual Studio core editor
01.06.2021 17:17:50 - Version : [15.0,16.0)
01.06.2021 17:17:50 -
01.06.2021 17:17:50 - Состав подписи...
01.06.2021 17:17:50 - Extension is signed with an invalid signature.
01.06.2021 17:17:50 -
01.06.2021 17:17:50 - Поиск применимых продуктов...
01.06.2021 17:17:50 - Найден установленный продукт - Глобальное расположение
01.06.2021 17:17:50 - Найден установленный продукт - Visual Studio Community 2019
01.06.2021 17:17:50 - VSIXInstaller.NoApplicableSKUsException: Это расширение не может быть установлено ни для одного из установленных продуктов.
в VSIXInstaller.ExtensionService.GetInstallableDataImpl(IInstallableExtension extension, String extensionPackParentName, Boolean isRepairSupported, IStateData stateData, IEnumerable1& skuData) в VSIXInstaller.ExtensionService.GetInstallableData(String vsixPath, String extensionPackParentName, Boolean isRepairSupported, IStateData stateData, IEnumerable1& skuData)
в VSIXInstaller.ExtensionPackService.IsExtensionPack(IStateData stateData, Boolean isRepairSupported)
в VSIXInstaller.ExtensionPackService.ExpandExtensionPackToInstall(IStateData stateData, Boolean isRepairSupported)
в VSIXInstaller.App.Initialize(Boolean isRepairSupported)
в VSIXInstaller.App.Initialize()
в System.Threading.Tasks.Task`1.InnerInvoke()
в System.Threading.Tasks.Task.Execute()
--- Конец трассировка стека из предыдущего расположения, где возникло исключение ---
в Microsoft.VisualStudio.Telemetry.WindowsErrorReporting.WatsonReport.GetClrWatsonExceptionInfo(Exception exceptionObject)

Hi Guillaume,

PowerQueryNet handles merging queries?. Up to now, I have run one single query with no merging queries inside. Would it be possible?

Thanks.

Hi,
I am just wondering if there is an update coming compatible with newer SDK version such as 10.0.0.20. I was trying to install the last version of PowerQueryNet with last SDK version (i.e.10.0.0.20) and it didn't work. Finally, I tried it with controlled SDK version (i.e. 10.0.0.16) and it worked fine.
Is there any chance that PowerQueryNet would be mainly independent of the SDK version?

Thanks in advance and congratulations for the project.
Jordi

Hi @gsimardnet, thanks for building this wonderful tool.

I'm facing some trouble with setting the credentials file. I have a query in an excel file that pulls data from a google sheet that's published on the web. I'm trying to run pqnet on command line, and what I get is
Credentials are required to connect to the Web source. (Source: from https://docs.google.com/spreadsheets/d/e/{my_url}/pub to https://doc-14-ac-sheets.googleusercontent.com/pub/l5l039s6ni5uumqbsj9o11lmdc/qp624dp2l1ak84fqggq0ki1pj8/1611085670000/112456903451302169848/*/e@.)

I've tried to add these urls in the credentials xml, but it doesn't work. Is there a way I can connect to Google Sheets?

Thanks a lot!

I just tried pqnet helloworld.pq helloquery but got this error: CreateMashupConnectionInfo has failed.
How can I find out what's wrong?

I have a query that reads records from a CSV file, performs some transformations, and outputs to another CSV file. The output file has about 35,000 rows.

If I run it with CSV output parameters, it takes 8.5 minutes:

pqnet query.pq -c credentials.xml -o csv -f output.csv

If I run it with no output parameters, it shows up in the built-in GUI and takes less than 5 seconds. I can select all and copy all 35,000 rows from that GUI and paste it into a text editor in a total of 10 seconds, so it's not due to lazy evaluation.

pqnet query.pq -c credentials.xml

Why does the formatting and output to CSV take so long? Am I doing something wrong?

Thanks!

I am running a query within an excel file. I run the query on one computer, and it gives me a wrong output.
I copy the exact same file to another computer, run it, and it gives me the correct output.

I installed PowerQueryNet on both computers from the same installation files.

I'm stumped, what could be the reason for this?

I am a bit lost searching for information about credentials management. I have understood and experimented the several exemples that are part of the project. In fact I have been able to connect to SQL database through windows account and also through an SQL account. Files and Folders seems to work also well, assuming they are taking my current windows credentials to log into the remote resources that are under the same Domain.

My doubts started using web resources. I have tried a couple of URLs I use in my powerqueries in Excel which are in fact Rest API services. In both cases I use usename/password to authenticate the query but there is no way to make it run in PQNet. I have tried Windows Credential manager, also tags for username and password in the Credentials XML file (as for SQL or OData), but with no success.

I feel I am not doing it correctly but I don't know what else to try.

Any information that can drive me in the use of PQNet for Rest API resources?
Thanks
Jordi

Hi,
I am facing issue while trying to download the master is build it in m local machine.

1. the dlls under project Engine/lib -Data and other mashup libraries are not available and causing issue.

I am using VS 2019. I am not sure If I am missing some installations.
Plan for usage:I am working on a automation tool where .net libraries can be used. When we tr to automate large excel files the operation usually fails while reading it as a data table. So we in turn import the excel data to sql server to perform normal CRUD operation. But after seeing power query features, I am confident to use M query to achieve CRUD operations on large excel data without the need of external sql server.Let me now your thoughts. Trying to avoid using VBA macro

Apologies for adding in issues

First, I really appreciate you building this for everyone. I'm interested in taking this code and using it in an integration but the architecture of the project is a bit confusing. Can you elaborate a bit on why you've set this up as a remoting server and client? What about the Power Query libraries makes that a necessity?

Hi @gsimardnet, would it be possible to connect to an azure sql server instance or does the sql server have to be installed on the same machine?

I have attempted to update the credentials file.

Thanks in advance.

Hello,

I am receiving the error below while installing on Windows 7 64bit

error writing to file: PowerQueryNet.Client.dll. Verify that you have access to that directory

Hi

I have been trying pqnet with the several options it provides. I have experienced some problems trying to execute a query hosted in an Excel file with the message "CreateMashupConnectionInfo has failed.". I could execute a query in a pbix file with no problems but I couldn't for an Excel file.

Even, I have created a simple Excel file named test.xlsx with Hello World M query inside and named as Query1. When I just tried to execute it with the following command line:

pqnet test.xlsx Query1

I got the same error as with other Excel files.

Can you provide some insights about it?

Thanks
Jordi

Hi, I was trying this tool but when it came to give credentials to folder, it failed with the following message..

here is my code :

Here is the Mashup Script:

What should I do about it?? Or, do you know how to make Credentials from an XML file... means the format of the XML document..?

I have a question about providing credentials to the Azure Blob Storage.
I would like to read csv from storage. This is a simple powerquery:

let
	Source = AzureStorage.Blobs("my_account"),
	mycontainervar = Source{[Name="my_container"]}[Data]
in
	mycontainervar

I run it with (txtMashup.Text is just above query);

var q = new Query { Formula = txtMashup.Text };
var pq = new PowerQueryCommand();
var result = pq.Execute(q);

As expected, result.ExceptionMessage is:
Credentials are required to connect to the AzureBlobs source. (Source at https://myaccount.blob.core.windows.net/my_container.)

Can anyone point me to the right direction how to provide those credentials (url, account name, key1)?

I also pass credential to PowerQueryCommand, but with no luck:

CredentialWeb web_credentials = new CredentialWeb();
web_credentials.Username = "account_name";
web_credentials.Password = "account_key_1";
web_credentials.Url = "https://myaccount.blob.core.windows.net/mycontainer";
pq.Credentials.Add(web_credentials);
var result = pq.Execute(q);

I've attached a screenshot of the returned CSV file to show the issue:

These are supposed to be a single column having URLs pointing to a geo coordinate. After looking through your code I understood the issue: when there are commas within quotes, it does not work and the entire string breaks.

 public static string ToDelimitedFile(this DataTable dataTable, char delimiter, bool inQuote)
        {
            StringBuilder content = new StringBuilder();

            string lastColumn = dataTable.Columns[dataTable.Columns.Count - 1].ColumnName;
            foreach (DataColumn dc in dataTable.Columns)
            {
                content.Append(dc.ColumnName);
                if (dc.ColumnName != lastColumn)
                    content.Append(delimiter);
            }

            int i = 0;
            foreach (DataRow dr in dataTable.Rows)
            {
                content.Append(Environment.NewLine);

                foreach (DataColumn dc in dataTable.Columns)
                {
                    if (inQuote)
                        content.Append('"' + dr[dc.ColumnName].ToString() + '"');
                    else
                        content.Append(dr[dc.ColumnName].ToString());

                    if (dc.ColumnName != lastColumn)
                        content.Append(delimiter);
                }
            }

            return content.ToString();
        }

Hi,

I am using pqnet for a while now and it is an excellent tool to help me automate some ETL processes. However, sometimes I have experienced some issues with some queries with an error "We're sorry, an error occurred during evaluation" and I have not been able to figure out why as the same query was working in PBI or Excel. By changing my queries a bit I was able to resolve most of them with those issues but honestly didn't know why.

Recently I am optimizing some of my ETL queries that retrieve information from SQL Server and I realized that there is something in common for all the queries with the above mentioned problem.

The optimization process mainly aims to ensuring Native Queries instead of loading the data into the in-memory DB. In this context I could observe that any query that retrieves data in Native Query mode ends up in the above error.

For example, this query works perfectly in PBI and Excel but not in pqnet:
let
Source = Sql.Databases("(local)\DWH"),
Test1 = Source{[Name="Test1"]}[Data],
dbo_Test = Test1{[Schema="dbo",Item="Test"]}[Data]
in
dbo_Test

Just buffering the native query result makes the query to work also in pqnet
let
Source = Sql.Databases("(local)\DWH"),
Test1 = Source{[Name="Test1"]}[Data],
dbo_Test = Test1{[Schema="dbo",Item="Test"]}[Data]
in
Table.Buffer(dbo_Test)

Table.Buffer is just an example of how to fix it, but any step that causes data to be loaded into memory makes the query work.

With this observation in mind, I decided to search the internet about the bug and found that it was present in some versions of Excel and PBI a long time ago, but it was also resolved long ago. My suspicion is that SDK version 1.0.0.16 has this bug and I guess it was resolved in newer versions of the SDK.

Do you have any plans to update the SDK to the latest version? The latest version is 1.0.0.25, released on 8/18/2019, so it seems pretty stable since then.

Thank you, especially for the great tool you have developed.
Jordi

Sorry if I'm overlooking something, but how do I prepare a file for credentials for use of the CLI? It appears I need to build an XML file - not sure what that looks like.

Hello

Is this project still active?

I have installed all of the prerequisites yet still can't see to get the Power Query App to run.

Any suggestions?

Thank you.

This query

let
	List = {1..20},
	Result = List.Split(List, 4)
in Result

fails with The name 'List.Split' wasn't recognized. Make sure it's spelled correctly.
List.Split is a valid built-in function and I can only guess that it is not recognized because of the outdated SDK.

• New functions, like List.Split, above.
• The language has evolved a little over time (e.g. the ?? coalesce operator).
• The signatures of certain functions have changed. For example:
  • Table.Skip no longer requires a second parameter (it now defaults to skipping one row).
  • Table.AddColumn can now take 5 rather than 4 parameters

Such inconsistencies make what should be simple lift & shift of code from Power BI to PQnet a very difficult exercise.

I understand there was a reason to freeze the version but that was several years ago, so can you please update PQnet's SDK compatibility?

Hello,

Thank you for this project! It proves that an idea of mine is feasible and inspires to pursue it.

Could you please explain how does it work internally? Especially the Power Query execution part. I've been trying to figure that out by following the code but lack of C# and .NET familiarity limits my research to guesswork.

From what I've gathered, the magic happens here: Commands.cs, namely in the instance of QueryExecutor class. Where does that class come from? Is there any documentation for it?

It seems that QueryExecutor is imported from one of the Microsoft.Mashup.* libraries in the beginning of the file, but I was unable to find any documentation about those.

Please expand/correct my findings and point me to the relevant documentation.

Thank you!

How would one go about using this with a custom pqx or mez extension?

Trailing comments in a PQ document can cause an error (I tested by adding comments to one of a few pq files in a folder).
If you have

let
    ...
in Result
//My Trailing Comment

you get Microsoft.Data.Mashup.MashupExpressionException (0x80004005): Token Semicolon expected. Start position: (209, 1). End position (209, 7).

Interestingly, one can avoid the error by writing a semicolon after the final in statement:
let ... in Result; //My Trailing Comment does not cause an error.

When I launch any project from this repo, even PowerQueryHelloWorld, I get error
"No endpoint listening for net.pipe://localhost/PowerQueryNet/".

What may be the problem?

I just downloaded the repo, opened PowerQueryNet.Samples.sln and launched Helloworld.

I just installed pqnet, saved let Source = Text.Proper("Hello, World") in Source as "helloworld.pq" and ran pqnet helloworld.pq and received Query name must be defined.

The error message is clear enough, but how do I define a query name? What step did I miss?

When executing multiple pq files within the same directory and a syntax error occurs PQnet will helpfully pass along the error message. If the pq file is too long the information is available in the Event Viewer, but it seems that the error message refers to a single document created from multiple .pq files. For example, an error I receive is Token Identifier expected. Start position: (2752, 27). End position (2752, 28). while my longest .pq file is less than 2752 lines.

If it's impossible/difficult to return the line number of the document containing the error and its file name, could PQnet at least (optionally?) make the combined document it creates available somewhere for debugging?

I was eventually able to find the error by subtracting line numbers and guessing at the sequence files were combined, but it was very slow.

I didn't find an instruction on how to install and use it on mac os.

I found this seems to happen whenever a hyphen is in the file name.

'let A =1 in A' | Out-File My-File.pq
pqnet.exe My-File.pq
#Token Equal expected. Start position: (4, 10). End position (4,11).

The start/end position numbers in the error message depend on the position of the hyphen in the name. pqnet.exe 'My-File.pq' doesn't help.

A query with fairly complex logic that cleans some atrociously formatted Excel files and writes the result to SQL Server works for up to a few dozen files but times out when I try to process hundreds at a time.
Here's the error:

System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
   at System.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync()
   at System.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket()
   at System.Data.SqlClient.TdsParserStateObject.TryPrepareBuffer()
   at System.Data.SqlClient.TdsParserStateObject.TryReadByte(Byte& value)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlBulkCopy.RunParser(BulkCopySimpleResultSet bulkCopyHandler)
   at System.Data.SqlClient.SqlBulkCopy.CopyBatchesAsyncContinuedOnSuccess(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource`1 source)
   at System.Data.SqlClient.SqlBulkCopy.CopyBatchesAsyncContinued(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource`1 source)
   at System.Data.SqlClient.SqlBulkCopy.CopyBatchesAsync(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource`1 source)
   at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestContinuedAsync(BulkCopySimpleResultSet internalResults, CancellationToken cts, TaskCompletionSource`1 source)
   at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestAsync(CancellationToken cts, TaskCompletionSource`1 source)
   at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalAsync(CancellationToken ctoken)
   at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServerAsync(Int32 columnCount, CancellationToken ctoken)
   at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table, DataRowState rowState)
   at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table)
   at PowerQueryNet.Service.PowerQueryService.OutputToSQL(PowerQueryCommand powerQueryCommand, DataTable dataTable) in Z:\GitHub\PowerQueryNet\Service\PowerQueryService.cs:line 137
   at PowerQueryNet.Service.PowerQueryService.Execute(PowerQueryCommand powerQueryCommand) in Z:\GitHub\PowerQueryNet\Service\PowerQueryService.cs:line 296
ClientConnectionId:6b4b230b-be66-4968-adb9-f5a8bf3a0e57
Error Number:-2,State:0,Class:11

Can you create an option to adjust the timeout to accommodate slow processes?

Hi, thanks for an excellent job, I am trying to connect to a SAP HANA cube. is there a way to connect sending the credentials (sending the url from sap hana server and the windows credentials)? thanks in advance.