Miguel Escobar Published May 21, 2018

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

Power BIPower Query

The results are in! Let’s find out which tool is better for the average Business Analyst based on the 3 scenarios that I showcased a few weeks ago here.

I was able to get in touch with the Product Team from Microsoft’s Power Query and also the one from Trifacta’s Wrangler. I tried getting in touch with the Tableau folks, but they  never replied to my emails. Luckily, someone within Tableau’s Sales team did see my videos on YouTube and was able to give me a more refined view of what Tableau is capable of. Big thanks to everyone who helped me along the way through forum responses and multiple emails!

We’re going to break this down by each scenario and then we’ll tally up the results to figure out who’s the winner.

Scenario 1: Grouping Data

All of the tools were able to accomplish the result fairly quick and simple.

Here’s a quick comparison showcasing the pros and cons of each tool based on the experience that I had with them during the creation of the Grouping Data solution:

image

Now let’s go with the final Results for this specific scenario:

image

(for more information about what is and how to read a Pugh Matrix I recommend reading this article)

The winner in this case is actually Tableau Prep! In second place we have Power Query and third place Trifacta Wrangler.

Personal Comments

The main difference between Power Query and Tableau Prep is the fun factor. The whole drag and drop and the whole visualization and layout that Tableau Prep was something new and refreshing to me, so perhaps that has something to do with it, but I haven’t had that much fun with an ETL tool in a while. Trifacta Wrangler also had that fun factor, but for some reason it was pretty sluggish loading datasets and loading its own screens.

Scenario 2: Merging Data

Similar to the first scenario, none of the tools had any issues delivering the expected solution. They were all able to accept the logic that we had in mind.

Here’s a quick comparison showcasing the pros and cons of each tool based on the experience that I had with them during the creation of the Merging Data solution:

image

Now let’s go with the final Results for this specific scenario:

image

(for more information about what is and how to read a Pugh Matrix I recommend reading this article)

The winner in this case is actually Tableau Prep! In second place we have Power Query and third place Trifacta Wrangler.

Personal Comments

Again, the main difference between Power Query and Tableau Prep is the fun factor and, in this specific case, the “flow” view that is right in your face whenever you’re creating a solution with Tableau Prep. You could argue that the Dependency view  inside Power Query could give you that “high level view” that Tableau Prep also gives you, but the whole drag and drop, the whole visualization and layout that Tableau Prep has is something really well done and interactive, so it’s engaging and inviting. It’s like Tableau Prep invites me to work visually, whilst Power Query expects me to figure out that Flow in my head and visualize the whole thing in my head.

Can you imagine Power Query with an interactive dependency view that works as a some sort of pop up lightbox? that would be pretty amazing.

Scenario 3: Appending Data

This scenario was quite different from the rest. Every tool had its own way of handling this type of cases. Some of the tools had their limitations like the ones shown by Tableau (here and here), while Trifacta Wrangler required the end user to already know ahead of time the function (UNION) in order to append the data – there’s no UI way to accomplish that in the latest public version of the tool.

Here’s a quick comparison showcasing the pros and cons of each tool based on the experience that I had with them during the creation of the Appending Data solution:

image

Now let’s go with the final Results for this specific scenario:

image

(for more information about what is and how to read a Pugh Matrix I recommend reading this article)

Personal Comments

Power Query hands down has the best experience when it comes to combining or appending all files in a folder. It can be any type of file – Excel, csv, json, access files, you name it – Power Query can do. The rest of the tools either don’t support this scenario (Trifacta can’t connect to a folder) or support it half way (Tableau forces you to do the UNION right away without cleaning the file first) which isn’t ideal.

Tableau Prep is a new product, so I understand that some functionality may not be present, but I was expecting a lot more since they are well aware that Power Query and Trifacta Wrangler were out there and that comparisons like this one were bound to happen.

Conclusion and Final Results

image

This is the final table with all the results. Power Query was the DATUM, so if the result was a positive one, it would mean that the tool was better than Power Query. If it was lower than zero (negative), it simply means that Power Query is a better tool for that case.

Overall, Power Query is still a long way ahead of Tableau Prep and Trifacta Wrangler. Both Trifacta’s an Tableau’s products have that fun factor that Power Query doesn’t have, but they lack some functionality and speed at development that Power Query can offer.

I had some conversations with the Trifacta team about their web version of Wrangler and it is WAY better than their free product that I used in the videos. The web version of Trifacta Wrangler is actually better than Tableau Prep from my point of view, but since it is not available for everyone, it wasn’t a fair comparison for this post, so I had to rule that out. Hopefully the Trifacta team will release a new Public and free version of their tool that can be similar to their web version.

The whole purpose of this post was to find out which one is the best ETL or Data Prep too for the average Business User, and that tool is:

Power Query by Microsoft

Leave your feedback in the comments below. What were your final scores? Do you think that I should’ve weighted things differently? which tool do you like the most and why?

Power BIPower Query
Subscribe
Notify of
guest
4 Comentario
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Mim

Powerquery is great but it has one big inconvenient, it does not operate with other tools, want to load your results to a database, no luck, Dataprep has an advantage here.

Miguel, have a look at Easymorph, it is amazing when how much can one developer can do.

Miguel Angel Escobar

btw! who do you think won on the 1st scenario? or on the 2nd one?

Daniel Otykier

We badly need Microsoft to open up the Power Query API, so that we could use it for stuff like this. Please vote for this suggestion: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/9111658-allow-power-query-to-export-data

Pravin Upadhyay

1-Online Power query can be used in to data factory pipelines, and can load the output into databases or other locations.
2- online power query editor while creating a Power BI dataflow, has the graphical interface too now.