M language and Nested API calls

[Floater]

Hi

I am populating a table based on a API call code below 

let
    Source = Json.Document(Web.Contents("https://xxxxxxxxxxxxxxxx/scenarios.json", [Headers=[Authorization="Token token=""xxxxxxxxxxxxxxxxxxxxxxx"""]])),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "title", "scenario_type", "date_started", "date_finished", "responses", "full_csv_url", "recipients", "notes"}, {"Column1.id", "Column1.title", "Column1.scenario_type", "Column1.date_started", "Column1.date_finished", "Column1.responses", "Column1.full_csv_url", "Column1.recipients", "Column1.notes"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Column1",{{"Column1.date_started", Order.Ascending}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Sorted Rows",{{"Column1.date_started", type datetime}}),
    #"Sorted Rows1" = Table.Sort(#"Changed Type",{{"Column1.date_started", Order.Ascending}}),
    #"Renamed Columns" = Table.RenameColumns(#"Sorted Rows1",{{"Column1.id", "ID"}, {"Column1.title", "Title"}, {"Column1.scenario_type", "Scenario_Type"}, {"Column1.date_started", "Date_Started"}, {"Column1.date_finished", "Date_Finished"}, {"Column1.responses", "Responses"}, {"Column1.full_csv_url", "Scenario_API_URL"}, {"Column1.recipients", "Recipients"}, {"Column1.notes", "Notes"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Scenario_API_URL", type text}, {"Date_Finished", type datetime}, {"Scenario_Type", type text}, {"Title", type text}, {"Recipients", Int64.Type}})
in
    #"Changed Type1"

the output of the table will have 200-300 rows (scenarios) and a column called Scenario_API_URL stores the string which is URL address for the nested API call which will return details about the selected scenario.

I wrote a function called scenariograbber

let scenariograbber=(url)=>
    let
        Source = Csv.Document(Web.Contents(url, [Headers=[Authorization="Token token=""xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"""]]))
    in
        Source
in 
scenariograbber

which it will return the result if I invoke the function manually and enter one of the URLs retruned by the first result set.

I want to combaine all the sub API calles as one table!

would appreciate your help

Thanks

[ImkeF]

Hi @Floater , 

you should execute your first query and then create a new column where you call the function (for each row/URL). That way you get it all in one table.

But if you want to refresh it in the service, your function has to be modified a bit: https://www.thebiccountant.com/2018/03/22/web-scraping-2-scrape-multiple-pages-power-bi-power-query/

The video in there also shows how to add a function to a table like yours.

[Greg_Deckler]

Invoking the masterful @ImkeF who is the master of M code.