Power BI refresh using python script

[ellgraa]

Hi! I´m a newbie to PBI and am experiencing some issues. 

I´m calling data from a rest API using python script in PBI. I would like this to automatically refresh at scheduled periods (e.g. every morning) or automatically when someone clicks the refresh visuals button in the PBI report. 

Problem (1):
- I have scheduled a daily refresh on the dataset settings using this page as guidance --> Data refresh in Power BI - Power BI | Microsoft Learn. However, as my personal gateway is not online 24/7, the refresh always fails. Furthermore, I believe my python dependencies (which my python script is based on, e.g. pandas, ratelimit, etc) is also causing the failues as currently I am manually installing the required library every time I log onto my computer. 

I have seen potential solutions such as using the Azure ID app however this is expensive.

Problem (2):
- I am leaving the company soon so I would not like anything tied to my latop etc. However, I am finding it hard to detach my work/codes from the refresh process. There are no other data scientists in the company so would like a relatively easy solution for them to operate.


Many Thanks, 
E

[123abc · Accepted solution]

To address your two problems related to Power BI data refresh using Python scripts, here are some potential solutions:

**Problem 1: Scheduled Data Refresh Failure**

1. **Use a Dedicated Server or Service:**
If your personal gateway is not online 24/7, consider using a dedicated server or service that can host your Power BI data refresh processes. This could be an on-premises server or a cloud-based server (e.g., Azure VM) that is always available. You can install the Power BI Gateway on this server and configure it to handle the data refresh.

2. **Automate Python Dependency Management:**
To address the issue of manually installing Python dependencies every time, you can automate this process using tools like `pip` and requirements files. Create a `requirements.txt` file that lists all the Python packages and their versions required for your script. Then, create a batch script or PowerShell script that runs `pip install -r requirements.txt` to install the dependencies. You can schedule this script to run periodically.

**Problem 2: Dependency on Your Laptop**

To ensure that your work and codes are not tied to your laptop, you can take the following steps:

1. **Documentation:** Document your Python script, including installation instructions for the required libraries, any environment setup, and the data source configuration. Provide clear, step-by-step documentation for your colleagues who will be taking over your work.

2. **Version Control:** If your Python script is not already in a version control system (e.g., Git), consider adding it to one. This way, your colleagues can access the latest version of the code and collaborate on it if needed.

3. **Data Source Configuration:** Ensure that the configuration for connecting to the REST API (e.g., API keys, endpoints) is well-documented and separate from the code. This allows others to update these configurations without modifying the script.

4. **Handover Training:** Provide training or handover sessions to your colleagues who will be responsible for maintaining the Power BI report and the associated Python script. Ensure they understand how the script works and how to make updates if necessary.

5. **Backup Ownership:** Ensure that the ownership and administration of the Power BI dataset and data source connections are transferred to someone else in the organization.

6. **Test the Transition:** Before you leave, have your colleagues test the data refresh process and ensure they can successfully run the script and update the report.

By following these steps, you can help ensure a smooth transition for your colleagues and minimize the dependency on your laptop for Power BI data refresh.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

[ellgraa]

Hi! Thankyou so much for your response :). We do not have any cloud machiene or server currently so I will follow the method of authomating the Python Dependency Management. 

Many Thanks!
E

[nirali_arora]

Better idea would be to have a cloud machine or a server in your organisation which is always running.