The Delete columns selectively instruction allows a user to delete columns from within an Excel worksheet based on the data contained in a row or in various rows across each of the selected columns.
- For example, a user can configure the instruction to delete any columns from Column A to Column M if the total amount is 0.
Step-by-step guide
1. Create a data step and upload the data.
2. Create an action step to manipulate data.
3. Select the Delete columns selectively instruction.
4. Configure the target range to select just the Total Sales row.
5. Enter the condition(s) on which a row will be hidden and click "Add".
In the following example, columns will be deleted on the condition that the value in row 1 of the selected range in step 4 (Total Sales) is equal to '0'.
6. Click "SAVE" and run the step.
Once this step completes successfully, the appropriate columns will be deleted.
Multiple conditions
In the Delete columns selectively instruction, you can specify more than one condition to be met, in order for a column to be deleted.
1. To add more conditions, fill the condition in the template row (the first row with the blue background colour) and remember to click "Add" after entering the condition (as seen in step 5 in the previous section).
2. In the case of multiple conditions, select whether you want ALL or ANY of the conditions to be met for the columns to be deleted.
- ALL: all conditions you enter must be satisfied for the row to be deleted
- ANY: at least one condition you enter must be satisfied for the row to be deleted
For example, suppose we would also like to delete column A (which contains the shop number) in the report.
- Two conditions are specified and we choose to meet "ANY" of the conditions for the column to be deleted.
This will provide the following output.
Useful tips and resources
- The conditions in step 5 can be generalised into different operators. For example, we can use a wide range of various operators including the below options.
- Users can choose to fix their conditional value (select "Literal") or set it to vary depending on a property elsewhere in the process (select "Data Property Link").
- For those who select "Data Property Link", instead of writing a literal value in the text box, you can link the value to a data property that contains a variable value
- When specifying a condition, the text can be toggled using the tick-box, depending on whether case-sensitivity is desired or leading & trailing whitespace can be ignored.
The file containing the example used in this article can be downloaded below.
Comments
0 comments
Please sign in to leave a comment.