Power Query vs Alteryx

analysiswithtech

Hi,

I have been using Power Query in Excel in my line of work for some time with no major issues.

Being keen to learn and improve how I work, I have started giving Alteryx a try.

So far, I have not found Alteryx to be a major improvement over what I performed using Power Query.

It will be helpful to hear from experienced Alteryx and Power Query users of where they have found each to be useful.

Thanks

Common Use Cases

Best Practices

Power BI

Accepted answers

All comments

ChrisHe

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!

-Chris

MarqueeCrew

If you have time, I volunteer to look at both with you.

Cheers,

Mark

danilang

Hi @analysiswithtech

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

Dan

martinoreb

Hi,

Many employers push power query over Alteryx because power query is free to use within excel.

As a user of both Altetyx and power query myself, for the following reasons I believe that employee satisfaction and productivity can significantly increase using Alteryx as an alternative to power query.

- For complex data manipulation steps or data processes with more than 4-5 steps required, Alteryx flows are more clear to follow and rely upon visually.

- Data integrity checks are more easily built and indentified in Alteryx flows.

- Alteryx flows are a lot more fun to build, use, and share with co-workers (almost as fun as playing video games).

jarroway

Great points

vaughangary

I LOVED Power Query.... Until I started using Alteryx. For general spreadsheet use and shared data, Power Query is still useful. For raw power, versatility, and ease of use, there is no comparison. Alteryx is boss, by far.

lehiep67

I use both PQ and Alteryx. Alteryx is for ETL, PQ for report and presentation. The users normally prefer to review in excel with its powerful companion Power Pivot. It is easy to use for Finance and Tax people.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703