Forum Discussion
Issue with returning a table.
Have you seen this post?
This and a few other posts of Chris' on the topic may offer some guidance.
Yes, that's what I meant by code block, thanks, that's much easier to read.
When I copied that code, Power Query complained about the syntax around the [postid=Centreid] section, so I had to mess around with that a bit.
Based on what I understand, I'm wondering if you'd be better off removing the last line so you have a working function that you could then use the 'Add Column' >> 'Invoke Custom Function' option.
So you end up with this...
let
Centreid = Centreids,
ids = List.Generate(() => 100000, each _ > 0, each _ - 1),
url = "https://auth.XXXXXXXX.com/api/v1/auth",
body = "{ ""user"": ""PCYCNSW-API"", ""password"": ""XXXXXXX""}",
tokenResponse = Json.Document(
Web.Contents(
url,
[Headers = [#"Content-Type" = "application/json"], Content = Text.ToBinary(body)]
)
),
data1 = tokenResponse[data],
token = "Bearer " & data1[token],
source = (Centreid as text) =>
Json.Document(
Web.Contents(
"https://office.XXXXXXXXX.com/api/enterprise/booking/center/rooms/summary",
[
Query = 10,
postid = Centreid,
Headers = [
#"x-api-key" = "XXXXXXXXXXXXXXXXXXXXXXXXXXX",
Authorization = token,
#"Content-Type" = "application/json"
]
]
)
)
in
source
The gif below, Query1 is your code that I pasted above, then add column and invoke the function based on the id column.
Mine errors obviously because the lack of valid url etc. but hopefully this will get you a little closer.
I had a type in my code so updated. Was just the line which had "Query=10"
Followed the gif and got this
Notepad copy of the error.
I think its closer
where the end of the url reads rooms/summary?postid=11169 it should read
rooms/summary/11169
And i really appreciate you helping me to trouble shoot this.
This is probably where my knowledge of accessing APIs via Power Query ends but I would've thought that something like the below would be more likely to return the format that you're expecting. (note the 'RelativePath...')
// Query2 let url = "https://auth.myxplor.com/api/v1/auth", body = "{ ""user"": ""xxxxxxxxx"", ""password"": ""xxxxxxxxx""}", tokenResponse = Json.Document( Web.Contents( url, [Headers = [#"Content-Type" = "application/json"], Content = Text.ToBinary(body)] ) ), data1 = tokenResponse[data], token = "Bearer " & data1[token], source = (Centreid as text) => Json.Document( Web.Contents( "https://office.myxplor.com/api/enterprise/booking/center/rooms/summary", [ RelativePath=Centreid, Headers = [ #"x-api-key" = "xxxxxxxxxxxxxxxxxxxxxxxxxxxx", Authorization = token, #"Content-Type" = "application/json" ] ] ) ) in sourceDoing this with the code you provided returns an error...
Unless of course that is expected because you haven't provided actual creds to access the API, which is kind of what I'd expect you'd do. 🤔
Edit: removed sensitive info.
Thank you KNP that has worked in the desktop version. You have saved me thank you.
Will need to test once published as I have found some weird errors but will come back as soon as I have tested.
Would you mind deleted the code from your last post I inadvertantly posted passwords etc.
They have been changed but would still like that info gone.Again thank you I have been trying for weeks to get this far.
KNPHave edited the post to exclude the sensitive info.
Not sure how to deal with the Syndicate_Admin post though as I don't know if anyone monitors that.
Hi KNP.
Its works in desktop however when published to the web and I try a refresh i get this error.
[Unable to combine data] Section1/Centreids/Expanded Query1 references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
Have you seen this post?
This and a few other posts of Chris' on the topic may offer some guidance.