Dynamic API request to get data

[PascalT]

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.

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

[Anonymous]

https://community.powerbi.com/t5/Power-Query/How-to-make-dynamic-rest-api-data-source-in-Power-bi/td...

I do not see any solution in the above link too..

[Anonymous]

https://powerbi.microsoft.com/en-us/blog/deep-dive-into-query-parameters-and-power-bi-templates/   ... this link provides some hint, but I think the pre-condition is that the data is already populated with different countries in the first place, else there is no way to retrieve the data on-the-fly.

[Anonymous]

yes, I have that similar needs..but I think in Power BI, it seems not possible... or if some M-code can help, I am not sure. Finally, I resort to my way of doing the scripting in R, which can produce the UI easily with the Shiny library.

[v-micsh-msft]

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

[PascalT]

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