How to import data from cascading queries?

[j_martinho]

I have a query that brings me NBA team data from RapidAPI and another that brings players based on id (team) and season (year) parameters.

https://api-nba-v1.p.rapidapi.com/teams - Teams
https://api-nba-v1.p.rapidapi.com/players?team=1&season=2022

How do I import player data either power query in excel or power bi?

API Documentation: https://rapidapi.com/api-sports/api/api-nba/

[artemus]

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"])

[j_martinho]

Thank you!

It worked in parts. As the API source has a limit and the NBA base is very large, I didn't get all the data.

How could I leave the search keys as a parameter to be obtained within the report in power bi or excel?

[artemus · Accepted solution]

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.