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.

 EmployeeAgeExperience, yearsPosition
Andy Miller253Middle front-end developer
Ashley Stone231.2Junior front-end developer
Jennifer Johnson243.5Middle front-end developer
 EmployeeExperience, yearsPositionAge
Oliver Black4Middle front-end developer27
Peter Jacobs1.5Junior front-end developer24
Thomas Powell6Senior front-end developer30

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.

NameAgeExperience, yearsPosition
Andy Miller253Middle front-end developer
Ashley Stone231.2Junior front-end developer
Jennifer Johnson243.5Middle front-end developer
Oliver Black274Middle front-end developer
Peter Jacobs241.5Junior front-end developer
Thomas Powell306Senior front-end developer
NameJavaScriptJavaPythonCertification
Andy Miller(green star)(green star)(green star)

CERTIFIED

Ashley Stone(green star)(green star)(green star)

UNCERTIFIED

Jennifer Johnson(green star)(green star)(green star)

CERTIFIED

Oliver Black(red star)(red star)(green star)

CERTIFIED

Peter Jacobs(red star)(red star)(star)

UNCERTIFIED

Thomas Powell(green star)(green star)(green star)

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 dateStoreItemsSubtotal
6/30/2021Store #3Date stamp$1040.00
6/29/2021Store #1Date stamp$784.00
6/28/2021Store #2Plastic folder with visiting card holding sheet$2,619.00
6/27/2021Store #3ID card holder$3,128.00
6/26/2021Store #3Letter opener$3,792.00
6/25/2021Store #2Notice board size 3x2fit & 5x3fit$320.00
6/24/2021Store #3Scissors (big)$78.00
6/24/2021Store #2File separator A-4 size$2,254.00
6/23/2021Store #2Stapler 10-D$1,472.00
6/22/2021Store #2Acknowledgement book$3,520.00
6/21/2021Store #3File separator A-4 size$3,441.00
6/20/2021Store #1A4 size plastic  file$3,762.00
6/19/2021Store #2A4 size plastic  file$3,526.00
6/18/2021Store #2Date stamp$3,078.00
6/17/2021Store #1Gel pen$3,268.00
6/16/2021Store #3Magnetic white board duster$1,035.00
6/15/2021Store #2Desk calculator$2,964.00
6/14/2021Store #1Letter opener$3,045.00
6/13/2021Store #2Magnetic white board duster$1,581.00
6/12/2021Store #3Visiting card holder$874.00
6/11/2021Store #2Printing of envelope A-4 size brown $182.00
6/11/2021Store #1Office tray ( in & out )$5,000.00
6/10/2021Store #3Brown paper sheet $2,976.00
6/9/2021Store #1Bubble sheet$3,589.00
6/8/2021Store #2Cello tape 1"$2,160.00
6/7/2021Store #3Non dust chalk$3,772.00
6/6/2021Store #1Eraser$1,176.00
6/5/2021Store #1Key ring$495.00
6/5/2021Store #2Highlighter (yellow,green,orange)$2,726.00
6/4/2021Store #1Brown paper sheet $280.00
6/3/2021Store #1Cello tape 1"$980.00
6/3/2021Store #2Scissors (big)$2,278.00
6/2/2021Store #3Tape dispenser$264.00
6/2/2021Store #3Printing of invitation cards$561.00
6/2/2021Store #1Post it 2 x 3$600.00
6/2/2021Store #2A4 size writing pad spiral$624.00
6/2/2021Store #3Plastic folder A4 size-$1,740.00
6/2/2021Store #3Sticker Sign here$4,150.00
6/1/2021Store #1Paper cutter stand$1,825.00



Priority

LOW

MEDIUM

HIGH




  • No labels