API pay load to unique rows

[Anonymous]

Hi All, 

The goal: 

Have a single API and have multiple imputs that will trigger it providing diffrent data to each row.

I used the code below and it created a different column, I wanted that, however it gave me an error for each row. The variable will be "ID" and should be inputed to the API every diffrent row. 

*****************************************

= Table.AddColumn(#"Filtered Rows", "API pay load", each let

Source = each Json.Document(Web.Contents("https://dseapps.com//v1/reporting/investments?entity_type=portfolio&entity_id=" & [id] & "&report_date=2019-03-30&currency=Portfolio"
)),
Source1 = Source{0}
in
[API pay load])

***************************************

I was able to eliminitate the error and acrtually create a connection using the below code, however, it is requirin me to imput a paramater that I dont have or know... 

= Table.AddColumn(#"Filtered Rows", "API pay load", each let
Source = each Json.Document(Web.Contents("https://dseapps.com//v1/reporting/investments?entity_type=portfolio&entity_id="&[id]&"&report_date=2019-03-30&currency=Portfolio")), Source1 = Source{0},
Results = Table.FromRecords(Source),
#"Changed Type1" = Table.TransformColumnTypes(Results,{{"Investment ID", Int64.Type}, {"Portfolio Name", type text}, {"Investor Name", type text}, {"Fund Name", type text}, {"Closing Date", type date}, {"Liquidation Date", type date}, {"Active / Liquidated", type text}, {"Original Commitment", type number}, {"Current Commitment", type number}, {"Contributions", type number}, {"Distributions", type number}, {"NAV", type number}, {"Last Reported NAV", type number}, {"Last Reported NAV Date", type date}, {"Unfunded", type number}, {"Exposure", type number}, {"DPI", type number}, {"RVPI", type number}, {"TVPI", type number}})
in
Source)

********************************************

Initiall I was thinking of having the user of the card select the paramaeters and then trigger the API. If you have any insignt on how to use variable parmanteres and then trigger APIs i will appreciate the info.

I am traying to trigger an api per row and have the payload populated next to the ID column. 

Thanks for all the help. 

[v-piga-msft]

Hi @Anonymous ,

I have a little confused about your scenario.

It seems that you have error message. If it is convenient, could you share the screenshot of the error message so that we could help further on it.

Best  Regards,

Cherry

[Anonymous]

I am sorry for the confusion. I was able to figurer out the error.I am still having issues finding a way to integreate a column variable or a parameter inside the API. I am running the below code and it it actully pulling correct data for the "entity_id=42" . The problem is that every row is pulling the data for the fixed id which is 42. 

= Table.AddColumn(#"Added Custom", "Custom.2", each Json.Document(Web.Contents("https://example.com/excel/v1/reporting/investments?entity_type=portfolio&entity_id=42&report_date=2019-03-30&currency=Portfolio")))

My end goal would be use a cloumn id as avariable that would populate diffrent "entity_id=variable". I have tried using the below code and it will give me an error. 

= Table.AddColumn(#"Added Custom", "Custom.2", each Json.Document(Web.Contents("https://example.com/excel/v1/reporting/investments?entity_type=portfolio&entity_id="&[id]&"&report_date=2019-03-30&currency=Portfolio")))

If you happend to know any better way to integrate a variable or parameters in the API I would appreciate your feedback.