Issue with returning a table.

[trwatts]

Hi Everyone 

New to M query and having issues get a table of records. 

I am calling through API.

Bearer Token needs to be called.

The URL has a dynamic ID at the end of the url which returns a set of values. 

When i run the query with a static centre id at the end of the URL i have no issues at all.

I need to create a query that dynamically runs through the availble ids to pull all of the data. 

I have come to a few different issues. 
Line 14. From my understanding this will try to match the key of "Centerid" to the "source([Centreid]). The two table do not have a common centreid field in the source the key id room_id which is an unrealted number.

When i run it I get the below.

I then tried replacing "Centerid" with "ids" that has a coressponding number to each row in the source table. 

Once run i get the following error. 

I have tried countless other things to try get the result but am now hitting my head up against a wall. 

 Completely Stuck. 
Any help would be fantastic. 

1 let
2 Centreid = Centreids,
3 ids = List.Generate(() => 100000, each _ > 0, each _ -1),
4 url = "https://auth.XXXXXXXX.com/api/v1/auth",
5 body = "{ ""user"": ""XXXXXXXX"",""password"": ""XXXXXXX""}",
6 tokenResponse = Json.Document(Web.Contents(url,[Headers = [#"Content-Type"="application/json"], Content = Text.ToBinary(body) ] )),
7 data1 = tokenResponse[data],
8 token = "Bearer " & data1[token],
9 source = (Centreid as text) => Json.Document(Web.Contents("https://office.XXXXXXXXX.com/api/enterprise/booking/center/rooms/summary",

10 [Query= [postid=Centreid],
11 Headers=[#"x-api-key"="XXXXXXXXXXXXXXXXXXXXXXXXXXX",
12 Authorization=token,
13 #"Content-Type"="application/json"]])),
14 Output= Table.AddColumn(Centreid,"Output", each source([Centreid]))
15 in
16 Output

[trwatts]

@KNP 

Is the below what you mean by code block? 

The Centreids references a separate list. 

 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"]])),
    Output= Table.AddColumn(Centreid,"Output", each source([Centreid]))
 in
   Output

[KNP]

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.

[Syndicate_Admin]

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. 

let
  Centreid = Centreids,
  ids = List.Generate(() => 100000, each _ > 0, each _ - 1),
  url = "https://auth.xxxxxxxxxxx.com/api/v1/auth",
  body = "{ ""user"": ""xxxxxx"",  ""password"": ""xxxxxx""}",
  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.xxxxxxxx.com/api/enterprise/booking/center/rooms/summary",
        [
          Query =[
          postid = Centreid],
          Headers = [
            #"x-api-key"    = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",
            Authorization   = token,
            #"Content-Type" = "application/json"
          ]
        ]
      )
    )
in
  source

[trwatts]

Hi,

Can you please delete the code in yor post. It includes passwords which I accidentally included.

[trwatts]

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. 

[KNP]

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
  source

Doing 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.

[trwatts]

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.

[v-jingzhang]

Hi @trwatts 

I found a similar question, see if the solution there works. Unable to combine data...AutoRemovedColumns1...Please rebuild this data combination (microsoft.com)

Best Regards,
Community Support Team _ Jing

[KNP · Accepted solution]

Have you seen this post?

https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power...

This and a few other posts of Chris' on the topic may offer some guidance.

[trwatts]

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.
@KNP 

[KNP]

Have 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.

[KNP]

Shouldn't the line...

 Output= Table.AddColumn(Centreid,"Output", each source([Centreid]))

Say...

 Output= Table.AddColumn(source,"Output", each source([Centreid]))

As in, your previous step.

I may be missing something.

[trwatts]

Just tried it and unlike the other times i was thinking abot it for a while then produced this error. 

[KNP]

Sorry, it's really hard to read the code you posted.

If you could post your complete code, inside a code block with some more detail about other queries etc. it may be easier to help.