This is my first week using Alteryx and I am enjoying finding ways to automate the tedious tasks that take the fun out of being a data analyst. Below is an example of some data you may need to reshape for use in Tableau. This looks like something that could have been created using a pivot table in excel. It would be difficult to use immediately in Tableau because the gender is not repeated on every row. When creating a pivot table in excel there is an option to repeat the field names on each row. This is not the default so often tables are saved with the field name only appearing on a separate row above a sub-group.    This creates problems in tableau as we cannot filter by gender when it is not in the same row as the rest of the data. Here’s how we can overcome this problem using Alteryx.

Step 1: Connect the data to the Multi-Row Formula tool
Drag the tool onto the workflow from the preparation tab

Step 2: Configure the multi-row tool
Click on the tool in the workflow to edit the configuration. We have the option to update an existing field or create a new field. In this case we want to fill in the blank gender cells so we will choose to update an existing field. Select the field to update, in this case ‘Gender’.

‘Num Rows’ refers to the number of rows that we are going to use in the calculation. You will notice new variables appear in the box below if you increase this to two or three. In our case we are going to use a simple formula which only uses the previous row so we can leave ‘Num Rows’ set to 1.

Step 3: Write an expression
An expression is required to tell Alteryx how to calculate the cells that we want to update. Here is the formula I used:
if isnull([Gender]) then [Row-1:Gender] else [Gender] endif
This simple if else statement can be created by inserting variables and functions from the pane above the expression pane. It simply says if a gender cell is blank (null) then use the gender value from the row above (Row-1). If a gender cell is not blank then it should remain whatever value it has. Because Alteryx updates the rows from the top of the table down to the bottom, a gender value will always be available in the previous row when the statement runs for a given row.

Here’s what your data should look like after you’ve run the tool:

The next steps would be to remove some of the unnecessary subtotal and blank rows using the filter tool.