topic Re: Power Query code question - Please help a noob! in Power Query

Power Query code question - Please help a noob!

Anonymous — Tue, 07 Jan 2020 11:59:35 GMT

Hi guys,

I’m kinda new to accessing APIs in general and I’m not really a coder. Nevertheless, I feel I must be close with this. Can anyone tell me how to fix my Power Query? It must be something super simple…
API key redacted, of course.
The Power Query editor errors saying “Token Comma Expected” and highlighting the colon after “boards (ids” in the following query:

let Source = Web.Contents("https://api.monday.com/v2/", [Headers=[Authorization="Bearer myapikey", #"Method"="GET", #"Content-Type"="application/json"]] { boards (ids:[000000000, 000000000]) { items { name id column_values{ text } } } } ), in Source

Re: Power Query code question - Please help a noob!

az38 — Tue, 07 Jan 2020 12:11:17 GMT

Hi Anonymous

try to remove comma "," before in statement:

    ),

in
    Source

do not hesitate to give a kudo to useful posts and mark solutions as solution

Re: Power Query code question - Please help a noob!

Anonymous — Tue, 07 Jan 2020 12:31:46 GMT

Hi az38

Thank you so much for the fast reply. I tried your suggestion, but sadly it still highlights that comma and says Token Comma Expected.

😞

Re: Power Query code question - Please help a noob!

az38 — Tue, 07 Jan 2020 12:34:57 GMT

Anonymous

not sure but it seems the code should look like

let Source = Web.Contents("https://api.monday.com/v2/", [Headers=[Authorization="Bearer myapikey", #"Method"="GET", #"Content-Type"="application/json"]], { boards (ids:[000000000, 000000000]) { items { name, id, column_values{ text } } } } ) in Source

do not hesitate to give a kudo to useful posts and mark solutions as solution

Re: Power Query code question - Please help a noob!

Anonymous — Tue, 07 Jan 2020 12:42:32 GMT

az38 still no joy, I'm afraid. Same issue persists.

I have put this up on the monday.com dev forums too, but it's a ghost town over there.

Re: Power Query code question - Please help a noob!

Anonymous — Tue, 07 Jan 2020 12:43:33 GMT

(it's a GraphQL API, if that makes any difference)

Re: Power Query code question - Please help a noob!

az38 — Tue, 07 Jan 2020 12:52:30 GMT

Anonymous

it's difficult to try without access to source, but this should give you more understandable error

let Source = Web.Contents("https://api.monday.com/v2/", [Headers=[Authorization="Bearer myapikey", #"Method"="GET", #"Content-Type"="application/json"],Query="{ boards (ids:[000000000, 000000000]) { items { name id column_values{ text } } } }"]), #"Imported JSON" = Json.Document(Source,1252) in #"Imported JSON"

do not hesitate to give a kudo to useful posts and mark solutions as solution

Re: Power Query code question - Please help a noob!

Anonymous — Tue, 07 Jan 2020 13:04:41 GMT

az38 That made the editor happy with all the syntax. After converting the result to a table etc, i end up with a message saying "no query string was present".

I've created a trail account on monday.com for you, so feel free to use the api key: eyJhbGciOiJIUzI1NiJ9.eyJ0aWQiOjMwMTk0MjY4LCJ1aWQiOjEyMDIxMzI1LCJpYWQiOiIyMDIwLTAxLTA3IDEzOjAyOjM1IFVUQyIsInBlciI6Im1lOndyaXRlIn0.5ksF8oi27hneEcfAiTrj5MNVV-4SW-Fh3KxTUZMpBII

I just need to get to a point where I can put my own queries in. They have a useful "try it yourself" site for the API, which I've been getting on with well: https://monday.com/developers/v2/try-it-yourself

Re: Power Query code question - Please help a noob!

Jimmy801 — Tue, 07 Jan 2020 13:19:20 GMT

Hello Anonymous

the error is coming from here

i think you have to put all in quotes and double quotes and use some content record where you have to feed a JSON-Document into a Text.FromBinary

See here an example

let content = "{ ""method"": ""get"", ""params"": { ""SelectionCriteria"": {}, ""FieldNames"": [""Id"", ""Name""] } }", Source = Json.Document(Web.Contents("https://api-sandbox.direct.yandex.com/json/v5/campaigns", [Headers=[Authorization="Bearer AQAAAAANlKwBAAQQn4Wfgbxxxxxxxxxx"], Content=Text.ToBinary(content)])) in Source

Hope this helps

Jimmy

Re: Power Query code question - Please help a noob!

Anonymous — Tue, 07 Jan 2020 13:41:41 GMT

Jimmy801 , thank you but i'm too noob to know quite what you mean. i tried putting my url and API key into your example and the following for the query:

let content = "{ ""method"": ""get"", ""params"": { ""boards"" { ""name"" }, } }",

but ended up with a result saying "No query string was present"

az38 i've simplified the query to the following:

let Source = Web.Contents("https://api.monday.com/v2/", [Headers=[Authorization="Bearer myapikey", #"Method"="GET", #"Content-Type"="application/json"]] { boards { name } } ) in Source

and no more syntax errors in the editor! but, on clicking done, I get: "Expression.Error: The name 'boards' wasn't recognized. Make sure it's spelled correctly."

Re: Power Query code question - Please help a noob!

Jimmy801 — Tue, 07 Jan 2020 13:47:09 GMT

Hello Anonymous

did you implement it like in my example? Meaning inputing in a variable and then applying to web.Contents as parameter with content= Text.FromBinary?

Jimmy

Re: Power Query code question - Please help a noob!

Anonymous — Tue, 07 Jan 2020 14:00:22 GMT

Jimmy801 I'm pretty certain that I did not, seeing as I don't know what you mean! I know you're trying to be helpful, but I'm very new to Power Query/Power BI and no real experince with coding before that.

Re: Power Query code question - Please help a noob!

Anonymous — Wed, 08 Jan 2020 15:14:17 GMT

*cries all over his noob clothes*

Re: Power Query code question - Please help a noob!

Jimmy801 — Wed, 08 Jan 2020 15:40:19 GMT

Hello Anonymous

setting up an api with Power Query is quite complex and needs very specific knowlege of Power query but also of the api you are trying to query.

We here in the forum are doing this all voluntarily and you may have problems finding someone that is willing to spent so much time on a issue. Maybe it would be best to contact a professional to create such a query.

I've posted an example how this has to be done in your case.

Jimmy