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.