Forum Discussion
Help Needed: Merging Email Lists from Lookup and Security Groups in a Fabric Pipeline
Hi,
I’m building a pipeline in Microsoft Fabric that aims to consolidate an email list from two sources: (1) a static list retrieved through a Lookup activity and stored in the emailsListArray variable, and (2) email addresses of members from multiple security groups, whose names also come from the same Lookup. To handle this, I use a ForEach loop that iterates over the group names, queries each group’s ID using a web activity (get_group_id), then fetches the group members using another call (get_group_members), and stores the result in the groupEmailsTemp variable. After the loop, I try to merge all the email addresses into a single variable, finalEmailsList, using a union() between emailsListArray and groupEmailsTemp. However, the pipeline currently only returns emails from the first group and ignores the rest.
I cannot use select() or nested ForEach, and I’m also restricted from self-referencing variables. Has anyone successfully solved a similar scenario in Microsoft Fabric under these constraints?
Thanks for the help.
Liney B
Hi,
Thanks for the response. I was finally able to resolve it as follows:
After the web activity that retrieves the members of the security groups, I added a notebook that processes the results and extracts the "mail" field from each member. This allowed me to generate a list of email addresses associated with the security groups.
Then, I added two "Set variable" activities:
The first one assigns the list of emails to a temporary variable inside the forEach loop.
The second one accumulates the emails into an accumulator variable, which allowed me to build the complete list of emails outside the forEach.
This approach enabled me to successfully retrieve all the email addresses from the security groups and use them in the subsequent steps of the pipeline.
6 Replies
Hi,
Thanks for the response. I was finally able to resolve it as follows:
After the web activity that retrieves the members of the security groups, I added a notebook that processes the results and extracts the "mail" field from each member. This allowed me to generate a list of email addresses associated with the security groups.
Then, I added two "Set variable" activities:
The first one assigns the list of emails to a temporary variable inside the forEach loop.
The second one accumulates the emails into an accumulator variable, which allowed me to build the complete list of emails outside the forEach.
This approach enabled me to successfully retrieve all the email addresses from the security groups and use them in the subsequent steps of the pipeline.
v-lgarikapatCommunity Support
Hi liney_marcela ,
Thanks for sharing the workaround really appreciate it! It's super helpful for others in the community as well.
Best Regards,
Lakshmi Narayana
- v-lgarikapat
Hi liney_marcela ,
Thanks for reaching out to the Microsoft fabric community forum.
Below are the workarounds
Instead of trying to union inside the loop, accumulate emails incrementally using a Set variable activity with @concat ().
Initialize Variables:
emailsListArray → from your static Lookup.
groupEmailsAccumulator → type: Array, default: [].
Inside the ForEach (over group names):
get_group_id → fetch group ID.
get_group_members → fetch members.
Add a Set variable activity:
Target: groupEmailsAccumulator
Value:
@concat(variables('groupEmailsAccumulator'), activity('get_group_members').output.value)
This appends the current group’s emails to the accumulator.
After the ForEach:
Use another Set variable to create finalEmailsList:
@union(variables('emailsListArray'), variables('groupEmailsAccumulator'))
For References
Expressions and functions - Microsoft Fabric | Microsoft Learn
https://learn.microsoft.com/en-us/fabric/data-factory/pipeline-rest-api-capabilities
Module 3 - Automate and send notifications with Data Factory - Microsoft Fabric | Microsoft Learn
Solved: Re: Issues configuring a pipeline in Fabric to obt... - Microsoft Fabric Community
Solved: Re: Outlook Step for email on Failure didn't activ... - Microsoft Fabric Community
If you are still encountering any challenges, we would be happy to assist you further.
Best Regards,
Lakshmi Narayana
Thank you very much for your response and for sharing this alternative!I followed the steps you suggested: I initialized the variables emailsListArray (with the static list from Lookup) and groupEmailsAccumulator (as an empty Array []).
Inside the ForEach over the group names, I added the get_group_id and get_group_members activities.
Then I added the Set variable activity to accumulate each group’s emails, using this expression as value:
@concat(variables('groupEmailsAccumulator'), activity('get_group_members').output.value)
However, I ran into two issues:1. The Set variable activity inside the ForEach fails because the expression self-references the target variable (groupEmailsAccumulator), which isn’t allowed (cannot reference the variable being set).
2. The Set variable activity after the ForEach that creates finalEmailsList using:
union(variables('emailsListArray'), variables('groupEmailsAccumulator'))
shows this warning:
Expression of type: 'Array[Any]' does not match the field: 'value'. Expected type is 'object'Do you have any suggestions on how to work around these limitations, or maybe another way to achieve this?
Thanks again for your help and your time!- v-lgarikapat
Hi liney_marcela ,
Apologies for the delayed response.
Could you please try the following workarounds?To fix the self-referencing error, create a temporary variable called tempAccumulator and inside the ForEach loop, first assign it the current value of groupEmailsAccumulator, then use tempAccumulator to build the new value by appending the current group’s members with @concat(...), which avoids referencing the variable being set. As for the type mismatch error when using @union, switch to an Append variable activity to build your final array gradually, or if you're stuck using Set variable, wrap the union result in an object like { "value": @union(...) }
If you're still facing challenges, feel free to let us know we’ll be glad to assist you further.
Looking forward to your response.
Best regards,
LakshmiNarayana.