# How to Handle Data Transformations in Retool

- Tool: Retool
- Difficulty: Beginner
- Time required: 20-25 min
- Compatibility: Retool Cloud and Self-hosted
- Last updated: March 2026

## TL;DR

Handle complex data transformations in Retool using standalone transformers in the Code tab. Chain .map(), .filter(), and .reduce() for reshaping. Join two queries using a lookup object built from one query's data. Use the bundled Lodash library (_ variable) for grouping, ordering, and multi-source merges. Transformers must return a value and are read-only.

## Complex Data Reshaping Patterns for Retool Apps

Most real-world Retool apps need to combine, reshape, and aggregate data from multiple sources. A sales dashboard might join user records with order history, group by month, and compute running totals. An operations tool might merge data from two APIs and filter by business rules.

This tutorial covers the transformation patterns that power these scenarios: array method chains for single-source reshaping, lookup map joins for merging two queries, reduce-based aggregations for statistics, Lodash utilities for complex grouping, and _.zipWith for parallel array merges. All examples use Retool's standalone transformer pattern.

## Before you start

- Understanding of Retool standalone transformers (Code tab → Transformer)
- JavaScript knowledge: array map, filter, reduce methods
- At least one query returning data in your Retool app
- Optional: familiarity with Lodash (bundled as _ in Retool)

## Step-by-step guide

### 1. Single-Source Transformation: Map/Filter/Reduce Chains

The most common transformation pattern chains .filter(), .map(), and other array methods to reshape a single query's output. Always start with a null guard, then chain operations in a logical order: filter first (reduces dataset), then map (transforms remaining rows).

```
// Transformer: processedOrders
// Input: query1.data = [{ id, user_id, amount, status, created_at, items: [...] }]

const orders = query1.data || [];

return orders
  // 1. Filter: only completed orders
  .filter(order => order.status === 'completed')
  // 2. Map: reshape and add computed fields
  .map(order => ({
    id: order.id,
    userId: order.user_id,
    amount: Number(order.amount || 0),
    amountFormatted: Number(order.amount || 0).toLocaleString('en-US', {
      style: 'currency', currency: 'USD'
    }),
    itemCount: Array.isArray(order.items) ? order.items.length : 0,
    createdDate: moment(order.created_at).format('MMM D, YYYY'),
    dayOfWeek: moment(order.created_at).format('dddd')
  }))
  // 3. Sort by amount descending
  .sort((a, b) => b.amount - a.amount);
```

**Expected result:** The transformer returns a filtered, reshaped, and sorted array of order objects.

### 2. Join Two Queries Using a Lookup Map

The most efficient way to join two datasets in JavaScript is building a lookup object from one, then using it while mapping over the other. This is O(n+m) compared to O(n*m) for nested loops — crucial when both datasets have thousands of rows.

Example: join users (query1) with their aggregate stats (query2).

```
// Transformer: usersWithStats
// query1.data = [{ id, name, email, department }]
// query2.data = [{ user_id, total_orders, total_revenue, avg_rating }]

const users = query1.data || [];
const stats = query2.data || [];

// Build lookup map: O(n)
const statsMap = stats.reduce((map, stat) => {
  map[stat.user_id] = stat;
  return map;
}, {});
// OR with lodash: const statsMap = _.keyBy(stats, 'user_id');

// Join: O(m)
return users.map(user => {
  const userStats = statsMap[user.id] || {};
  return {
    ...user,
    totalOrders: userStats.total_orders ?? 0,
    totalRevenue: Number(userStats.total_revenue ?? 0),
    avgRating: Number(userStats.avg_rating ?? 0).toFixed(1),
    isHighValue: (userStats.total_revenue ?? 0) >= 1000
  };
});
```

**Expected result:** Each user object is enriched with their stats data from the second query, with safe fallbacks for users with no stats.

### 3. Aggregate Data with Reduce

Use .reduce() to compute aggregated statistics across all rows: totals, averages, counts, and maximums. Return an object with computed metrics for binding to stat cards, charts, or summary rows.

