simple for loop over a distinctList in Power Query for an API request

Question

I have to admit my confidence has taken a hit with this one.

I have a single list which i extrapolate from a table with the List.Distinct function.

I have an API I need to call but needs to take an ID which will come from the List.Distinct output.

so my question is how can I loop over my distinct_list and feed that as a parameter into my query in the api call.

in Pseudo code this would be:

distinct_table = unique(table[id])
empty_list = []
for each_id in distinct_table[List]:
    j = JsonDocument("www.apicall.com?peopleid=each_id")
    empty_list.append(j)

Code so far.

let
    distinct_list = List.Distinct(Projects[project_id])

    Source = Json.Document(Web.Contents("https://api.random.com",
                                                    [
                                                       RelativePath="/v3/logged-tasks",
                                                       Headers = [mytoken],
                                                       query = [
                                                       person_id = (each row from distinct_list)],
   #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)





in 
    Source

Alexis Olson Alexis Olson · Accepted Answer · 2020-05-27T14:41:27

I'd recommend making the API call its own function.

For example, this function I've named API returns text for a given number:

(n as number) as text =>
let
    Source = Text.FromBinary(Web.Contents("http://numbersapi.com/"&Text.From(n)&"/math"))
in
    Source

Now we can call this function in other queries. For example:

let
    distinct_list = {3,6,12},
    #"Converted to Table" = Table.FromList(distinct_list, Splitter.SplitByNothing()),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "API", each API([Column1]))
in
    #"Added Custom"

Here's what this looks like:

If you can transform instead of adding a column if you prefer:

Table.TransformColumns(#"Converted to Table", {{"Column1", each API(_), type text}})

simple for loop over a distinctList in Power Query for an API request

1 Answers