Mapping Data Flow Expresson (IIF and IN)

I can't for the life of me get this syntax create for a mapping data flow expression:

iif(in(GroupingName, ['Item1', 'Item2', 'Item3'])
,   replace(
        unescape(
            replace(
                replace(toString(collect(KeyValuePair))
                , '[', '{'
                )
            , ']', '}'
            )
        , 'json'
        )
    , '""', '"'
    )
,   collect(KeyValuePair)
)

The issue appears to be the syntax with the iif and in. The error I get back is columns should be wrapped in an aggregate function

Columns should be wrapped with aggregate function

The above occurs when you use a column in the aggregate transformation without wrapping in any aggregate functions like first(), avg(), collect() ,.etc. Here, In the IN() function, you have directly used column name GroupingName and that is the reason why it didn't give any error when you remove the iif(in()) expression.

To check the condition with normal column, you can do that after the aggregate transformation.

First get the collect(KeyValuePair) in a column from the aggregate transformation

Now, use your expression in a derived column to check the condition on your column. Here, in your original expression, the else expression will give an array and if expression will give a string. In the iif() both results from if and else should give the same data types, so you can modify the else part like below.

iif(in(['Item1', 'Item2', 'Item3'],GroupingName)
,   replace(
        unescape(
            replace(
                replace(toString(new)
                , '[', '{'
                )
            , ']', '}'
            )
        , 'json'
        )
    , '""', '"'
    )
,   toString(new)
)

You can see it gave the expected results when the GroupingName value is not in the provided array.