Transforms all array elements using a formula fragment and returns an array with the results. MAP ({ 1, 2 }, Element * 10) MAP ({ 1; 2 }; Element * 10) multiplies each array element by ten and returns the array { 10, 20 }{ 10; 20 }.

MAP versus array-savvy operators

Transforming { 1, 2 }{ 1; 2 } to { 10, 20 }{ 10; 20 } can more easily be done through this formula:

Most operators can be applied to arrays. This formula returns the array { 1, 2 }{ 1; 2 }:

This formula returns the array { 8, 2 }{ 8; 2 }:

This formula returns the array { FALSE, TRUE, TRUE }{ FALSE; TRUE; TRUE }, because only the last two elements are greater than 7:

Most functions can also be applied to arrays. This formula returns the array { 2, 4 }{ 2; 4 }:

MAP is useful when the transformation is more complex. Consider this formula:

The formula above transforms the array { 10, 20 }{ 10; 20 } into the array { 1000, 200 }{ 1000; 200 }. The first array element 10 is multiplied by 100 because it is not greater than 15, and the second array element 20 is multiplied by 10 because it is greater than 15.

MAP and functions which take arrays

Functions which take arrays, such as SUM, process an array as a whole. Given an array of arrays, SUM adds together the elements of all the arrays. This formula returns 10:

To apply SUM to each inner array instead, MAP must be used. This formula returns the array { 3, 7 }{ 3; 7 }:

FILTER, MAP and REDUCE

FILTER, MAP and REDUCE are commonly used together in a data processing pipeline to transform data. FILTER removes irrelevant elements from an array and MAP transforms the remaining elements. REDUCE, finally, transforms the resulting array to a single value.

Instead of REDUCE, simpler functions can be used to reduce an array of values to a single value. Those include SUM, which adds all array elements together and returns the result, and AVERAGE, which calculates an average of all elements.

This formula calculates the total payroll for employees who worked more than 30 hours in a given time period:

The formula assumes the existence of an Employees table with the following columns:

• Employees!NameEmployees!Name, an array of employee names.
• Employees!HoursWorkedEmployees!HoursWorked, an array representing the number of hours each employee worked.
• Employees!HourlyRateEmployees!HourlyRate, an array containing each employee's hourly rate.

This formula fragment returns an array containing the names of the employees who worked for more than 30 hours:

The filtered array of names is then passed to MAP, which returns an array consisting of the payroll costs of all employees who worked for more than 30 hours:

In this step, MAP transforms each employee name into their total payroll cost using XLOOKUP. First, it is used to look up the number of hours the employee worked and then to fetch the employee's hourly rate.

Finally, SUM is provided the array of payroll costs and returns the total payroll.

Chaining multiple FILTER and MAP invocations

A single formula can use multiple invocations of FILTER and MAP. For instance, an innermost FILTER invocation can filter the raw array once, and then hand this data to MAP, which transforms the filtered data. This data can then, once more, be given to FILTER, which filters out additional elements, and so on.

Consider this formula:

The formula above filters the number array { 326.60, 402.80, 290.00, 128, 3002 }{ 326,60; 402,80; 290,00; 128; 3002 }, which lists prices, leaving only those below 1000. It then applies a 10% markup to each price, keeps only those exceeding 200 and finally returns the sum of these prices.

Working our way outwards from the number array, FILTER is applied to it and uses the formula fragment Element < 1000Element < 1000 to exclude prices of 1000 or more. That leaves { 326.60, 402.80, 290.00, 128 }{ 326,60; 402,80; 290,00; 128 }.

MAP is applied to this array, with the formula fragment Element * 1.1Element * 1,1, which applies a 10% markup to each price: { 359.26, 443.08, 319.00, 140.8 }{ 359,26; 443,08; 319,00; 140,8 }.

FILTER is then applied to this array using this formula fragment, Element > 200Element > 200, which filters out all elements which are not greater than 200. That leaves the array { 359.26, 443.08, 319.00 }{ 359,26; 443,08; 319,00 }. Finally, SUM is applied to this array, returning the grand total 1121.34.