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

[jasonfordx]

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]

 Hi @jasonfordx,

there is a way to loop queries. I have written about it here:

As you your example.

You first need to create a custom function that gets the records. I will look like this:

(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"

Then generate a list with numbers from 1 to 1600 using List.Numbers(1,10) and use List.Transform applying the function.

So something like this:

let



Numbers = List.Numbers(1,1600),

Final = List.Transform(Numbers, each Query1(_))

in

Final

This should work!

Let me know if it works.

[jasonfordx]

Dear Mr.Cwiok,

Im doing this part:

(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"

But how I point out the column with the 1600 lines on it?

My location for the list is: =Sheet2!A1:A1623

[Michal_cwiok]

First you need to set up a function.

(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"

Once you create it, you should see something like this:

Please test it by typing in 1, 2 or whatever number. You should get a result for that particular book.

Once we get this working, we will get the list from Excel.

[jasonfordx]

Dear Mr.Cwiok,

I got into that, but some books are not named as book1 or book2, some names also have codes like bookINF, bookXML, bookTYR.

Is there any problems calling letters instead of numbers? Or is the same solution?

EDIT:

I did your first step sugestion. When that window appears and ask me an input to invoke I filled with INF and worked.

[Michal_cwiok]

There is not problem with 1, 2 or even INF or whatever.

To download the list from excel try this approach:

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", Int64.Type}}),
    List_of_values = #"Changed Type"[Column1],
    Download_all = List.Transform(List_of_values, each Query1(_))
   
in
    Download_all

[jasonfordx]

Dear Mr. Cwiok, I write this function together with the main query or I create a new query?

EDIT: Trying to put everything in one query im getting an warning:Token Eof expected

[Michal_cwiok]

No, do it as a seperate "blank query", like this:

[jasonfordx]

Dear Mr. Cwiok,

Just did. Got several erros because was trying to convert letters to numbers I guess:

DataFormat.Error: We couldn't convert to Number.
Details:
    INF

EDIT: Although I can see the rows with the say Error, so got the right file 🙂

[jasonfordx]

After that can you please provide me your bitcoin Wallet? I want to send you something because of your time. Ty soo much for being a really nice person.

[Michal_cwiok]

Try this and thanks 🙂

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

[jasonfordx]

Dear Mr. Cwiok,

Cant run. It says:

Token In Expected

Looking your first query, i did this lul:

in
Download_all

[Michal_cwiok]

Sorry, I have pasted the first part. Just for future Token "IN" is the last bit in the query:

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

[jasonfordx]

Dear Mr. Cwiok,

Now all cells are filled with "Error" saying:

Expression.Error: A cyclic reference was encountered during evaluation.

[Michal_cwiok]

That is interesing...

So previously you did see some records, but now everything is with Error?

Please paste Query1 code and Download_all code. Thank you

My code:

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

and:

(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"

[jasonfordx]

Dear Mr. Cwiok,

Probably I expressed myself in a wrong way. I dont think that I saw any values besides error.

I did not understood your instructions now.

Edit: You were suggesting doing this?

[Michal_cwiok · Accepted solution]

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:

[jasonfordx]

Dear Mr. Cwiok,

I did that and now the Excel is working. Its loading something big hehehehe. Still loading.

[Michal_cwiok]

It does this API call 1623 times. If each call takes 1 second, you have a half an hour break 😄

Perhaps, get only 10 rows from Excel just to test it, before going with the whole thing.

[jasonfordx]

Hehehehhehe you are right again hehehe, probably it going to take time

[jasonfordx]

It worked, but now instead of having several rows and columns, I just have one column with the sayings: Table.

I had an query to convert the fields that I wanted to but in this query how should I write to exctract each table value in a row and several columns?