Credentials format

[stobot]

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.

[gsimardnet]

You can find some examples of credentials file in the Samples folder.

Here is one : #credentials.xml

[amks1]

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/<mysheetid>/pub to https://doc-14-ac-sheets.googleusercontent.com/pub/l5l039s6ni5uumqbsj9o11lmdc/qp624dp2l1ak84fqggq0ki1pj8/1611085670000/112456903451302169848/*/e@<mysheetid>.)

I've tried to add these urls in the credentials xml, but it doesn't work. The original url contains a ?gid=596863012&single=true&output=csv after the /pub and I'm not able to add this part in the XML file because it gives me a syntax error. How can I make this work?

Thanks a lot!

[gsimardnet]

Hi @amks1,

You should be able to define your credential by providing only the first part of the url like the following:

https://docs.google.com

Let me know if it still doesn't work.

Thank you

[amks1]

Hi @gsimardnet thanks for the reply, I entered both https://docs.google.com/ and https://doc-14-ac-sheets.googleusercontent.com/ in credentials and that error disappeared.

However now I'm getting another error: 5 arguments were passed to function which expects between 2 and 4.

There doesn't seem to be any mention of this string in your source code so I assume this is from within the PowerQuery module? My query works fine from the excel file. What could be the issue?

[gsimardnet]

You are probably right that this errors comes from PowerQuery itself. Maybe there is a mismatch in the versions between PowerQuery in Excel and PowerQuery in this library. Can you try to isolate the M function that cause this problem ?

[amks1]

@gsimardnet After some frustration I figured it out: it come from a step to add an index column. When you add an index column in excel Power Query, it creates a function having 5 arguments but the PQ version used in your app supports only 4. After I removed the 5th argument, it worked. I guess the PQ version needs to be updated? There may be more such functions.

[gsimardnet]

Thank you for taking time to test that.

Could you please tell me the name of the M function ? I would like to try to reproduce this problem.

[amks1]

Here's the function: = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type)

Works in excel but not in PQNet.

[gsimardnet]

This is definitely a recent update to that function.

Unfortunately, this project (PowerQueryNet) is based on the Power Query SDK and the latest version 1.0.0.25 doesn't support the 5th argument on Table.AddIndexColumn either. I suggest that you ask them for an update.

[amks1]

Thanks for your reply mate. Now this might be a stupid question, but is there any way to make this work in a Linux environment?

[gsimardnet]

Unfortunately no for the same reason that Power Query SDK only works in Windows