Forum Discussion
How to import data from cascading queries?
Power Bi vs Excel are quite a bit different in this regard.
For Power Bi*, your best bet is to add a parameter with the search term, and then if you called your parameter SeachTerm, you can simply put that search term in your url like "<url>?search=" & SearchTerm.
To change the parameter the user will need to click the Transform Data => Edit parameters.
For Excel, things are a bit tricky. You will need to use Excel.CurrentWorkbook() function to get the data that is entered into Excel and drill down to the search term. After the user changes it, a data refresh is required to update it.
* An expert user can create their own connector for this data source in order to enable Direct Query which will allow a user to filter the data using the filter controls in Power Bi. This option will work online if you use a gateway and install the custom connector there. Please note that this is not an easy undertaking and is typically reserved for data suppliers who want to make there data set more easily avilable in Power Bi.
1. Start with your teams query.
2. Then add a new custom column that gets the players for that team using the team id column.
3. Expand the table column of your newly added column.
To make the REST call you can do something like:
Web.Contents("https://api-nba-v1.p.rapidapi.com/", [RelativePath = "teams", Headers = [X-RapidAPI-Host = "api-nba-v1.p.rapidapi.com"]], ApiKeyName = "X-RapidAPI-Key"])