Comparison of Union, Join, and Append
When we analyze data from different sources, we often need to combine two or more tables into one to process the data in the next step. This process is commonly referred to as “joining tables,” and there are various ways to join tables depending on the tool you are using. If you are just starting your data analytics journey and exploring different data preparation tools such as Tableau Prep, Alteryx, or Power Query, it’s important to be familiar with the different terms and menu names used in these tools to avoid any mistakes.
Tableau Prep vs Alteryx vs Power BI
First of all, in Power Query, a “table” is referred to as a “query.” The menu name for “Union” in Power Query is “Append Queries.” Similarly, you can perform a “Join” by clicking “Merge Queries” in Power Query.
When choosing join types in these tools, you need to carefully consider what you want to achieve as a result. For example, selecting “Right unmatched” in Tableau Prep is equivalent to choosing “Right (R)” in Alteryx Designer and “Right Anti” in Power Query. If you want to include every record from the “Right” table in Alteryx, you can use the “Union” tool to combine the Inner (J) and Right (R) outputs from the “Join” tool. In Power Query, you would select “Right Outer” as the join type.
While the concept of an Inner join is consistent across these tools, the “Unmatched only” option in Tableau Prep is not directly achievable in Power Query through the Merge Queries menu.
In the table below, the types of table joins and menu names in Tableau Prep, Alteryx Designer, and Power Query are compared.
However, merging or joining tables is not the only way to analyze a dataset of tables. If there is a relationship between them, you can connect the tables and build a data structure called a schema. It is always recommended to review the dataset and decide how to build a proper structure before you start joining or merging tables.
# Read more about Join
Tableau: Aggregate, Join, or Union Data – Tableau
Alteryx: Join Tool
Power Query: Merge queries overview – Power Query | Microsoft Learn
Author: Don Kang
Experienced Engineer with a diverse background in academic research and engineering seeking for a career transition to data and AI engineer. Skilled in Python, SQL, Alteryx, Tableau, Power BI and cloud platforms such as, GCP, AWS and AZURE. Detail-oriented and quick to adapt to evolving business needs and industry trends, with a passion for leveraging data to drive decision making and innovation.