Next Level - Data Unions

No More Copy/Pasting

Many times, we want to combine reports from over multiple months to look at trends over time. Learn how to use unions and never manually combine half-a-dozen reports again!

It turns out the web-form wasn't working for some of Friendship Heights Community Reinvestment Fund's outreach staff so they had to compile their data using paper surveys. They just finished entering their data but it's in a separate file found here. Feel free to download a local copy if that's easier.

We could paste this file at the bottom of the original file but that's high-touch and would be tedious if this process had to be repeated multiple times. Instead let's pull the data into our workflow with a Union Step.

Pull the data in either through a direct connection to Google Sheets or to a local copy.

Once the data source is in your workflow, simply drag and drop it onto your original Targeted Outreach dataset where it says Union.

Skill Highlight: Data Unions

If data joins combine data side-to-side, unions stack data on top of each other. Tables that are unioned should have the exact same headers - any headers that are mismatched will still appear but they will only include data from their original tables.

Table A

Table B

Unioned Table A + Table B

Tableau Prep and Tableau Desktop also have a function called "wildcard unions". These functions search a folder for files that contain a given name and/or wildcards (*). Use this function to automatically union new files as they get dropped into a folder.

Data Culture

Help your teammates maintain consistent data labels and consistent data types across all data sets to ensure tables can be unioned without any issues.

Infrastructure

Use consistent data headers when creating new datasets that may be unioned in the future.

Tableau

Use Tableau Prep's Union Step or Tableau Desktop's Union Function to combine similar datasets.
Learn about wildcard unions to automatically pull in new files in a given folder.

Click into the Union Step to see the results. In the heading of each field there is now an indicator of whether that field was successfully pulled from each table. While most fields like Outreach ID and Full Name have data from both tables, the new table only has a header for Friendship Heights Community Reinvestment Fund. Tableau Prep highlights the mismatched fields in the bottom left.

While this is not an issue for this data, keep an eye on the mismatched fields for any misspellings or other simple errors.

The only thing left to do is reconnect the rest of the workflow to the union result. Right-click the line between the original data and the first Clean Step then drop the union onto the Clean Step where it says Add.

And that's it! Everything else in the workflow is preserved and you should be able to re-run the workflow without any additional work.

You are a data shaping boss.

Congratulations on completing the Next Level modules!

In these sections, we learned how to perform data joins to combine two tables across a common field and data unions to combine files with similar headers. We used the Join Step and Union Step to augment our original data with additional files.

Mastering joins and unions are key to automating your data cleaning processes - practice with smaller files as you build your skills!

With your newly robust data, are there any interesting trends or analyses in the data? Try digging around a bit in Tableau Desktop!

Page updated

Report abuse