I need… to union multiple tables

Intro

The technique of unioning data sets together involves stacking similarly structured data sets on top of each other. If your union involves lots of separate files or worksheets, this job can take a lot of effort.

The scenario

You want to draw together all four regions’ orders into a single data set.

The steps

1. Bring in one of the files / worksheets you want to include in the output. In this example, Central has been brought in.

2. Select ‘Tables’ in the configuration pane of the Input step

3. Select ‘Union multiple tables’

4. Select the blue ‘Apply’ at the bottom-right corner of the configuration pane. Once the Apply option has been selected it will turn grey and state ‘Applied’.

5. Add a Clean step by clicking the + icon at the end of the Input step

6. Remove the File Paths field from the data set as it’s unnecessary due to all the worksheets coming from the same workbook. Click on the ellipsis icon when hovering over the field in the Profile Pane and selecting ‘Remove’.

Bonus

If your file has worksheets you don’t want to bring in, you will need to add filters to only include those worksheets you need. If you only want the regions with names ending ‘st’ then you can apply a filter using a wildcard icon — an asterisk. Click ‘+ Add Worksheet Filter’ and enter ‘*st’ as the filter. Once you click ‘Apply’ you will see only those worksheets you will bring into the workflow.

Outcome

Using unions on input will save you bringing in a single input for every file or worksheet you need. Using a wildcard union can also allow you to handle more complex file make-ups.