Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Invoking the masterful @ImkeF who is the master of M code.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.