Table Transformer is one of the macros bundled in the Table Filter, Charts & Spreadsheets for Confluence app. The macro allows you to merge and transform all kinds of tables using both automatic presets and advanced SQL queries.
Sample table #1 - Merge tables preset
Two tables with the same set of columns are merged using the Merge tables preset.
Switch to the page editor and explore the Table Transformer macro configuration.
| Employee | Age | Experience, years | Position |
|---|---|---|---|
| Andy Miller | 25 | 3 | Middle front-end developer |
| Ashley Stone | 23 | 1.2 | Junior front-end developer |
| Jennifer Johnson | 24 | 3.5 | Middle front-end developer |
| Employee | Experience, years | Position | Age |
|---|---|---|---|
| Oliver Black | 4 | Middle front-end developer | 27 |
| Peter Jacobs | 1.5 | Junior front-end developer | 24 |
| Thomas Powell | 6 | Senior front-end developer | 30 |
Sample table #2 - Lookup tables preset
Two tables with one common column are merged using the Lookup tables preset.
Switch to the page editor and explore the Table Transformer macro configuration.
| Name | Age | Experience, years | Position |
|---|---|---|---|
| Andy Miller | 25 | 3 | Middle front-end developer |
| Ashley Stone | 23 | 1.2 | Junior front-end developer |
| Jennifer Johnson | 24 | 3.5 | Middle front-end developer |
| Oliver Black | 27 | 4 | Middle front-end developer |
| Peter Jacobs | 24 | 1.5 | Junior front-end developer |
| Thomas Powell | 30 | 6 | Senior front-end developer |
| Name | JavaScript | Java | Python | Certification |
|---|---|---|---|---|
| Andy Miller |  | | | CERTIFIED |
| Ashley Stone | | | | UNCERTIFIED |
| Jennifer Johnson | | | | CERTIFIED |
| Oliver Black |  | | | CERTIFIED |
| Peter Jacobs | | |  | UNCERTIFIED |
| Thomas Powell | | | | UNCERTIFIED |
Sample table #3 - Custom transformation
In the SQL query tab you can add any custom query to perform complex calculations and any kind of table transformation.
You can find a list of all the available SQL functions here and more examples of custom transformation here.
In this example, we use the following query to calculate and add the Priority column based on some conditions:
SELECT *, CASE WHEN 'Subtotal' < 1000 THEN "LOW" WHEN 'Subtotal' >= 1000 AND 'Subtotal' < 2000 THEN "MEDIUM" ELSE "HIGH" END AS 'Priority' FROM T1
Switch to the page editor and explore the Table Transformer macro configuration.
| Order date | Store | Items | Subtotal |
|---|---|---|---|
| 6/30/2021 | Store #3 | Date stamp | $1040.00 |
| 6/29/2021 | Store #1 | Date stamp | $784.00 |
| 6/28/2021 | Store #2 | Plastic folder with visiting card holding sheet | $2,619.00 |
| 6/27/2021 | Store #3 | ID card holder | $3,128.00 |
| 6/26/2021 | Store #3 | Letter opener | $3,792.00 |
| 6/25/2021 | Store #2 | Notice board size 3x2fit & 5x3fit | $320.00 |
| 6/24/2021 | Store #3 | Scissors (big) | $78.00 |
| 6/24/2021 | Store #2 | File separator A-4 size | $2,254.00 |
| 6/23/2021 | Store #2 | Stapler 10-D | $1,472.00 |
| 6/22/2021 | Store #2 | Acknowledgement book | $3,520.00 |
| 6/21/2021 | Store #3 | File separator A-4 size | $3,441.00 |
| 6/20/2021 | Store #1 | A4 size plastic file | $3,762.00 |
| 6/19/2021 | Store #2 | A4 size plastic file | $3,526.00 |
| 6/18/2021 | Store #2 | Date stamp | $3,078.00 |
| 6/17/2021 | Store #1 | Gel pen | $3,268.00 |
| 6/16/2021 | Store #3 | Magnetic white board duster | $1,035.00 |
| 6/15/2021 | Store #2 | Desk calculator | $2,964.00 |
| 6/14/2021 | Store #1 | Letter opener | $3,045.00 |
| 6/13/2021 | Store #2 | Magnetic white board duster | $1,581.00 |
| 6/12/2021 | Store #3 | Visiting card holder | $874.00 |
| 6/11/2021 | Store #2 | Printing of envelope A-4 size brown | $182.00 |
| 6/11/2021 | Store #1 | Office tray ( in & out ) | $5,000.00 |
| 6/10/2021 | Store #3 | Brown paper sheet | $2,976.00 |
| 6/9/2021 | Store #1 | Bubble sheet | $3,589.00 |
| 6/8/2021 | Store #2 | Cello tape 1" | $2,160.00 |
| 6/7/2021 | Store #3 | Non dust chalk | $3,772.00 |
| 6/6/2021 | Store #1 | Eraser | $1,176.00 |
| 6/5/2021 | Store #1 | Key ring | $495.00 |
| 6/5/2021 | Store #2 | Highlighter (yellow,green,orange) | $2,726.00 |
| 6/4/2021 | Store #1 | Brown paper sheet | $280.00 |
| 6/3/2021 | Store #1 | Cello tape 1" | $980.00 |
| 6/3/2021 | Store #2 | Scissors (big) | $2,278.00 |
| 6/2/2021 | Store #3 | Tape dispenser | $264.00 |
| 6/2/2021 | Store #3 | Printing of invitation cards | $561.00 |
| 6/2/2021 | Store #1 | Post it 2 x 3 | $600.00 |
| 6/2/2021 | Store #2 | A4 size writing pad spiral | $624.00 |
| 6/2/2021 | Store #3 | Plastic folder A4 size- | $1,740.00 |
| 6/2/2021 | Store #3 | Sticker Sign here | $4,150.00 |
| 6/1/2021 | Store #1 | Paper cutter stand | $1,825.00 |
| Priority |
|---|
LOW |
MEDIUM |
HIGH |