API works until invoking custom function - Expression.Error: Evaluation resulted in a stack overflow

[bigshooTer]

I have a query which is an API to pull some basic numerical data.  When the data is returned, I have to tranpose the rows & columns, then promote headers.  This puts the returned data in the correct format.

I then set a parameter for the account number, with a default value = to the account number which successfully returned data when hardcoded.  It continued to function properly.

Lastly, I imported a table from access and invoked a custom function to use a list of account numbers (text) to pass to the parameter.  I assumed this would work fine.  Wrong.  I get "Expression.Error: Evaluation resulted in a stack overflow and cannot continue."

Initally I assumed it was because I needed to transform the data in a step before invoking the function, however I still get the same error.

I do have some records in the imported table of account numbers the function uses which are blank or invalid.  I added a step to filter off of them though.  I don't think that is the issue but if anyone can help would be greatly appreciated.

Inital dataset after converting to table

AcctNumber	accountnumber#################
balance1	555
balance2	66666
balance3	22222
balance4	111111111111111

After transposing the table, the data set looks like this (prior to invoking function)

AcctNumber	Balance1	Balance2	Balance3	Balance4
accountnumber#################	555	66666	22222	111111111111111

I'm referencing the list of account numbers in the function in hopes of adding addtional rows to this table for other account numbers.

Why am i getting this stack overflow error and how do I fix it?

[v-jingzhang]

Hi @bigshooTer 

I'm afraid you cannot pass a list of account numbers (text) to the parameter. You set a parameter for the account number (text), so this parameter can only accept a Text type value. It cannot accept a list as a List type is different from the data type Text when this parameter is defined in the function. The data type should be consistent. 

How do you get all Account numbers? You may need to get all Account numbers into a column of a table first, then use Add Column > Invoke Custom Function to add an additional column. In this way, you can pass the [Account Number] column to the parameter in the function. It will invoke the function for every row with the account number on that row. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it. Highly appreciate your Kudos!

[bigshooTer]

The account numbers are called from a text column in a table.  There is a table which has account numbers (as text) in a column.  The function which I created references this text column.

I am confused because I've done this with a list a values of 10x larger than what I am doing now.  If I step into the query, everything works fine until I invoke the custom function.  The default parameter value is passed succesfully.