```
// Transformer: orderStats
// Computes summary statistics for a dashboard

const orders = query1.data || [];

if (orders.length === 0) {
  return { total: 0, count: 0, average: 0, maxOrder: 0 };
}

const stats = orders.reduce((acc, order) => {
  const amount = Number(order.amount || 0);
  return {
    total: acc.total + amount,
    count: acc.count + 1,
    maxOrder: Math.max(acc.maxOrder, amount),
    completedCount: acc.completedCount + (order.status === 'completed' ? 1 : 0),
    pendingCount: acc.pendingCount + (order.status === 'pending' ? 1 : 0)
  };
}, { total: 0, count: 0, maxOrder: 0, completedCount: 0, pendingCount: 0 });

return {
  ...stats,
  average: stats.count > 0 ? stats.total / stats.count : 0,
  completionRate: stats.count > 0
    ? (stats.completedCount / stats.count * 100).toFixed(1) + '%'
    : '0%'
};

// Access in components:
// {{ orderStats.value.total.toLocaleString('en-US', {style:'currency',currency:'USD'}) }}
// {{ orderStats.value.completionRate }}
```

**Expected result:** orderStats.value contains an object with total revenue, count, average, and completion rate.

### 4. Group Data with Lodash _.groupBy

Use _.groupBy() to split an array into groups by a field value. This is useful for building section headers, grouped charts, or per-category summaries. Lodash is bundled in Retool as the _ variable — no import needed.

```
// Transformer: ordersByDepartment
// Groups users by department for a grouped table

const users = query1.data || [];

// Group by department:
const grouped = _.groupBy(users, 'department');
// Result: { Engineering: [...], Sales: [...], Marketing: [...] }

// Convert to array of group objects with summary stats:
return Object.entries(grouped)
  .map(([department, members]) => ({
    department: department || 'Unassigned',
    memberCount: members.length,
    activeCount: members.filter(m => m.is_active).length,
    totalRevenue: members.reduce((sum, m) => sum + Number(m.revenue || 0), 0),
    members: members.map(m => m.name).join(', ')
  }))
  .sort((a, b) => b.totalRevenue - a.totalRevenue);

// For a chart: bind to bar chart with department on x, totalRevenue on y
```

**Expected result:** The transformer returns an array of department summaries, each with member count and total revenue.

### 5. Merge Parallel Arrays with _.zipWith

When you have two arrays of the same length representing different metrics for the same items (e.g. category names from one query and revenue figures from another), use _.zipWith() to merge them into combined objects. This is common when APIs return separate arrays for labels and values.

```
// Transformer: chartData
// query1.data = [{ month: 'Jan', year: 2024 }, { month: 'Feb' }, ...]
// query2.data = [{ revenue: 12450 }, { revenue: 18200 }, ...]
// Both arrays are same length, ordered identically

const months = query1.data || [];
const revenues = query2.data || [];

// Merge parallel arrays:
return _.zipWith(months, revenues, (month, rev) => ({
  label: `${month.month} ${month.year}`,
  revenue: Number(rev?.revenue || 0),
  revenueFormatted: Number(rev?.revenue || 0).toLocaleString('en-US', {
    style: 'currency', currency: 'USD', minimumFractionDigits: 0
  })
}));

// Alternative without Lodash:
// return months.map((month, i) => ({
//   label: month.month + ' ' + month.year,
//   revenue: Number(revenues[i]?.revenue || 0)
// }));
```

**Expected result:** chartData.value returns an array of combined label+revenue objects ready for a chart component.

### 6. Create Running Totals and Cumulative Sums

Use .reduce() to compute running totals or cumulative sums across an ordered array. This is useful for cumulative revenue charts, stock level tracking, or running balance displays.

```
// Transformer: cumulativeRevenue
// Adds a running total to each row

const salesByDay = query1.data || [];

let runningTotal = 0;

return salesByDay.map(day => {
  runningTotal += Number(day.revenue || 0);
  return {
    ...day,
    dailyRevenue: Number(day.revenue || 0),
    cumulativeRevenue: runningTotal,
    cumulativeFormatted: runningTotal.toLocaleString('en-US', {
      style: 'currency', currency: 'USD', minimumFractionDigits: 0
    })
  };
});

// For a Chart with two series:
// Series 1: x = date, y = dailyRevenue (bar)
// Series 2: x = date, y = cumulativeRevenue (line)
```

