Looping results of Query in another Query RRS feed

  • Question

  • I am working with a few REST APIs and have created a workbook that refreshes each API query and creates the tables when opened. One of the APIs requires an ID number from a list retrieved from another API. The Query to get just a list of the IDs is called "ReqID"

    What i want to do is something like:

    For Each vID in ReqID, create a concatenated table of data for all vID.

    Is this possible and if so, can anyone give me directions on how to start?

    Tuesday, March 21, 2017 1:09 PM


All replies

  • Yes, it is possible through the creation of a function in Power Query.

    The simplest way to do this is to create a query that returns results for one ID number. If that works, go to the advanced editor, and add the following line before everything else:

    (ID) =>

    Also, in the script replace the static ID number you used to build the query with by ID. Now you created a Power Query function with and ID parameter. The name of the function is the name you gave to the query in the first place; let's say this is 'fID'

    In your query ReqID, add a custom column and call the function fID using fID() using the appropriate column to provide the ID parameter. The result is a column containing a table for each row. Expand this column to retrieve the rows from each table as one large table.

    Note that after creating the function, you cannot go back to the GUI editor to make changes. The best way to address this is to keep the original query and create the function from a copy of it.

    Tuesday, March 21, 2017 2:08 PM
  • "Also, in the script replace the static ID number you used to build the query with by ID."

    The ID is part of a URL string enclosed in "".

    when i try to replace the number with ID i get no results because it is using the physical letters ID instead of the numbers they represent.

    I also tried "https://test.test/example/" & ID & "/request" but get an error. "We cannot apply operator & to types Text and Number." do i need to force the number to be a text string somehow? 

    Tuesday, March 21, 2017 2:35 PM
  • Hi olivetwist. For the operator error, try this:

    "https://test.test/example/" & Text.From(ID) & "/request"


    Wednesday, March 29, 2017 7:19 PM