Connect to REST API (Python-MongoDB)

[Anonymous]

Upon clicking the "Get Data" option and then clicking "Web" in PowerBI, I typed in my own REST API's URL (http://127.0.0.1:5000/players/all). Once I press connect, the following can be seen:

Screenshot of image

Note that the data can be seen in the "Web View", but not in the "Table View". Since it can't be represented in the form of tables, it cannot be loaded into PowerBI. Is there any way where in the data from my REST API can be loaded into PowerBI?

NOTE: I tried linking my MongoDB backend to PowerBI and it worked successfully. However, the source of my data should be the API's URL rather than the backend itself.

Please help. Thanks.

[amitchandak]

@Anonymous , Copy this JSON and check with the online JSON formatter. is the format correct ?

[Anonymous]

@amitchandak , the format is correct, the online converter just beautfied it. 

Even if this format were to be wrong, I won't be able to change it as the API is taking data directly from MongoDB (which stores data in .json format)

Is there any workaround for this problem? Is/are there any other way(s) to solve this?

[v-kelly-msft · Accepted solution]

Hi  @Anonymous ,

Import using Power BI Query Mode:  Select your DSN (after connecting using ODBC connector)and click Advanced Option to enter custom SQL Query to Import your REST API data. You can use ODBC DSN Data sources Preview tool to generate SQL Query. For example you can enter query like below. If you are not sure use Query builder (Found on Driver Preview Window)

SELECT * FROM $
WITH(src='https://my-api-url')

For  details,pls check below reference:

https://zappysys.com/blog/howto-import-json-rest-api-power-bi/?gclid=EAIaIQobChMIzLjLqqTi8gIVAplmAh3vuQOUEAAYASAAEgI_8fD_BwE

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[Anonymous]

Hi @v-kelly-msft. It worked. Thank you so much. 🙂

[v-kelly-msft]

Hi  @Anonymous ,

Glad to help.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!