**Expected result:** Each row includes both its daily revenue and the cumulative total up to that day.

### 7. Deduplicate and Merge Duplicate Records

When APIs or queries return duplicate records, deduplicate using a Set or _.uniqBy(). For duplicate records where you need to merge data (e.g. sum order amounts for the same user appearing multiple times), use a reduce-based merge approach.

```
// Deduplicate by id (keep first occurrence):
return _.uniqBy(query1.data || [], 'id');

// OR merge duplicates by summing amounts:
const rows = query1.data || [];
const merged = rows.reduce((acc, row) => {
  const existing = acc.find(r => r.user_id === row.user_id);
  if (existing) {
    // Merge: add amounts
    existing.total_amount = (existing.total_amount || 0) + Number(row.amount || 0);
    existing.order_count = (existing.order_count || 0) + 1;
  } else {
    acc.push({
      user_id: row.user_id,
      name: row.name,
      total_amount: Number(row.amount || 0),
      order_count: 1
    });
  }
  return acc;
}, []);

return merged.sort((a, b) => b.total_amount - a.total_amount);
```

**Expected result:** Duplicate records are merged into single entries with combined totals.

### 8. Multi-Source Dashboard Data Transformer

For dashboards pulling from multiple queries, create a single comprehensive transformer that joins and aggregates all data sources into one object. Binding multiple dashboard components to one transformer is cleaner than binding each to individual queries with separate transformations.

```
// Transformer: dashboardData
// Aggregates data from 3 queries for dashboard display
// query1: users, query2: orders, query3: products

const users = query1.data || [];
const orders = query2.data || [];
const products = query3.data || [];

// KPIs
const totalRevenue = orders.reduce((s, o) => s + Number(o.amount || 0), 0);
const activeUsers = users.filter(u => u.is_active).length;
const pendingOrders = orders.filter(o => o.status === 'pending').length;

// Chart data: revenue by month
const revenueByMonth = _.chain(orders)
  .groupBy(o => moment(o.created_at).format('YYYY-MM'))
  .map((monthOrders, month) => ({
    month,
    revenue: monthOrders.reduce((s, o) => s + Number(o.amount || 0), 0)
  }))
  .orderBy('month')
  .value();

// Top products by order frequency
const topProducts = _.chain(orders)
  .flatMap(o => o.items || [])
  .groupBy('product_id')
  .map((items, productId) => ({
    productId,
    count: items.length,
    product: products.find(p => p.id === productId)
  }))
  .orderBy('count', 'desc')
  .take(5)
  .value();

return { totalRevenue, activeUsers, pendingOrders, revenueByMonth, topProducts };
```

**Expected result:** A single dashboardData.value object contains all KPIs and chart-ready arrays for the entire dashboard.

## Complete code example

File: `Transformer: salesDashboard`

```javascript
// Transformer: salesDashboard
// Joins orders (query1) + users (query2) + products (query3)
// Access as {{ salesDashboard.value.kpis }}, {{ salesDashboard.value.topUsers }}, etc.

const orders = query1.data || [];
const users = query2.data || [];
const products = query3.data || [];

// Build lookup maps
const userMap = _.keyBy(users, 'id');
const productMap = _.keyBy(products, 'id');

// Orders enriched with user + product data
const enrichedOrders = orders.map(order => ({
  ...order,
  userName: userMap[order.user_id]?.name ?? 'Unknown',
  userDept: userMap[order.user_id]?.department ?? 'N/A',
  amount: Number(order.amount || 0)
}));

// KPIs
const kpis = {
  totalRevenue: enrichedOrders.reduce((s, o) => s + o.amount, 0),
  orderCount: enrichedOrders.length,
  avgOrderValue: enrichedOrders.length > 0
    ? enrichedOrders.reduce((s, o) => s + o.amount, 0) / enrichedOrders.length
    : 0,
  activeUsers: users.filter(u => u.is_active).length
};

// Revenue by month for line chart
const revenueByMonth = _.chain(enrichedOrders)
  .groupBy(o => moment(o.created_at).format('YYYY-MM'))
  .map((monthOrders, month) => ({
    month,
    revenue: _.sumBy(monthOrders, 'amount')
  }))
  .orderBy('month')
  .value();

// Top 10 users by revenue
const topUsers = _.chain(enrichedOrders)
  .groupBy('user_id')
  .map((userOrders, userId) => ({
    userId,
    name: userMap[userId]?.name ?? 'Unknown',
    revenue: _.sumBy(userOrders, 'amount'),
    orderCount: userOrders.length
  }))
  .orderBy('revenue', 'desc')
  .take(10)
  .value();

return { kpis, revenueByMonth, topUsers, enrichedOrders };
```

