UnionByName gem
Use the UnionByName gem to combine rows from multiple datasets by matching column names. This can help when working with data from different sources where schemas might vary slightly in order or structure, but the column names are consistent.
The UnionByName gem has a corresponding interactive gem example. See Interactive gem examples to learn how to run sample pipelines for this and other gems.
Input and Output
| Port | Description |
|---|---|
| in0 | The first input table. |
| in1 | The second input table. |
| inN | Optional: Additional input tables to include in the union. |
| out | A single table containing the combined rows, with columns matched by name. |
To add additional input ports, click + next to Ports.
Parameters
| Parameter | Description |
|---|---|
| Operation Type | Choose between two different operations.
|
Example
Let’s say you’re working with two tables: Table A and Table B.
- Both tables contain order-related data.
- Table B is missing the
amountcolumn that exists in Table A. - The columns in Table B that match Table A are ordered differently.
Table A
| order_id | customer_id | order_date | amount |
|---|---|---|---|
| 101 | 1 | 2024-12-01 | 250.00 |
| 102 | 2 | 2024-12-03 | 150.00 |
| 103 | 1 | 2025-01-15 | 300.00 |
| 104 | 3 | 2025-02-10 | 200.00 |
Table B
| order_id | order_date | customer_id |
|---|---|---|
| 103 | 2025-01-15 | 1 |
| 104 | 2025-02-10 | 3 |
| 105 | 2025-03-05 | 4 |
| 106 | 2025-03-07 | 2 |
Result
The table below shows the output after running a UnionByName gem with Allow Missing Columns enabled.
Since Table B doesn’t have the amount column, Prophecy fills in null values for rows coming from Table B.
| order_id | customer_id | order_date | amount |
|---|---|---|---|
| 101 | 1 | 2024-12-01 | 250.00 |
| 102 | 2 | 2024-12-03 | 150.00 |
| 103 | 1 | 2025-01-15 | 300.00 |
| 104 | 3 | 2025-02-10 | 200.00 |
| 103 | 1 | 2025-01-15 | NULL |
| 104 | 3 | 2025-02-10 | NULL |
| 105 | 4 | 2025-03-05 | NULL |
| 106 | 2 | 2025-03-07 | NULL |
This example will fail if you select No Missing Columns in the gem settings, since Table B is missing a column in Table A.