I’m now six weeks into The Data School, and just when I thought I had a handle on data preparation, we were introduced to a third tool! Suddenly the question everyone debates, Are you Team Alteryx, Team Tableau Prep, or Team Power BI/PowerQuery, felt a lot more relevant.
Ever since I joined, I’ve heard strong opinions about which tool is most powerful, most intuitive, or simply the “best.” So this week, I put the debate to the test by taking an Alteryx Weekly Challenge and tried solving it not just in Alteryx, but also in Tableau Prep and PowerQuery. My hope was to see how each tool handled the task, where each one shines, and which one allowed me to complete the work with the least friction.
The challenge itself was straightforward:
Find the five highest-average-rated clothing items from each clothing class, but only for items that had at least 10 feedback reviews.
This required three main steps:
- Filter for clothing items with positive feedback (score ≥ 10)
- Aggregate to calculate the average rating per item
- Return the top 5 items per clothing class
Solving the Challenge in Alteryx
Because this was originally an Alteryx challenge, I expected it to be the most direct solution, and it was.
- Filtering: simple Filter tool
- Aggregation: Summarize tool to get average rating
- Sorting + Selecting Top 5: Sort tool followed by the Sample tool, grouped by Class Name
This gave me a clean and efficient workflow with no surprises. Alteryx does what it says on the tin.
Solving the Challenge in Tableau Prep
Tableau Prep handled the filtering and aggregating steps similarly smooth manner, but finding the top 5 per class required something new to me.
Prep doesn’t have a built-in “top N per group” feature, so I had to create a calculated field using ROW_NUMBER(), along with the PARTITION and ORDERBY functions to assign a row number to each item within each class.
{PARTITION [Class Name]:{ ORDERBY [Average Rating] DESC: ROW_NUMBER()}}
ROW_NUMBER() assigns a sequential number to each item in the sorted list - ORDERBY [Average Rating] DESC) - and PARTITION [Class Name] tells Tableau Prep to restart the numbering for each class
Once each item had a row number within its class, I simply filtered for rows 1–5.
It took an extra step compared with Alteryx, but once I understood the logic, it was a neat and flexible approach.
Solving the Challenge in PowerQuery
PowerQuery was where things got interesting. It’s the tool I’m least familiar with, so it took a minute to familiarise myself with it again and remember where the Group By feature lived! Once I found it, calculating the average rating per item was straightforward. But getting the top 5 items per class was far more complex and required a crash course in M code.
But first I needed to group the data again by Class Name, this time keeping all rows as nested tables by selecting All Rows under Operation.
From there, I created a custom column using M code and the Table.FirstN function to select the top 5 items per class.
Top 5 Items = Table.FirstN(Table.Sort([Grouped data], {{"Average Rating", Order.Descending}}), 5)
Table.Sort() takes the nested table ([GroupedData]) and sorts it by the Average Rating column in descending order. And Table.FirstN() takes the newly sorted table and keeps only the top 5 rows. This results in another new nested Table “Top 5 Items”
After expanding the “Top 5 Items” table and cleaning up duplicate columns, I finally had my result.
It wasn’t the most intuitive process, but once I understood how PowerQuery handles nested tables, it made a lot more sense.
Conclusion: Which Tool Won?
Each tool got me to the correct answer, but the journey was different in all three.
- Alteryx felt the most natural for this challenge : fast, visual, and low-friction.
- Tableau Prep required a calculated field for ranking but handled everything gracefully once the logic was set up.
- PowerQuery was the most complex for this type of task, but it also pushed me to understand the underlying M language, which I know will be valuable long-term.
At this point, I’m definitely leaning toward Alteryx as my preferred tool, but it’s still early days. With more practice in PowerQuery, I expect its will start to feel more intuitive.
For now, it was a fun experiment and a great reminder that every tool has its own personality, strengths, and quirks.