Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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"
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
Proud to be a Super User!
@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
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"
Proud to be a Super User!
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |