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
PascalT
Helper I
Helper I

Dynamic API request to get data

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

 

5 REPLIES 5
Anonymous
Not applicable

Anonymous
Not applicable

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
Not applicable

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
Employee
Employee

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

 

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

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.