Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
goody
New Member

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

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

 

2 REPLIES 2
skippy2070
New Member

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

skippy2070_2-1689975500872.png

 

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

skippy2070_1-1689975256703.png

 

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 

skippy2070_3-1689975839601.png

 

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

skippy2070_4-1689975880546.png

 

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:

skippy2070_5-1689976081236.png

 

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:

skippy2070_0-1689975193120.png

 

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

skippy2070_6-1689976312332.png  

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"

skippy2070_7-1689976386543.png

 

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.)

skippy2070_10-1689976620853.png

 

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

 

 

 

 

 

Vera_33
Resident Rockstar
Resident Rockstar

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])

Vera_33_0-1637022495939.png

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

Vera_33_1-1637022842857.png

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors