Solved: Re: It is possible to execute one query 1600 times... - Page 2

jasonfordx · ‎06-14-2018

We are here with a certain need and without the knowledge necessary to solve. I would like to share the problem with you and seek help once again.

I need to execute an API 1600 times. This is because the following code needs to be runned:

let
    Source = Json.Document (Web.Contents ("https://api.educationcenter.com/1.0/book1", [Headers = [# "x-api-key" = "xxxxxxxxxxxxxxx"])
    # "Converted to Table" = Record.ToTable (Source)
in
    # "Converted to Table"

When executing this command and converting to Table, I have a line describing a book and several columns with various features and ratings of that book.

So I need to get the information from all 1600 books. And the difference for me to get book1 or book2 is to change the end of the API address. Ex:

book1:
https://api.educationcenter.com/1.0/book1

book2:
https://api.educationcenter.com/1.0/book2

I have a spreadsheet that has a column with the 1600 rows (book1, book2, book3, book4 ...)

Is it possible to create a query to be able to execute everything at once and fetch the 1600 rows of books in a single time instead of executing this query 1600 times and manipulating several Ctrl`s + C & Ctrl`s + V?

Michal_cwiok · ‎06-18-2018

Ok, so from the start 🙂

Delete everything and go to:

Open advanced editor and paste:

(row_num as text) =>

let
    Source = Json.Document (Web.Contents ("https://api.educationcenter.com/1.0/book"&row_num, [Headers = [#"x-api-key" = "xxxxxxxxxxxxxxx"]])),
    #"Converted to Table" = Record.ToTable (Source)
in
    #"Converted to Table"

Remember to change the API KEY. Rename it to Query1. You should see something like this:

Then go to blank query again , open advanced editor and paste:

let
    Source = Excel.Workbook(File.Contents("C:\Users\MichałĆwiok\Documents\List.xlsx"), null, true),
    Sheet2_Sheet = Source{[Item="Sheet2",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sheet2_Sheet,{{"Column1", type text}})[Column1],
    Download_all = List.Transform(#"Changed Type", each Query1(_))
in
    Download_all

Change the path of the file and it should be done.

Edit:

Query1:

Query2:

View solution in original post

jasonfordx · ‎06-18-2018

Dear Mr. Cwiok, good morning, how are you?

I read it and I will run the test right now.

Thank you for your time and support.

I will be right back 🙂

Have a wonderful week.

Kind Regards,

Michal_cwiok · ‎06-18-2018

Sure, I am online right now, so let me know once you have any results 🙂

Aron_Moore · ‎06-14-2018

Seems that API will force you to run the query for each book: "to get book1 or book2 is to change the end of the API address."

Power BI won't be able to change the way the API works.

Is there a different API call you could use to get the consolidated info?

jasonfordx · ‎06-14-2018

Dear Aron_Moore, good afternoon, how are you?

No, there is only this API. Since we are using this API to grab data for our research and that company which is providing is doing a "favor" to us, this is what we have. Are you sure Moore, I mean, there is no function to call via power query that will ready a column in my excel and run several times that?

Aron_Moore · ‎06-14-2018

You could try something like this thread, but basically it will run the query 1600 times not exactly what you wanted "fetch the 1600 rows of books in a single time instead of executing this query 1600 time "

jasonfordx · ‎06-14-2018

Dear Mr. Moore,

Reading your suggestion.. BRB

It is possible to execute one query 1600 times reading values from a spreadsheet?

Helpful resources

FabCon Global Hackathon

Power BI Monthly Update - October 2025

FabCon Atlanta 2026

How to Get Your Question Answered Quickly

Fabric Data Days starts November 4th!