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 se...
Need 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. 

       

       

       

      • KNP's avatar
        KNP
        Super User
        4 years ago

        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.

    • Syndicate_Admin's avatar
      Syndicate_Admin
      Administrator
      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. 

       

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

        Hi,

         

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