Power Automate vs Power BI vs Python for Handling Multiple Excel Files - My Experience

Recently, I had to process multiple Excel files in a folder, each following one of two table layouts. My goal was to extract specific columns based on which table layout the file followed, perform calculations and aggregate the data effectively.

I tried doing this in Power Automate, then Power BI and also in Python. Each tool has its strengths and weaknesses and I found Power BI to be ideal in my situation.

Power Automate

Pros:

Being an tool from the Microsoft Power Platform, it integrates well with other Microsoft storage apps like SharePoint and OneDrive so it's easy to access files from there.

There is also no coding required for simple tasks as Power Automate comes with an arsenal of drag and drop blocks for you to put together into a flow.

Cons:

The preset blocks are also somewhat of a limitation as it can be difficult to do anything outside of the block-based logic if there isn't a preset block ready for you, you'd need to use custom blocks with your own code which makes Power Automate redundant.

To figure out the layout of a table, the "block engineering" required seemed unnecessarily complex for simply checking a specific value and choosing to read a specific column.

There is also the problem of efficiency compared to the other tools. Iterating through the many excel files and running the if-else checks along with reading specific columns took several hours for a few hundred files.

Python

Pros:

Python, despite being a high level language, is much more low level than Power Platform apps, this allows for maximum flexibility and control over the data transformation logic. There are also many data focused libraries such as Pandas.

Cons:

Firstly, Python may require certain security clearance for accessing shared files from apps like OneDrive and SharePoint which may cause extra administrative work in a business or corporate context.

In this context, more effort was required to write code to do simple tasks which have preset blocks built into the Power Platform apps.

Power BI

Power BI offers a balance of flexibility and ease of use. There are predefined commands in the transformation window similar to those in Excel however the added benefit is that when you add a step to the transformation, it creates a line of code that you can edit further in a query editor or add your own line of code using Power Query.

There is also the benefit shared with Power Automate of being able to easily integrate with and access files from OneDrive and SharePoint.

There is also a huge performance advantage compared to Power Automate for iterating through Excel files.

Power BI handled multiple files seamlessly while Power Automate struggled. Power BI also gave lower level control compared to Power Automate along with preset commands that aren't available in Python. This balance meant that I could easily find code blocks for simple tasks and integrate custom code easily so that I'm not limited by the block-based logic.

Final Takeaway

Assess the needs of your ETL project and consider the trade-offs between these different tools before choosing one.