Union Your Data - Tableau

</noscript><div id="app" class="wrapper"><header id="tableau-help-article-header" class="container--full-width quick-help-header"><div class="container--centered"><div class="header__back-button"><back-button title="Go back"/></div><div class="header__mobile-menu quick-help-hidden"><menu-tree-toggle/></div><div class="header__logo quick-help-hidden"><a href="https://www.tableau.com/en-us/"><img src="./Resources/tableau-logo.png" class="header__logo__img" alt="Tableau"/></a></div><div class="header__search"><search-header-help placeholder="Search"/></div><div class="header__home-button"><home-button title="Go home"/></div></div></header><div class="container--navigation-top quick-help-hidden content-only-hidden"><div id="help-subheader" class="subheader print-hidden"><div class="container--centered"><h4 class="heading--subheader">Tableau Desktop and Web Authoring Help</h4></div></div><div class="container--top-links"><div class="container--centered container--breadcrumbs"><div><breadcrumb-links-help/></div></div><div id="help-container-menu-headings" class="container--menu-headings"><nav class="nav-medium-screen"><menu-heading-links-static-help menu-title="In this article" :disabled="false" :headings="pageHeadings"/></nav></div></div></div><div class="section--main container--full-width"><div class="container--centered"><nav class="nav-side nav-side--left" role="navigation"><menu-tree-help menu-title="Contents"/></nav><article role="main"><h1 id="contentH1"/><div class="caption article__tags content-only-hidden quick-help-hidden"><span class="article__tags--applies-to">Applies to: Tableau Cloud, Tableau Desktop, Tableau Server</span><br/><span class="article__tags--role"> </span></div><div id="content-body">&#13; <p>You can union your data to combine two or more tables by appending values (rows) from one table to another. To union your data in Tableau data source, the tables must come from the same connection.</p>&#13; <h2 is="heading-item" :level="2" id="supported-connectors">Supported connectors</h2>&#13; <p>If your data source supports union, the <span class="uicontrol">New Union</span> option displays in the left pane of the data source page after you connect to your data. Supported connectors may vary between <span class="VariablesTabsProductDesktop">Tableau Desktop</span> and <span class="VariablesTabsProductServer">Tableau Server</span> and <span class="VariablesTabsProductOnline">Tableau Cloud</span>.</p>&#13; <p>For best results, the tables that you combine using a union must have the same structure. That is, each table must have the same number of fields, and related fields must have matching field names and data types. </p>&#13; <p>For example, suppose you have the following customer purchase information stored in three tables, separated by month. The table names are "May2016," "June2016," and "July2016."</p>&#13; <p>A union of these tables creates the following single table that contains all rows from all tables.</p>&#13; <p><b>Union</b>&#13; <h2 is="heading-item" :level="2" id="union-tables-manually"><a name="union_manually"/>Union tables manually</h2>&#13; <p>Use this method to manually union distinct tables. This method allows you to drag individual tables from the left pane of the Data Source page and into the Union dialog box.</p>&#13; <h3 is="heading-item" :level="3" id="to-union-tables-manually">To union tables manually</h3>&#13; <p>On the data source page, double-click <span class="uicontrol">New Union</span> to set up the union.</p>&#13; <p class="note"><b>Tip:</b> To add multiple tables to a union at the same time, press <strong>Shift</strong> or <b>Ctrl</b> (<strong>Shift</strong> or <b>Command</b> on a Mac), select the tables you want to union in the left pane, and then drag them directly below the first table.</p>&#13; <p>Click <span class="uicontrol">Apply</span> or <span class="uicontrol">OK</span> to union.</p>&#13; <h2 is="heading-item" :level="2" id="union-tables-using-wildcard-search-tableau-desktop"><a name="union_wildcard"/>Union tables using wildcard search (Tableau Desktop)</h2>&#13; <p>Use this method to set up search criteria to automatically include tables in your union. Use the wildcard character, which is an asterisk (*), to match a sequence or pattern of characters in the Excel workbook and worksheet names, Google Sheets workbook and worksheet names, text file names, JSON file names, .pdf file names, and database table names. </p>&#13; <p>When working with Excel, text file data, JSON file, .pdf file data, you can also use this method to union files across folders, and worksheets across workbooks. Search is scoped to the selected connection. The connection and the tables available in a connection are shown on the left pane of the Data source page.</p>&#13; <h3 is="heading-item" :level="3" id="to-union-tables-using-wildcard-search">To union tables using wildcard search</h3>&#13; <p>On the data source page, double-click <span class="uicontrol">New Union</span> to set up the union.</p>&#13; <p>Enter the search criteria that you want Tableau to use to find tables to include in the union. </p>&#13; <p>For example, you can enter <strong>*2016</strong> in the <span class="uicontrol">Include</span> text box to union tables in Excel worksheets that end with "2016" in their names. Search criteria like this will result in the union of May2016, June2016, and July2016 tables (Excel worksheets), from the selected connection. In this case, the connection is called Sales, and the connection made to the Excel workbook containing the worksheets you wanted was in the quarter_3 folder in the sales directory (e.g., Z:\sales\quarter_3).</p>&#13; <p>Click <span class="uicontrol">Apply</span> or <span class="uicontrol">OK</span> to union.</p>&#13; <h3 is="heading-item" :level="3" id="expand-search-to-find-more-excel-text-json-pdf-data"><a name="expand"/>Expand search to find more Excel, text, JSON, .pdf data</h3>&#13; <p>The tables initially available to union are scoped to the connection you've selected. If you want to union more tables that are located outside of the current folder (for Excel, text, JSON, .pdf files) or in a different workbook (for Excel worksheets), select one or both check boxes in the Union dialog box to expand your search. </p>&#13; <p>For example, suppose you want to union <u>all</u> Excel worksheets that end with "2016" in its name outside of the current folder. The initial connection is made to an Excel workbook located in the same directory in the above example, Z:\sales\quarter_3. </p>&#13; <p><b>Include:</b> If you enter <b>*2016</b> in the <span class="uicontrol">Include</span> text box and leave the remaining search criteria of the dialog as is, Tableau looks for all Excel worksheets that end with "2016" in its name inside the current folder. </p>&#13; <p>In the diagram below, the yellow highlighted item represents the current location, that is, the Excel workbook that you created a connection to in the "quarter_3". The green box represents the tables belonging to workbooks and sheets that are unioned as result of this search criteria. </p>&#13; <p><b>Include + Expand search to subfolders:</b> If you enter <b>*2016</b> in the <span class="uicontrol">Include</span> text box and select the<span class="uicontrol"> Expand search to subfolders</span> check box, Tableau does the following:</p>&#13; <p>Looks for all Excel worksheets that end with "2016" in their names inside the current folder.</p>&#13; <p>Looks for additional Excel worksheets that end with "2016" in their names that are located in Excel workbooks in subfolders of the "quarter_3" folder.</p>&#13; <p>In the diagram below, the yellow highlighted item represents the current location, that is, the Excel workbook that you created a connection to in the "quarter_3" folder. The green box represents the tables belonging to workbooks and worksheets that are unioned as a result of this search criteria.</p>&#13; <p><b>Include + Expand search to parent folder:</b> If you enter <b>*2016</b> in the <span class="uicontrol">Include</span> text box and select the <span class="uicontrol">Expand search to parent folder</span> check box, Tableau does the following:</p>&#13; <p>Looks for all Excel worksheets that end with "2016" in their names inside the current folder, "quarter_3."</p>&#13; <p>Looks for additional Excel worksheets that end with "2016" in their names that are located in parallel folders of the "quarter_3" folder. In this example, "quarter_4" is the parallel folder.</p>&#13; <p>In the diagram below, the yellow highlighted item represents current location, that is, the Excel workbook that you created a connection to in the "quarter_3" folder. The green boxes represent the tables belonging to the workbook and worksheets that are unioned as a result of this search criteria.</p>&#13; <li value="1"><b>Include + Expand search to subfolders + Expand search to parent folder:</b> If you enter <b>*2016</b> in the <span class="uicontrol">Include</span> text box and select both the <span class="uicontrol">Expand search to subfolders</span> and <span class="uicontrol">Expand search to parent folder</span> check boxes, Tableau does the following:<ul><li value="1"><p>Looks for all Excel worksheets that end with "2016" in their namesinside the current folder, "quarter_3."</p></li><li value="2"><p>Looks for additional Excel workbooks that are located in the subfolders of the current folder, "quarter_3."</p></li></ul><ul><li value="1"><p>Looks for additional Excel workbooks that are located in parallel folders and subfolders of the "quarter_3" folder. In this example, "quarter_4" is the parallel folder.</p></li></ul><p>In the diagram below, the yellow highlighted item represents the current location, that is, the Excel workbook that you created a connection to. The green box represents the tables belonging to the workbook and worksheets that are unioned as a result of this search criteria. </p><p><img src="Img/union_pattern_subfolders_parent.png" alt=""/></p></li>&#13; <p class="note"><b>Note:</b> When working with Excel data, wildcard search includes named ranges but excludes tables found by Data Interpreter.</p>&#13; <h2 is="heading-item" :level="2" id="rename-modify-or-remove-unions"><a name="manage"/>Rename, modify, or remove unions </h2>&#13; <p>Perform basic union tasks directly in the canvas of the Data Source page.</p>&#13; <div is="accordion-item"><span slot="title">To rename a union</span><div slot="content">&#13; <p>Double-click the logical table that contains unioned physical tables.</p>&#13; <p>Double-click the union table on the physical layer canvas.</p>&#13; <p>Enter a new name for the union. </p>&#13; </div><h2 is="heading-item" :level="2" id="matching-field-names-or-field-ordering"><a name="matching"/>Matching field names or field ordering</h2>&#13; <p>Tables in a union are combined by matching field names. When working with Excel, Google Sheets, text file, JSON file or .pdf file data, if there are no matching field names (or your tables do not contain column headers), you can tell Tableau to combine tables based on the order of the fields in the underlying data by creating the union and then selecting <b>Generate field names automatically</b> option from the union drop-down menu.</p>&#13; <h2 is="heading-item" :level="2" id="metadata-about-unions"><a name="metadata"/>Metadata about unions</h2>&#13; <p>After you create a union, additional fields about the union are generated and added to the grid. The new fields provide information about where the original values in the union come from, including the sheet and table names. These fields are useful when unique information that is critical to your analysis is embedded in the sheet or table name. </p>&#13; <p>For example, the tables used in the example above have unique month and year information stored in the table name instead of in the data itself. In this case, you can use the <span class="uicontrol">Table Name</span> field that is generated by the union to access this information and use it in your analysis.</p>&#13; <p>If a named range is used in a union, null values display under the <b>Sheet</b> field.</p>&#13; <p class="note"><b>Note:</b> You can use the fields generated by a union, such as <b>Sheet</b>or <b>Table Name</b>, as join keys. You can use a unioned table in a join with another table or unioned table.</p>&#13; <h2 is="heading-item" :level="2" id="merge-mismatched-fields-in-the-union"><a name="merge"/>Merge mismatched fields in the union</h2>&#13; <p>When field names in the union do not match, fields in the union contain null values. You can merge the non-matching fields into a single field using the merge option to remove the null values. When you use the merge option, the original fields are replaced by a new field that displays the first non-null value for each row in the non-matching fields. </p>&#13; <p>You can also create your own calculation or, if possible, modify the underlying data to combine the non-matching fields.</p>&#13; <p>For example, suppose a fourth table, "August2016", is added to the underlying data. Instead of the standard "Customer" field name, it contains an abbreviated version called "Cust."</p>&#13; <td><b>August2016</b>&#13; <th class="TableStyle-Basic-Border-HeadE-Column1-Header1">Cust.</th>&#13; <th class="TableStyle-Basic-Border-HeadE-Column1-Header1">Purchases</th>&#13; <p>After you merge fields, you can use the field generated from the merge in a pivot or split, or use the field as a join key. You can also change the data type of the field generated from a merge. </p>&#13; <div is="accordion-item"><span slot="title">To merge mismatched fields</span><div slot="content">&#13; <p> Select two or more columns in the grid. </p>&#13; <p>Click the column drop-down arrow, and then select <b>Merge mismatched fields</b>. </p>&#13; </div><div is="accordion-item"><span slot="title">To remove a merge</span><div slot="content">&#13; <li value="1">Click the column drop-down arrow of the merged field and select <b>Remove merge</b>.</li>&#13; </div><h2 is="heading-item" :level="2" id="at-a-glance-working-with-unions"><a name="glance"/>At a glance: Working with unions</h2>&#13; <h3 is="heading-item" :level="3" id="tableau-desktop-and-web-authoring-tableau-cloud-and-tableau-server"><a name="Tableau"/>Tableau Desktop and web authoring (Tableau Cloud and Tableau Server)</h3>&#13; <p>A unioned table can be used in a join.</p>&#13; <p>A unioned table can be used in a join with another unioned table.</p>&#13; <p>The fields generated by a union, <span class="uicontrol">Sheet</span> and <span class="uicontrol">Table name</span>, can be used as the join key.</p>&#13; <p>If a named range is used in union, null values display under the <span class="uicontrol">Sheet</span> field.</p>&#13; <p>The field generated from a merge can be used in a pivot.</p>&#13; <p>The field generated from a merge can be used as a join key.</p>&#13; <p>The data type of the field generated from a merge can be changed.</p>&#13; <p>Union tables from within the same connection. That is, you cannot union tables from different databases. </p>&#13; <h3 is="heading-item" :level="3" id="tableau-desktop-only"><a name="Tableau2"/>Tableau Desktop only&#13; <p>When working with Excel data, wildcard search includes named ranges but excludes tables found by Data Interpreter.</p>&#13; <p>The field generated from a merge can be used in a pivot or split.</p>&#13; <p>To union a JSON file, it must have a .json, .txt, or .log extension. For more information about working with JSON data, see <a href="examples_json.htm" class="MCXref xref">JSON File</a>.</p>&#13; <p>When using wildcard search to union tables in a .pdf file, the result of the union is scoped to the pages that were scanned in the initial .pdf file you connected to. For more information about working with data in .pdf files, see <a href="examples_pdf.htm" class="MCXref xref">PDF File</a>.</p>&#13; <p>Stored procedures cannot be unioned.</p>&#13;