Forum Discussion

trwatts's avatar
trwatts
Helper I
4 years ago
Solved

Issue with returning a table.

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

 

 

 

 

 

Need Help

14 Replies

  • KNP's avatar
    KNP
    Super User
    4 years ago

    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's avatar
      trwatts
      Helper I
      4 years ago

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

       

       

      • KNP's avatar
        KNP
        Super User
        4 years ago

        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. 

         

  • trwatts's avatar
    trwatts
    Helper I
    4 years ago

    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's avatar
      KNP
      Super User
      4 years ago

      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.

       

       

      • trwatts's avatar
        trwatts
        Helper I
        4 years ago

        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.