Sort an Array in Fabric Data Pipeline via Pipeline Expressions

Problem Statement

In the Fabric Data Pipeline, there is no dedicated function under regular expressions to Sort an Array.

So is it possible to Sort an Array in Fabric Pipeline?

Prerequisites

Fabric Data Pipeline
Solution
To Sort an Array, we can follow the below approach.

step 1. Create a Pipeline parameter, InputArray, and Pipeline Variables as below.

Pipeline Variables

Step 2. Initially Assign the InputArray parameter value to a variable SourceArray which we would leverage and update based on the Sort Logic.

InputArray parameter

Step 3. Set the Iteration range (equivalent to #1 in Code Snippet).

Iteration range

@range(0, add(length(variables('SourceArray')), -1))

Step 4. Until Activity (Equivalent to While Iteration in Code).

Iteration in Code

Step 5. Within Until Activity.

Until Activity

Step 6. Get the Minimum Index in the Iteration.

Minimum Index

Step 7. Get (Min + 1) Index value.

Index value

@string(add(int(variables('CurrentItemValue')), 1))

Step 8. Compare the Elemental values (Equivalent to #2 in Code Snippet).

Elemental values

@greater(
    variables('SourceArray')[int(variables('CurrentItemValue'))],
    variables('SourceArray')[int(variables('NextItemValue'))]
)

Step 9. In case the initial element is greater than the next element value (TRUE Activities), Swap the values (Equivalent to #3 in Code Snippet).

Initial element

Finally, Reset the iteration ( Equivalent to #4 in Code Snippet).

Reset the iteration

@range(0, add(length(variables('SourceArray')), -1))

Step 10. In case the initial element is less than or equal to the next element value (FALSE Activities).

FALSE Activities

Increment the iteration ( Equivalent to the yellow highlighted section in Code Snippet)

In Data Pipeline, it is not possible to self-reference a variable in Set Variable Activity. Hence, we would need to create a TempArray variable and then override the original array with the TempArray one.

Set Variable Activity

Update

Step 11. The Final Activity Debug is only for debugging purposes to get the sorted value in the last activity log (This can be avoided in real case scenarios as we are mapping the original sorted Array into a new variable).

Final Activity Debug

Result

Numeric

Input

Numeric

Output

String

Input

String

Output

Debug array

Note. To get the Sort in Descending order, we can follow the below logic.

In #8, IF Activity; use the below logic.

@less(variables('SourceArray')[int(variables('CurrentItemValue'))], variables('SourceArray')[int(variables('NextItemValue'))])