Python Script in Power BI and Power Automate

1
votes

I'm pretty inexperienced with Power BI and Power Automate so apologies if there is a simple answer to this question (I couldn't find it at least).

I have a python script that takes data from some excel files, creates a few dataframes, and then produces an excel workbook with 3 sheets as the result. The script essentially uses pandas for the dataframe work and then xlsxwriter to produce the excel sheet. The input excel files are received via email daily.

We've just started using Power BI and Power Automate at work and I was able to create a flow that takes the input excel files directly from my inbox and pastes them into a work sharepoint. I would then run the python script manually.

However, I've recently been trying to automate running the python script in Power BI / Automate, given the success of the 1st flow. However, I have not found a solution that seems to work.

I've tried creating another flow that essentially starts with a trigger that activates when the input excel files are updated. I then input the code into Power BI as a dataset with an action in the flow to update this Power BI dataset. The flow is successful but doesn't produce the output excel file.

Essentially, I want the python script to run whenever the input files in the sharepoint are updated (result of the 1st flow) but I'm confused as the best route to go down. It seems I could generate each worksheet as a table in Power BI and maybe export those into an excel workbook but I haven't had any success.

1
pythonexcelpowerbipower-automate

1 Answers

0
votes

To my knowledge there is no direct way to do what you are asking. But I think there are multiple workarounds so here are my recommendations:

  1. (Recommmended solution) I love python, but Power BI is built to injest data via Power Query. I have found that Power Query can do similar tasks to Python, in regards to data cleansing. Here is quick start guide on Power Query. So my recommended approach is continue to use Power Automate to push excel files to SharePoint. Then utilize PowerQuery to pull the data into Power BI and format it the way you want. If you are publishing the PowerBI dashboard to the PowerBI app you can schedule up to 8 refreshes a day of your data. I think it's also possible to use Power Automate to push refreshes of the data as well.

  2. Continue with current approach utilizing Power Automate to pull excel files from inbox to SharePoint but use Windows scheduler to run your python script on a recurring schedule..