Seeking Advice for the report flow

[pennyhoho117]

We are going to use Power BI to extract data from Database A and B, and then joining the tables in Power BI, and then store the report data to Database B and send the result report (in CSV or Excel)to user.

is the below workflow is possible?

1. Create Python script to Call Power BI API to run the report and retrieve the Report Data, and save report to DB<is there any API can retrive the report in CSV> ?

2.  Use Power Automate to call the python script

[v-karpurapud]

Hi @pennyhoho117 
Hope you are doing well!

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.

Thank you.

[v-karpurapud]

Hi @pennyhoho117 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-karpurapud]

Hi @pennyhoho117 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[Poojara_D12]

Hi @pennyhoho117 

Yes, your workflow is possible with some adjustments. Power BI’s REST API does not directly export report data as CSV/Excel, but you can use the Dataset API to run DAX queries (/datasets/{datasetId}/executeQueries) and extract data, then process it with Python to save it to Database B. Alternatively, the Export to File API can export reports in PDF or other formats, but not CSV. After processing the data in Python, use libraries like pyodbc or SQLAlchemy to store it in Database B. Power Automate can then trigger the Python script via HTTP requests, scheduled flows, or Power Automate Desktop. Ensure you have appropriate API permissions, optimize DAX queries for performance, and set up Power Automate to notify users with the final report via email. This approach enables efficient automation of report generation and delivery.

 

[v-karpurapud]

Hi @pennyhoho117 

Thanks for reaching out to Microsoft Fabric Community Forum.

Yes, the workflow you described is possible with some modifications.

• Power BI REST API:

  • Utilize the Power BI REST API to execute the report and retrieve the data. Although the API does not directly support exporting reports in CSV format for standard reports, you can retrieve the data used in the report and save it as a CSV file using Python.

• Power Automate:

  • Create a flow in Power Automate to run the Python script. Use the "Run Python script" action in Power Automate Desktop.

• Modify Python Script:

  • Adjust the Python script to include code that saves the report data to Database B after retrieving it from Power BI.

• Distribute Report:

  • After saving the report data to Database B, use Power Automate to send the CSV or Excel file to the user via email or another preferred method.

If my response has resolved your query, please mark it as the Accepted Solution to help others. Additionally, I would appreciate a 'Kudos' if you found my response helpful.

Thank you!

[pennyhoho117]

hi if we dont want to use power automate desktop, then any other possible approach to call our python files in cloud?

[v-karpurapud]

Hi @pennyhoho117 

 To execute Python scripts in the cloud without using Power Automate Desktop,  these are the  following approaches:

1. Azure Functions:

• Host your Python script as an HTTP-triggered serverless function in Azure then call this function on a schedule using Azure Logic Apps or Azure Scheduler, or via a simple HTTP request.

2. CI/CD Tools:

• Use GitHub Actions or Azure DevOps to execute the python script on a schedule or trigger it via an HTTP endpoint.
• Add the python script to a GitHub repository. Configure a GitHub Actions workflow or Azure DevOps pipeline to execute the script based on specific triggers, and then initiate the workflow or pipeline via HTTP or on a schedule.

3. Google Cloud Functions:

• Deploy the Python script in Google Cloud Functions.
• Trigger it via an HTTP request or a scheduler in Google Cloud Scheduler.
  
  

If you find this post helpful, please mark it as an "Accept as Solution" and give a KUDOS.

Thank You!

[pennyhoho117]

Why when call python in power bi deskop return with error such as ImportError: No module named 'psycopg2'? can install libraies in power bi desktop?

[v-karpurapud]

Hi @pennyhoho117 

The error ImportError: No module named 'psycopg2' occurs because the Python environment used by Power BI Desktop cannot locate the psycopg2 library. In Power BI Desktop, it is not possible to install Python libraries directly within the application.

1. Verify Python Installation Path in Power BI Desktop:

     In Power BI Desktop:

• Navigate to File > Options and Settings > Options.
• Under Global > Python scripting, ensure the correct Python installation path is set. This should point to the Python environment where you intend to run scripts.

2. Install the Missing Library:

• Open a terminal or command prompt.
• Activate the Python environment set in Power BI and execute the following command:
  pip install psycopg2-binary

If you find this post helpful, please mark it as an "Accept as Solution" and give a KUDOS.

Thank You!

[Akash_Varuna]

Hi If you are in cloud then you could use Azure Functions to execute the python scripts it is a service with serverless compute 
if this post helped please do give a kudos 
Thanks in advance