Http Get with specifying 'all' for a required field

[sohananahid]

Hi all: Good day! I have a data source that is an Api portal. It has a required filed, Ids that I need to specify an id/ids for which it will give me detail info [for that id/ids]. In order to avoid passing any specific id, I am trying to pull detail info for all ids. How do I specify the ids={ids} part in the Request URL to mention all ids?

I tried differnt ways [no luck yet]  like  https://..../details?ids={}&format=csv 

[ I am planning to specify ‘csv’ for the data format!]. Many thanks in advance!

[v-shex-msft]

Hi @sohananahid,

I think it will help if you share the dev document about that API and it data structures and usages.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[parry2k]

@sohananahid in a scenario like this, create a function which takes id as a parameter and then in the function constructs the URL, in your main table let's say you have 10 rows values 1 to 10, and let's say it I called id. Add a custom column and in this call the function and pass the id as a parameter value. and from there you expand this custom column which will append the data for all the ids. 

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[sohananahid]

Hi @parry2k : Thanks for your response.

Since the data source is [not a db table/ file, but] an API portal, in my 'Get Data' stage: I am using Other-> 'Web' -> Advanced-> then have to specify the Web url .  How can I specify the custom Column when I have not yet connected and pulled data from this data source/ Get Data? Best, Sohana

[parry2k]

@sohananahid I assumed you have a list of IDS that you want to pass to the url, if that is not the case where you suppose to get ids?

[sohananahid]

Hi @parry2k  Since I don't  know the ids- so, trying to find a way to get "All" of them [that would not require me to specify any id]! 

[parry2k]

@sohananahid it doesn't make sense to me, as per the screen shot, it required Id value and you have to have set of ids you want to pull or may be there is another api call that returns Ids and then use that to pass id to this api.

It is very hard to answer with limited information and how this API works, completely shooting in the dark here.

[sohananahid]

Hi @parry2k , Hi all: Sorry for the confusion, I should have provided more info earlier.

There are two APIs (‘notice’ and ‘notice details’ ) I am trying to connect. Api#1 [notice] is my data source where I am doing “Get Data” via ‘Web’ providing the api url and Api-Key [using ‘Advanced’ feature]- get 50 most recent notices. I get the data and display  them in a ‘Table’ visual. One of the fields is ‘Id', an unique field for each notice. When I click on a notice in the ‘Table’ visual, I want to see the ‘notice details’ depending on the 'Id'.

Api#2 [notice details] is what I provided the info for earlier. ‘Id’ is a required field for this Api#2 to get details for a specific notice [with that id]. Now, When I click on a specific notice on the table visual, I have the Id for that notice. I have added drill-through page with another ‘Table’ visual to display the ‘notice details’ info. I have created a measure:

Api Link for ABC Notice Detail = "https://api.abc.com/natgas/events/v1/notices/" & SELECTEDVALUE ('abc-notices_apiConnect'[Id] )

Added a button, ‘More info’ -> with Action-> Web Url and selected this measure for Conditional formatting. But clicking the button, I get ‘this page isn’t working right now’.

As mentioned earlier, I provided Api url and Api-key while I used the Api#1 as a data source. I am not using APi#2 as a data source [as I don’t know the Id ahead of time], but rather trying to connect later to get the details info. I am using the Api url in the measure, but don’t know how to pass the Api-Key- probably that’s why the error is happening.

Any idea how to resolve this issue or get the 'notice details' info to display in a visual ? Many thanks in advance.

[parry2k]

@sohananahid now it started making sense, so basically you make API #1 call to and get the IDs (50 ids) let's say and now you are dynamically creating this url to open the URL and see the list of the id (not in power bi) but it will be online, correct?

[sohananahid]

Hi @parry2k : there are 2  options I thought of.

Option 1: I created a measure ABC Notice Detail Link = "https://apps.abc.com/natgasportal/notice/" & SELECTEDVALUE ('abc-notices_apiConnect'[Id] and

A “More” button, by clicking on it- it goes to a web site that displays the ‘notice details’ info. But it needs to create login access for n of users who will use the Dashboard.

So, thinking of Option 2: created a measure and the button, which I am hoping to connect to the Api portal[ note the difference in urls] and pull the notice details info in json/csv format and show it in a Visual on the Power BI dashboard.

The api url is like: https://api.abc.com/natgas/events/v1/notices/details?ids={ids}&format=csv [ changing the real name with ‘abc’]

I have to change the measure I have come up with so far to use this url [for test I am using a simple version of this api w/o the format of the data]

Api Link for ABC Notice Detail = "https://api.abc.com/natgas/events/v1/notices/" & SELECTEDVALUE ('abc-notices_apiConnect'[Id] )

Option 2 doesn’t need the login credentials for N number of users. But it needs the Api url [which I provided in the measure] and Api key, which don’t know how to provide! Thanks.

[parry2k]

@sohananahid I think both these APIs should be called in Power Query and create this dataset (goes back to my original reply) and that way you can visualize the data in Power BI and don't need to click the external link to view the data, the dataset is prepared at the time of refreshing the data.

Reach out to me directly in case you need how to set this up. My email is in my signature.

[sohananahid · Accepted solution]

Hi @parry2k  : Thanks for the help! Really  appreciate it.

For those newbees like me: 

-I had to change the function in advance editor-> to change NoticeId type from text to number and use Number.toText(NoticeId) in the url.

-Also I copied my master data source into a  2nd table, renamed it and kept only Id Column. Then selected this table to -'Add Column', 'Invoke Custom Function' step to bring a table of records for each row/ Id on this 2nd table. After expanding the Column-> I selected which fields I need to show up in this table. 

-I then added a 'Notice Details' page, added drill-through feature with 'Id', had a 'Table' visual to show info from the 2nd table [giving me the info form my api#2].

-Now I can drill-through on 'Id' from my first page/ Notice Summary: for a specific row/Id-> I go to the 'Notice Details' page and see the Details for that Notice! 

Voila!  🙂