AXEL Table Operations

← Back to Overview

📋 Advanced Table Manipulation

11 powerful functions for table transformation and manipulation.

SUMMARIZE

Group data and calculate aggregations.

SUMMARIZE(
    [Monthly Report],
    [Year],
    "Total", SUM([Monthly Report].First_Class_Mail)
)

Power: SQL GROUP BY equivalent
Use Cases: Pivot tables, data summarization

SUMMARIZE (Multi-Column)

Group by multiple columns.

SUMMARIZE(
    [Monthly Report],
    [Monthly Report].Year,
    [Monthly Report].Month
)

ADDCOLUMNS

Add calculated columns to existing table.

ADDCOLUMNS(
    [Monthly Report],
    "Double", [First_Class_Mail] * 2
)

Use Cases: Extending tables, calculated fields

SELECTCOLUMNS

Select and rename specific columns.

SELECTCOLUMNS(
    [Monthly Report],
    "Period", [Month],
    "Volume", [First_Class_Mail]
)

Use Cases: Column filtering, renaming

PIVOT

Transform rows into columns.

PIVOT(
    [Monthly Report],
    [Month],        -- Rows
    [Year],         -- Columns
    [First_Class_Mail], -- Values
    "SUM"           -- Aggregation
)

Result: Cross-tabulation matrix

UNPIVOT

Transform columns into rows.

UNPIVOT(
    [Monthly Report],
    "Value", "Metric",
    First_Class_Mail, Return_Mail
)

Result: Normalized long format

CROSSJOIN

Create Cartesian product of tables.

CROSSJOIN(
    VALUES([Products].Category),
    VALUES([Stores].Location)
)

Use Cases: Generating all combinations

UNION

Combine rows from multiple tables.

UNION(
    [Table1],
    [Table2]
)

Note: Removes duplicates by default

INTERSECT

Find common rows between tables.

INTERSECT(
    [CurrentCustomers],
    [LastYearCustomers]
)

Use Cases: Finding overlaps

EXCEPT

Find rows in first table but not in second.

EXCEPT(
    [AllProducts],
    [DiscontinuedProducts]
)

Use Cases: Finding differences

💡 Advanced Patterns

Dynamic Grouping

SUMMARIZE(
    [Sales],
    [Product].Category,
    [Date].Year,
    "Revenue", SUM([Sales].Amount),
    "Units", SUM([Sales].Quantity)
)

Table Transformation

SELECTCOLUMNS(
    ADDCOLUMNS(
        FILTER([Sales], [Amount] > 100),
        "Margin", [Amount] * 0.3
    ),
    "Product", [ProductName],
    "Profit", [Margin]
)

Complex Union

UNION(
    SELECTCOLUMNS([Sales2023], "Year", 2023, "Amount", [Total]),
    SELECTCOLUMNS([Sales2024], "Year", 2024, "Amount", [Total])
)

Next: Advanced Analytics →