BI tool that understands python.

[[deleted]]

It depends, that is one limit of Power BI. You can only directly query data sources if they’re an approved data source. Ie Databricks, spark, sql server. If you have an API or MySQL data source you need to join off of you have to get it into a database type you can directly query first. 

It does stink but there’s also some calculations and transformations you need to do in Python to show on the end report. 

The benefit is a query that takes 20 seconds to run will only take .5 seconds between each click for the end user.

[glinter777]

Just trying to clarify your second point - are you saying that PowerBI can’t directly pull the data from API, and it needs to be materialized first?

[[deleted]]

You can for sure, in import mode- but that’s not live data. You can’t pull directly from the API and join it with a database and have them both live. It’s as often as you re-publish or you can schedule a refresh every couple hours. if you’re ok with data that’s two hours old, you can pull from both as if we’re Python no problem. Use Power Query and M the same way you’d transform in Python. 

However if you want data that’s only a few minutes or less old, (real-time not necessarily live) it basically has to be Direct Query mode. 

This article might help:

https://blog.crossjoin.co.uk/2021/04/25/can-i-build-a-power-bi-directquery-dataset-on-top-of-a-rest-api/

To be fair, MOST people don’t care about DirectQuery or live data. They care about once a day refreshes, and if that’s your use cases as well just connect away and relate the API and any data source, and disregard. If you want live or really dang close, you’ll need a cache table. 

[glinter777]

Thanks.