Reply
Regular Visitor
Posts: 18
Registered: ‎03-09-2017

Dynamic API request to get data

[ Edited ]

Hi the community

My title is not really self explanatory... so let me try to be clear enough here to get an answer!

 

I'm using Campaign Monitor (CM) to send email marketing.

Without going into details, I usualy create a dedicated list for one campaign.

CM-List1, CM-Lists2, CM-list3 and so on.

 

In PowerBI (PBI), I successfully get data from CM using the CM API.

The API request looks like that: https://api.createsend.com/api/v3.1/lists/xxxxxxxxxx/active.xml

Where the xxxxxxxxxx = le List ID provided by CM.

The Query starts by this:

 

let
    Source = Xml.Tables(Web.Contents("https://api.createsend.com/api/v3.1/lists/xxxxxxxxxx /active.xml")),
    #"Changed Type... etc

 

So now, here is my question:

In PBI, have a table. In this table, i've a title column listing the list I've in CM. In the last column, i've the ID.

 

PBI1.jpg

 

I would like to have dynamically use the CM API to get generate or modify a query based a selected row from this above table using list ID (at the end of the table).

 

I'm not sure it should be done in the query editor. 

In my Dashboard, basically, i would like to have the 4 Title, i select one and then, i get a table filled with the Data pulled from the corresponding list from CM (using the API)...

 

Tricky question isn't it?

 

Thank you for your help

 

Pascal

 

Highlighted
New Contributor
Posts: 550
Registered: ‎08-11-2016

Re: Dynamic API request to get data

I think you should be able to do this by setting a Query paramter for the List ID part in the URL.

Take a look at the Query paramter:

https://powerbi.microsoft.com/en-us/blog/deep-dive-into-query-parameters-and-power-bi-templates/

 

Regards,

Michael

 

Regular Visitor
Posts: 18
Registered: ‎03-09-2017

Re: Dynamic API request to get data

[ Edited ]

Hi and thank you. I gave a try to this but in fact it doesn't fulfil my needs :-(

 

I would really like to have a bunch of query, based on the same model. These queries are built based on a list coming from SharePoint Online.

 

My SharePoint Online list have several URL to call APIs.

 

In PowerBI, i want to have as many query as I've records in my SPO list.

 

But i think it's maybe too demanding for PowerBI.

 

Thank you for you hepl.

 

P