The Best ETL tool for the Business Analyst–Tableau Prep vs Power BI vs Trifacta Wrangler (Google Prep)

I’ve been working with Power Query inside of Excel and Power BI for the past few years, but I’ve always tried to stay on top of its competitors, trying to make sure that I’m investing my time using the best tool that there is.

This is one of those times where I allocate some time to find out if Power Query is still the best ETL tool for the Business Analyst out there.

In this case I’m going to compare 3 tools:

• Power Query – inside of Power BI Desktop and representing Microsoft
• Tableau Prep – pka as Project Maestro ad representing Tableau
• Trifacta Wrangler – representing both Trifacta and Google’s Data Prep (since for all intends and purposes, it provides the same UX for the end user)

Disclaimer: I am in no way being sponsored nor promoted by any of these companies. I’m not a Microsoft MVP nor a Tableau Zen Master. My main goal with this comparison is to find the best ETL tool out there and use it.

Some background about me

It’s probably better that I put this out there before we begin. I am one of the authors of the #1 book out there about Power Query and I’m also one of the creators of the #1 site for Power Query related information.

I’ve been trying to promote Power Query for the past few years and the main reason why it’s because every time that I do these types of comparisons (privately), Power Query comes on top against any of the tools that I’ve tried to compare with.

This time is different. We have a new competitor in town, Tableau Prep, which got some new things that I couldn’t test out during the preview period and a more mature version of Trifacta’s Wrangler. Fair to say that I’ve been using all of the 3 tools for quite some time – Power Query more than the rest, but I do have fair amount of experience using Project Maestro and Trifacta Wrangler.

Regardless of what I’ve been doing, I have no issues switching to a completely new tool and starting all over, which means learning its language as long as this new tool is better or has the potential to be better than Power Query.

Note: If you wish to read a general overview of what Tableau Prep is, you can read this article by Jen Underwood here and if you want a general comparison of Power Query vs Tableau Prep you can read this other one by Teo Lachev.

The Scenarios and Rating Criteria

In order to maintain a standard and objectively compare the 3 tools, I’ve taken 3 scenarios from the eBook “Magic Tricks for Data Wizards”. These are 3 of the most frequent data transformation scenarios that Data Analysts or Data Wranglers face on a day to day basis.

These are:

• Grouping Data – doing aggregations to get a different granularity level
• Merging Data – the typical VLOOKUP
• Appending Data – appending or combining data from multiple sheets or from multiple files. Basically stacking one table on top of the other

We’re putting an emphasis on 2 aspects:

• Only focusing and judging these tools on their capabilities as self-service ETL tools – integrations with other tools will not come into play
• Everything should be done through the User Interface (buttons only). We can not use their own languages as most new users will not know anything about that language.

In regards to the rating criteria that I’ll be using, here are the categories:

• Ease of use – how intuitive the UI is and how the final overall user experience was.
• Flexibility – hardcoded arguments vs flexible and future proofed solutions that withstand dynamic changes of the dataset. I’m looking for a tool that helps me craft a recipe, script or query that will work based on a dynamic logic and not just work on specific scenarios. I’m making an investment in creating a recipe that will always work and that I don’t have to constantly modify.
• Transformations available – is the tool putting a limitation on what I can do with my data or is it enabling me to do the transformations that I want? It should have all of the transformations that I need in order to reach my final output
• High-Level Visibility – am I able to see where am at in the whole workflow process? do I see the full picture or am I limited to a narrow scope?
• Development Speed – how many clicks before I get to my final output? does the development process feels sluggish and slow? Does it completely slow my train of thoughts?
• Fun Factor – working with data doesn’t have to be boring. Am I having fun with this tool? or does it look like one of those old programs written in MS-DOS?

You can do the comparison by yourself using the Pugh matrix template that I’ve created for this. Simply click on the button below to download the template.

Instead of writing a long blog post explaining what I did for each scenario (which would’ve taken me weeks to write), I’ve created these 3 videos, one for each scenario, showcasing how I created solutions using each tool.

The next step

I’m sending this blog post over to all the vendors listed here so they can provide feedback on what I could’ve done better when creating the solutions.

In about 2 weeks I’ll come back and write part 2 of this comparison with a final Pugh matrix (using Power Query as the Datum) so I can publish the scores on a per scenario basis. My goal is to continue adding more scenarios as time passes and revisit the ones that I’ve already published once the tools get new updates.

Is Tableau Prep better than Power Query? is Trifacta’s Wrangler better than Tableau’s Prep? I’m hoping to find the answer to this question and, if I make any impressive discoveries you’ll be one of the first to know about it as I’ll start blogging about how awesome that specific tool is.

Don’t forget to leave your feedback! What tool do you think is the best and why?