Import data via python, manipulate it, and send to PowerBI

[Anonymous]

Hello all,

In my scenario I want to accomplish below steps but it takes very long, I waited more than 20minutes.

1. Import data from sql with python( with pyodbc)

2. Manipulate the data, create timeseries forecasting as dataframe(pandas)

3. Send result table to PowerBI and visualize it.

4. Publish report to service and share the report.

In my local python script runs without any error and it takes <1 minute.

But when I try to run it in PowerBI (Get Data->Python script->copy/paste script) it takes very long and throws an error in the end.

Python-PowerBI examples on web are quite simple as just read data from some csv or xlsx file. I didn't see any advanced examples.

So I have several questions about it.

1. Is there any way to accelerate this?

2. Can I use scripting for existing table in PowerBI? - because I can get data from SQL via PowerBI without using Pythonç

3. If everything runs smoothly how does it work in service(powerbi app) ? When people hit refresh how the calculation goes, etc.

4. Is there any other scenario that comes to your mind in order to achieve the steps that I mentioned in the beginning of the post.

Any comment would be helpful.

Thanks in advance.

[v-eachen-msft · Accepted solution]

Hi @Anonymous ,

1. You could try higher version of Python.

2. So your datasource is SQL Server, right? 

You could import data from SQL Server directly. If you want to custom the sql queries, you could set it like the image below.

3. If you want refresh data in service, based on this document, It is not supported to implement schedule refresh via on premise Data Gateway (Enterprise Gateway) for Python in Power BI Service currently .

• To enable scheduled refresh of your Python dataset, you need to enable Scheduled refresh and have a Personal Gateway installed on the computer that houses the workbook and the Python installation. 
• You'll need to have the pandas library installed in your Python environment for the previous script code to work properly.
• For the Python scripts to work properly in the Power BI service, all data sources need to be set to public.
• Nested tables (table of tables) are currently not supported

[Anonymous]

Hello @v-eachen-msft 

Thanks for the comment, I tried but it didn't work, I mean it takes ages to load so I change the pipeline to :

Get data from SQL - execute Python script - write back result to SQL - get data from SQL with PowerBI instead of SQL-Python-PowerBI.

I really don't know why it is so slow but in case someone searching for similar situation, writing result back to SQL is much faster than Python-PowerBI connection. I guess this connection works fine for simple scripts.

Thanks!

[v-eachen-msft]

Hi @Anonymous ,

Does that make sense? If so kindly mark my answer as a solution to close the case. Thanks in advance.