CODEWITHSUNDEEP
powerbipowerquery
Jeremy Jones
Jeremy Jones

Reputation: 3

Power Bi web.contents Variables

Please excuse my lack of knowledge in explaining my problem as i have only just started learning Power Bi.

I am attempting to return data by using a dynamic variable within my source url.

Source = Json.Document(Web.Contents("https://api.****.com/jobs/{ID}/invoices", [Headers=[Authorization="Bearer "&GetToken()]]))

I have successfully returned the data i needed from multiple queries Blank Query 1 Query Names

However, i am trying to run a final query in which a job ID needs to be specified.

Source = Json.Document(Web.Contents("https://api.****.com/jobs/{ID}/invoices", [Headers=[Authorization="Bearer "&GetToken()]]))

With the bold item being the variable.

I have successfully returned values by hard coding the variable (seen below). Hard coded variable

However, i would like to make dynamic in that it will return the values for all the Job ID's witin the "jobs" table.

Job Id's

I don't know if what im asking is possible, or if my explanation is good enough, but any help would be greatly appreciated!

Upvotes: 0

Views: 2104

Answers (1)

Peter
Peter

Reputation: 12325

What you are looking for is a custom function.

  1. Make a function out of your above query by adding (ID) => in the first line and separating "ID" in your URL string.
(ID) =>
let
    Source = Json.Document(Web.Contents("https://api.****.com/jobs/{" & ID & "}/invoices", [Headers=[Authorization="Bearer "&GetToken()]]))
in
    Source

Of cause you can add all your other transformation steps too.

  1. Now take your JobIDs table and add a column by invoking a custom function, select the above function and take the ID parameter from your ID column.

  2. For every row you'll get a separate table and all that's left is simply expanding these tables into your query.

This will solve your problem.

Upvotes: 0

Related Questions