Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Dear all,
I need some help with the following: when i call the API i'm using for my data, I need to use a parameter which is used by a other query.
So in this case: I have a table with all our locations in our warehouse: that's easy luckily:
= (Page as number)=>
let
Bron = Json.Document(Web.Contents("https://hag.picqer.com/api/v1/",
[RelativePath = "locations",
Query = [offset=Number.ToText(Page)]])),
This will give me a table with the locations. But in the next step, I would need to know what products are on that location.
In that case I will need a second call:
https://example.picqer.com/api/v1/locations/{locationID}/products
= (LocationID as number)=>
let
Bron = Json.Document(Web.Contents("https://example.picqer.com/api/v1/locations/",
[RelativePath=Number.ToText(LocationID),ProductsParameter="/products"])),
So, i will need Power Query to fill in the 'locationID' which i got from the previous call and then would like put /products after it.
This will give me the following:
= (LocationID as number)=>
let
Bron = Json.Document(Web.Contents("https://example.picqer.com/api/v1/locations/"&Number.ToText(LocationID),
[RelativePath="/products"])),
And the good thing is: that will work.
But when I will now publish this to the PowerBI Service, I can't refresh this automatically. Simply because the path is dynamic.
So somehow I would need to get the 'Number.ToText(LocationID)' somewhere else, but i don't know how I can insert more then one RelativePath or something.
Does anyone have any idea?
Solved! Go to Solution.
Instead of
= (LocationID as number)=>
let
Bron = Json.Document(Web.Contents("https://example.picqer.com/api/v1/locations/",
[RelativePath=Number.ToText(LocationID),ProductsParameter="/products"])),
Your code should look like
= (LocationID as number)=>
let
Bron = Json.Document(Web.Contents("https://example.picqer.com/api/v1/",
[RelativePath="locations/" & Number.ToText(LocationID) & "/products"])),
Instead of
= (LocationID as number)=>
let
Bron = Json.Document(Web.Contents("https://example.picqer.com/api/v1/locations/",
[RelativePath=Number.ToText(LocationID),ProductsParameter="/products"])),
Your code should look like
= (LocationID as number)=>
let
Bron = Json.Document(Web.Contents("https://example.picqer.com/api/v1/",
[RelativePath="locations/" & Number.ToText(LocationID) & "/products"])),