## Common mistakes

- **Using nested loops (find inside map) to join two large arrays, causing performance issues** — undefined Fix: Build a lookup object first: const map = _.keyBy(query2.data, 'id'). Then use map[row.id] inside .map() for O(1) lookup instead of O(n) find.
- **Forgetting to return a value from a reduce accumulator function** — undefined Fix: Every callback in .reduce() must return the accumulator. A missing 'return acc;' makes the accumulator undefined on the next iteration, causing cascading errors.
- **Trying to call query.trigger() inside a transformer to refresh data** — undefined Fix: Transformers are read-only and cannot trigger queries. If you need to re-fetch data based on transformation results, use a JS Query with query.trigger() and await.
- **Binding a transformer output to {{ transformer1.data }} instead of {{ transformer1.value }}** — undefined Fix: Transformers expose their return value as .value (e.g. transformer1.value). Using .data returns undefined. Queries use .data; transformers use .value.

## Best practices

- Build lookup maps (using reduce or _.keyBy) before joining — O(n+m) lookup maps dramatically outperform O(n*m) nested loops on large datasets.
- Always add null guards at the start of every transformer: 'const data = query1.data || [];' prevents crashes when queries haven't run yet.
- Use Lodash's _.chain() for complex multi-step transformations — it enables fluent chaining without creating intermediate variables.
- Return early with a meaningful default value if input data is empty: 'if (data.length === 0) return { total: 0, items: [] };'
- Keep transformers focused on one logical output — if a transformer is getting long, split it into two transformers that each serve a specific component.
- Transformers are read-only: they cannot trigger queries, set state, or make async calls. Move any side effects to JS Queries.
- Use _.orderBy(array, ['field'], ['desc']) over .sort() for multi-field sorting — it is more readable and handles null values better.

## Frequently asked questions

### How do I join data from two Retool queries in a transformer?

Build a lookup map from one query using _.keyBy(query2.data, 'id') or reduce(), then map over the other query using that map for O(1) lookups: users.map(user => ({ ...user, ...statsMap[user.id] })). This is more efficient than using .find() inside .map() which is O(n*m).

### Is Lodash available in Retool transformers?

Yes. Lodash is bundled in Retool and available as the global _ variable in all transformers and JS Queries. You do not need to import it. Use _.groupBy, _.keyBy, _.sumBy, _.orderBy, _.chain, and any other Lodash utilities directly.

### Can I use a Retool transformer to aggregate data from more than two queries?

Yes. A standalone transformer can reference any number of queries (query1.data, query2.data, query3.data, etc.). All referenced queries become dependencies and the transformer re-runs whenever any of them return new data. Build lookup maps from each query and join them in a single mapping operation.

### Why is my .reduce() returning undefined instead of an aggregated value?

The most common cause is a missing 'return acc;' inside the reduce callback. Every path through the callback must return the accumulator. Also ensure your initial value (the second argument to reduce) matches the expected output shape — if you expect an object, start with {} not 0.

---

Source: https://www.rapidevelopers.com/retool-tutorial/how-to-handle-data-transformations-in-retool
© RapidDev — https://www.rapidevelopers.com/retool-tutorial/how-to-handle-data-transformations-in-retool