This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Hey @analysiswithtech - I'll give my two cents though I'm sure some non-Alteryx employees will chime in as well! When your task is primarily a data prep and blend scenario to prepare for a report or visualization then PowerQuery can definitely be a powerful tool. The advantage of Alteryx comes to play for me in two big areas 1) Breadth of analytical tasks I can tackle and 2) Ease of use.
For the first point I love that I can go from simple prep and blend to extremely complex, looping business logic followed by geospatial and predictive analytics all with drag and drop in the same pane of glass. As a non-developer who only knows a little Python and SQL Alteryx has really allowed me to go after any analytical task I can imagine from store cannibalization to custom API pulls to risk ring analysis.
For the second, the visual workflow Alteryx allows lets me easily iterate and see my data every step of the way to ensure I'm getting the right result. But more importantly the self-service aspect means that a person with any skillset can use Alteryx, whether it's someone who lives in Excel or a data scientist who lives in Jupyter notebooks.
And once I've got my workflow ready to I know I have a secure place to share and run my workflows in Alteryx Server. Happy to go into some more detail on any of those points if you like. Excited to hear others' experience too!
We were heavy users of Power Query in the past and we've found the following issues.
1. Performance: When running apps that deal with 1M+ rows, Power Query Excel apps tend to become unresponsive. This is probably because, while Power Query itself is relatively fast, it's layered on top of Excel, which isn't. Excel is powerful and very flexible, but it's not optimized for speed.
2. Fragility: Unless your users are very disciplined, they tend to mix Power Query with native Excel operations, i.e. pivot tables, vlookups, absolute cell references, etc. In a large, complex workbook, all you need is one extra row or value in the wrong place to cause the entire thing to crash.
3. Data Integrity: In a worksheet with a column of formulas, it's easy for a user to "correct" a displayed value by simply typing in a value, thereby overwriting the formula. The alternative for the user is tracking down the source of the incorrect value and fixing the issue there, a process which is very time consuming compared to typing in a new value. From this point forward, the results of the workbook will be based on this value as opposed to the correct, calculated one.
4. Maintenance: Excel formula's are difficult to debug especially in multi-sheet workbooks and almost impossible to document. This causes crufting, where a user will add a new worksheet in the app to correct the results of a previous one as opposed to fixing the issue with the offending sheet.
5. Source of Truth issues. Since each workbook can be duplicated multiple time possibly with modifications, there is no way to be sure of which copy is the correct one.
6. Versioning. "Critical Business Process Gold Standard_Final_V6.5_DO_NOT_USE_Corrected_V3.1.xlsx". 'nuff said
7. Auditing/Compliance. Since most excel workbooks are shared with no user access tracking, it's difficult to satisfy the requirements of various Auditing and Compliance bodies under whose jurisdiction you fall under.
Note: The previous shortcomings, aside from the performance one, can all be overcome by applying a rigorous development methodology to developing, maintaining and securing Excel/Power Query Apps. However, most users of Power Query are business users who a) don't have the development domain knowledge required to apply said methodology, b) don't have the time/budget to develop a solid, bullet-proof app.
Alteryx doesn't automatically fix all these issues, but it's core engine is very fast, and does make it easier to build apps that are easier to maintain and when deployed on properly secured Alteryx server, easier to version and audit as well