How to pass each row of the list as a parameter to the function ?

[goody]

Hi all,

First, sorry for my English 🙂

If you understand, what I mean, thanks you very much.

So:

I have a some List of Account_Id`s in Power Query.

And I have a function in Power Query.

This Function make a POST Query to server.

ModulToken is Constant. Problem is in Acount_Id :(((

I want to send each row from List of Account_Id`s to Function, and get Response from Server for all my Account`ids in one Query,

But I have a Error.

Error is not a question.

I understand, that I make some not right.

How must I send each row of my list to function right ? I would be grateful for a sample :))

(account_id)=>let
    data = Json.FromValue([id = ModulToken, account=account_id]),
    headers=[#"Content-Type"="application/json"],
    web = Json.Document(Web.Contents("*/*.php", [Content=data, Headers=headers])),
    #"Преобразовано в таблицу" = Table.FromList(web, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Развернутый элемент Column1" = Table.ExpandRecordColumn(#"Преобразовано в таблицу", "Column1", {"id", "companyId", "status", "category", "contragentName", "contragentInn", "contragentKpp", "contragentBankAccountNumber", "contragentBankCorrAccount", "contragentBankName", "contragentBankBic", "currency", "amount", "bankAccountNumber", "paymentPurpose", "executed", "created", "docNumber", "absId", "ibsoId", "kbk", "oktmo", "paymentBasis", "taxCode", "taxDocNum", "taxDocDate", "payerStatus", "uin"}, {"id", "companyId", "status", "category", "contragentName", "contragentInn", "contragentKpp", "contragentBankAccountNumber", "contragentBankCorrAccount", "contragentBankName", "contragentBankBic", "currency", "amount", "bankAccountNumber", "paymentPurpose", "executed", "created", "docNumber", "absId", "ibsoId", "kbk", "oktmo", "paymentBasis", "taxCode", "taxDocNum", "taxDocDate", "payerStatus", "uin"})
in
    #"Развернутый элемент Column1"

Best regards

Dmitry

[skippy2070]

I just found an answer for this, so even though this is a very old question I thought other searchers might find it useful. I used a separate table containing several rows of IDs as a list of parameters to invoke a SQL Table Value Function for each ID in that table.

Steps

First, I converted my table of "EventIDs" into a list by selecting it in the queries list and right clicking on the desired column header, then selecting "Drill Down."

Then I created a function by right clicking on my new List in the queries sidebar and selecting "Create Function"

Next I copied the M code that invokes my SQL Table Value Function. You can get this by adding a new datasource from SQL Server and selecting your SQL function.

First click on "New Source" and select Database -> SQL Server 

Then enter your server and database names and click "OK"

In the next window, click on your SQL function and click "OK" to create a new query that calls that sql function.

Click on that query to edit it, and click on the "Advanced Editor" button to see the auto-generated code. This is what you will need to copy:

Paste the copied code into the Advanced Editor for the Custom Power Query function you created earlier (overwriting most of the automatically generated code), but replace the last step with "List.Transform ([LIST NAME], each [SQL FUNCTION NAME](_))" as in the code below:

Click the "Invoke" button, and Power Query will create a new query full of tables containing the results of your custom function:

  

Right click on the column header and click "To Table." In the next window choose the needed delimiter (N/A for my scenario) and error handling and click "OK"

Now click on the Left/Right arrow button in the top right corner of your table and select the desired columns. (I always uncheck the "Use original column name as prefix" box.)

Lastlly, click "OK" and you will have a shiny new query that simulated a CROSS APPLY result from your SQL function!

[Vera_33]

Hi @goody 

I am trying to picture what you have, let's call your function as fxTable for now

if you have a table like this, add a custom column and call this function to pass account_id, then you can do Table.Combine( #"Added Custom"[Custom])

or if it is a list of account id, you can do List.Transform then Table.Combine