API Call for each row

[Griffin_BI]

Hi I have function to return a random name using an API call It's calle "Get Random User Query":

= (x as text) => Json.Document(Web.Contents("https://api.api-ninjas.com/v1/randomuser/", [Headers=[#"X-Api-Key"=x]]))[name]

I would like to return a unique random name for each row in a table.  

I use invoke custom function:
= Table.AddColumn(#"Changed Type", "PseudoNym", each #"Get Random User Query"("API Key"))

But it returns the same random name for every row instead of a unique random name for each row. Any ideas please?

[Anonymous]

Hi @Griffin_BI ,

Agree with Omid_Motamedise and PhilipTreacy.

One approach is to generate a list of random names first and then add them to your table. Here's how you can do it:

First, create a function to generate a list of random names, please refer to this blog:

How to use List.Generate to make API Calls in Power Query M

Please use List.Generate function to return the list of radom names.

Then add an index in your table and do as follows:

= Table.AddColumn( #"AddIndex", "PseudoNym", each GenerateRandomNames{[Index]})

AddIndex: Let's say you've added an index column to your table, named "Index".
GenerateRandomNames: This is a list of random names.
each GenerateRandomNames{[index]}: For each row in the table, [Index] is the index value of the current row. Query3{[Index]} takes the name of the corresponding index from the Query3 list and assigns it to the "PseudoNym" column.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Omid_Motamedise]

The issue you're encountering is due to how Power Query evaluates the custom function when adding a new column. When you use a function like Table.AddColumn, it evaluates the function for each row, but if the function returns the same output (like a random name) for each invocation because of caching or other factors, you'll see the same value across all rows.

Modify your API call to get, for example, 10 random users:

= (x as text) => Json.Document(Web.Contents("https://api.api-ninjas.com/v1/randomuser/?count=10",
Add a column to the table where you pull a random name from the list returned:

= Table.AddColumn(#"Changed Type", "PseudoNym", each
let
users = #"Get Random User Query"("API Key"),
randomIndex = Number.RoundDown(Number.RandomBetween(0, List.Count(users) - 1))
in
users{randomIndex}[name]

[PhilipTreacy]

Hi @Griffin_BI 

I can't speak for how the API works as I don't have access to it, but does it require a different seed or URL query parameter each time it's called so as to give a different response?

Perhaps calling it in the same way each time gives the same result?

Phil