Forum Discussion

jpt1228's avatar
jpt1228
Responsive Resident
5 years ago

API with basic authorization token comma expected error

Hello, I am trying to figure out my error in this API query. I want to add the page= as a variable outside of the base URL because I cannot schedule a refresh in the Power BI service with a dynamic string. I think I have the query correct but i'm getting a Token Comma expected error.

 

 

(pagestart as text)=>

let
    Source = Json.Document(Web.Contents("https://harvest.greenhouse.io/v1/jobs?", [Headers=[AUTHORIZATION="Basic xxxxxx=",
    Query=[page="&pagestart&"]])),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "name", "requisition_id"})

in
    #"Expanded Column1"

 

 

 

Need Help
Show and Tell

4 Replies

Replies have been turned off for this discussion
  • sayaliredij's avatar
    sayaliredij
    Solution Sage
    5 years ago

    Could you try this ?

     

    (pagestart as text)=>

    let
    Source = Json.Document(Web.Contents("https://harvest.greenhouse.io/v1/jobs?", [Headers=[#"AUTHORIZATION"="Basic xxxxxx="]
    ,Query=[page="&pagestart&"]])),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "name", "requisition_id"})

    in
    #"Expanded Column1"

    • jpt1228's avatar
      jpt1228
      Responsive Resident
      5 years ago

      Hi sayaliredij that removed the error, but it doesn't appear to pass the function correctly to the base URL to change the page=1, then page=2 etc. The URL looks the same as the one that works for a single page but the %26 leading and trailing I believe are causing the error. Maybe I am not calling the function correctly?

       

  • sayaliredij's avatar
    sayaliredij
    Solution Sage
    5 years ago

    Could you try following M Query

     

    (pagestart as text)=>

    let
    Source = Json.Document(Web.Contents("https://harvest.greenhouse.io/v1/jobs?", [Query=[page="&pagestart&"]])),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "name", "requisition_id"})

    in
    #"Expanded Column1"

     

    this is without authentication. 

    when you will enter this you will be asked the power bi-based authentication and there you can enter. I think its not required to pass here

    • jpt1228's avatar
      jpt1228
      Responsive Resident
      5 years ago

      sayaliredij Thank you for responding. Unfortunately this is where I am stuck at. I need to hard code the authentication into the URL and change to anonymous web authentication and pass the page parameter outside of the Source = Json.Document(Web.Contents("https://harvest.greenhouse.io/v1/jobs?"

       

      When I schedule refresh in the service it will not allow dynamic URL's. Based on my research I need a static connection string for Power BI service to validate and then using a relative.path or other method to add the dynamic page= to string. 

       

      This is the source document I am referencing to "fake" the Power BI service to schedule refresh. "Fake Out" PBI blog