Alteryx Tricks You Didn’t Know – Ep 2: Data Preparation

[TheOC]

Welcome back to Alteryx Tricks You Didn’t Know, the series where I intend to level up your Alteryx game. Each edition will have 5 quick tips, all based around a specific theme (and if you missed the first edition, you can read it here).

This time: Data Preparation.

1. 💾 Custom Formulas can be your secret weapon

Got a formula that you use across multiple different workflows? Maybe some awful regex that you feel lucky to get working, or a date conversion you often have to check the documentation for? Here’s a trick most people miss: You can actually save your formulas inside the Formula tool!

Once you’ve written your expression, just hit the little save disk icon next to the expression editor. This will save it for future use, not just on the workflow that you’re currently working on, but any future workflow builds.

The next time you’re building a formula, you can use any of these saved expressions by clicking the folder icon:

This makes it extremely quick to reuse logic you’ve written before.

2. 📊 Get Row or Column Percentages in a single tool

When trying to calculate grouped percentages in Alteryx, you may be tempted to break the data apart into multiple summarize tools and a formula tool; however, there is a better, often overlooked way.

Take, for instance, the following dataset:

Let’s say I want to calculate, for each month, what percentage each city contributed to the total spend. Let me show you how we can do it within a single cross-tab tool:

By selecting “Percent Row”, the output contains each city, with a percentage value of the visits over the total, even grouped by month:

This is very useful for shares of revenue per product, survey responses, HR datasets – literally anywhere you need grouped percentages!

3. 🔣 Keep Special Characters in Cross Tab Output

Have you ever Cross Tabbed a field named something like “Percentage (%)” or “Total (£)” and had this happen?

Very frustrating!

Luckily, the ever-so-kind developers at Alteryx have us covered. Did you know there is a new option in Designer 25.1 in the Cross Tab tool to retain special characters?

Simply hit this, run your workflow again, and lovely stuff, we have our data as expected:

4. 🤔 Save tool Defaults and Save your Sanity

Got a particular tool habit: e.g. first 1 record in a sample tool, or grouping by ‘region’ in a Summarize tool?

How cool would it be if we could set these actions to be the defaults of these tools? Oh yeah, we can!

Simply configure your tool and hit the pencil icon in the bottom left corner to save the default of the tool:

Now, every time you drag a new version of this tool onto the canvas, it will already be populated with the default selection you have saved. How amazing is that?!

5. 🧼 Data Cleanse without Data Cleansing.

You may or may not have noticed that the data cleansing tool is a little slow. It’s great for demos or small datasets, but when you want to apply cleansing steps to larger datasets, it can become a serious issue for performance.

I propose 2 solutions to fix this for you!

The first is using the all-new Data Cleanse Pro Tool, designed to be the more performant, more flexible version of the data cleansing tool.

 

If, however, you do not have access to this tool because you are not on Designer version 25.1, we have another option! One reason the Data Cleansing tool is slow is that it’s actually a standard macro, containing several nested macros that carry out each step. You can check this by right-clicking a data cleansing tool and selecting ‘Open Macro’:

Only want to perform a single data cleansing step on your data? Why not take the inner macro from the data cleansing tool that performs that step and place it directly onto your canvas, skipping any of the headroom of the additional macros inside that tool? This can shave valuable time off large workflows, especially when you’re dealing with millions of rows or chained cleansing steps.

 Source: GIPHY

That’s it for this episode of 5 Alteryx Tricks You Didn’t Know!

5 very quick tricks that can massively improve your workflow efficiency.

Do you have a favorite data prep hack I didn’t mention? Drop it in the comments. Let me know if there’s a theme you’d love to see!

For your next read, check out my workflow configuration tips.