none
Using Data from one Web Content and use it for other RRS feed

  • Question

  • Hi,

    I Am trying to get data from one of the systems using REST API. 

    The first API returns the name of parameters in a table format.

    The second API returns the details from another REST API, which would use the parameter from first API.

    How Can I achieve this .

    Sample Code:

    let
        Source = Json.Document(Web.Contents("http://jenkins.com:8080/api/json?tree=jobs[name,url]")),
        jobs = Source[jobs],
        #"Converted to Table" = Table.FromList(jobs, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"name", "url"}, {"Column1.name", "Column1.url"}),
        #"Renamed Columns" = Table.RenameColumns(#"Expanded Column1",{{"Column1.name", "JobName"}, {"Column1.url", "JobUrl"}})

    <<Need to do something like below, which uses the JobName as parameter in previous line in another Rest API>>

    #"JobData" = Json.Document(Web.Contents("http://jenkins.com:8080/job/<<JobName>>/api/json?tree=changeSet[*[*]]"))


     in
        #"JobData"

    Thanks in advance.

    Regards

    Milind

    Wednesday, October 26, 2016 4:48 PM

Answers

  • After you've retrieved the results from your first queries in a table (where the parameters you want to use further on sit in the column "JobName"), you just add a column where you perform the 2nd webcall. In there you can reference the parameter by addressing the column name in square brackets: [JobName]. You have to escape and combine with & as follows:

    Json.Document(Web.Contents(http://jenkins.com:8080/job/<<"&[JobName]&">>/api/json?tree=changeSet[*[*]]"))

    So the full M-code of that step would look like this:

    #"JobData" = Table.AddColumn(#"Renamed Columns", "Custom", each Json.Document(Web.Contents(http://jenkins.com:8080/job/<<"&[JobName]&">>/api/json?tree=changeSet[*[*]]")))

    This will produce a web-call for every row of your table taking in the value of the current row from column "JobName" as a parameter (thanks to the "each" that transforms every command in the "Table.AddColumns"-command into a function).


    Imke Feldmann TheBIccountant.com

    Wednesday, October 26, 2016 6:53 PM
    Moderator

All replies

  • After you've retrieved the results from your first queries in a table (where the parameters you want to use further on sit in the column "JobName"), you just add a column where you perform the 2nd webcall. In there you can reference the parameter by addressing the column name in square brackets: [JobName]. You have to escape and combine with & as follows:

    Json.Document(Web.Contents(http://jenkins.com:8080/job/<<"&[JobName]&">>/api/json?tree=changeSet[*[*]]"))

    So the full M-code of that step would look like this:

    #"JobData" = Table.AddColumn(#"Renamed Columns", "Custom", each Json.Document(Web.Contents(http://jenkins.com:8080/job/<<"&[JobName]&">>/api/json?tree=changeSet[*[*]]")))

    This will produce a web-call for every row of your table taking in the value of the current row from column "JobName" as a parameter (thanks to the "each" that transforms every command in the "Table.AddColumns"-command into a function).


    Imke Feldmann TheBIccountant.com

    Wednesday, October 26, 2016 6:53 PM
    Moderator
  • Thanks Imke for the response.... It really helped...

    I am new to Power Query and in process of learning....

    Regards

    Milind

    Thursday, October 27, 2016 9:41 AM
  • Glad to hear that!

    You can find more learning resources for Power Query here: http://www.thebiccountant.com/learning-resources/


    Imke Feldmann TheBIccountant.com

    Thursday, October 27, 2016 10:02 AM
    Moderator