Appending text to an excel row based on value comparison

[Sidhant_02]

Hi everyone,
I have a scheduled flow that gets generated Monday morning and the flow looks like this:

The loop wherein I am adding rows and also checking if the current week does it have any Holiday for which I have maintained a separate list

So now I had one more requirement which was some individuals report to office daily (no hybrid mode) that is their work mode status will be WFO. So for them I wanted to add 'WFO' for all the date columns only for those individuals. So I had created a variable (array type) wherein I mentioned those individuals emails like:

Now in the same loop (Add members to Excel) after checking for Holiday or not I wanted to perform a check if the current email (from the Teams action does it match with any of the email defined in the variable (WFO members) if it does then append 'WFO' else leave it blank.

So I have a expression in my mind which is like:
contains(variables('WFO_Members'), items('Add_Team_Member_to_Excel')?['email'])

But I am not sure whether it is correct and the second thing I am not sure where do I need to place it and How?. And the other thing is if the particular column is already marked as Holiday from the previous check (that particular day like Christmas : 25th Dec)  in that case it should not add the WFO text for the set of employees defined in the variable.

Just an update:
So I have created a variable (Initialize variable: Renamed it to WFO Members) and the actual variable name is set to WFOMembers

Then in the Add_Members_to_Excel (Apply to each loop):
In the existing expression have modified it a bit i.e. made use of another if loop:
(To check if they belong to the WFO members i.e. current record email)

if(contains(join(body('Select'),', '),split(outputs('Header'),',')[3]),'Holiday',if(contains(variables('WFO_Members'),items('Add_Team_Member_to_Excel')?['email']),'WFO'),'')

In the outer if (first if):
Check: If the current weekday is there any Holiday:
if(contains(join(body('Select'),', '),split(outputs('Header'),',')[3]),'Holiday',
 
Then in its else part:
(Check if the current record/item email are they part of WFO_Members array:
if(contains(variables('WFO_Members'),items('Add_Team_Member_to_Excel')?['email']),'WFO'),
 
And if its not satisfied:
Then leave it blank:
''
 

The complete expression in the Row action:
{
  "SNo": "@{variables('varCounter')}",
  "Employee Name": "@{items('Add_Team_Member_to_Excel')?['displayName']}",
  "Employee Email": @{items('Add_Team_Member_to_Excel')?['email']},
  "@{split(outputs('Header'),',')[3]}": "@{if(contains(join(body('Select'),', '),split(outputs('Header'),',')[3]),'Holiday',if(contains(variables('WFOMembers'),items('Add_Team_Member_to_Excel')?['email']),'WFO'),'')}",
  "@{split(outputs('Header'),',')[4]}": "@{if(contains(join(body('Select'),', '),split(outputs('Header'),',')[4]),'Holiday',if(contains(variables('WFOMembers'),items('Add_Team_Member_to_Excel')?['email']),'WFO'),'')}",
  "@{split(outputs('Header'),',')[5]}": "@{if(contains(join(body('Select'),', '),split(outputs('Header'),',')[5]),'Holiday',if(contains(variables('WFOMembers'),items('Add_Team_Member_to_Excel')?['email']),'WFO'),'')}",
  "@{split(outputs('Header'),',')[6]}": "@{if(contains(join(body('Select'),', '),split(outputs('Header'),',')[6]),'Holiday',if(contains(variables('WFOMembers'),items('Add_Team_Member_to_Excel')?['email']),'WFO'),'')}",
  "@{split(outputs('Header'),',')[7]}": "@{if(contains(join(body('Select'),', '),split(outputs('Header'),',')[7]),'Holiday',if(contains(variables('WFOMembers'),items('Add_Team_Member_to_Excel')?['email']),'WFO'),'')}"
}

So I saved the flow and tried to test it manually but got the following issue:

Have I missed something?
So if you guys can help me, it will quite helpful.
If you need any more information do let me know.

Note:
Starting from tomorrow (16/07/2024 to 22/07/2024) probably we might not able to comment on the post as there is some transition that will be happening (from Microsoft) so in that case if comment is disabled, you can message me where we can continue the discussion.

Regards,
Sidhant.

[mmbr1606]

hey @Sidhant_02 

your topic is a better fot for the power automate community to be found here:

Microsoft Power Automate Community - Power Platform Community

Let me know if my answer helped solving your issue.

If it did please accept as solution and give it a thumbs up so we can help others in the community.



Greetings

[Sidhant_02]

Hi @mmbr1606 ,
I will post this query on the Power Automate forum as well.

Regards,
Sidhant.

[Sidhant_02]

Hi,
Quick update have solved the issue that I was facing earlier related to arguments i.e. it expected 3 but got 4:

Earlier:
split(outputs('Header'),',')[3]}": "@{if(contains(join(body('Select'),', '),split(outputs('Header'),',')[3]),'Holiday',if(contains(variables('WFOMembers'),items('Add_Team_Member_to_Excel')?['email']),'WFO'),'')

The 4th argument that was referring to was    'WFO') , ''  <- This was the 4th argument.
General syntax of If is: If (condition, True part, Else part) so earlier I was specifying the else outside the else block which is:

if(contains(join(body('Select'),', '),split(outputs('Header'),',')[3]),'Holiday',
//Else Part: (if-block)
if(contains(variables('WFOMembers'),items('Add_Team_Member_to_Excel')?['email']),
'WFO'  #TRUE PART

            # ELSE PART (WAS MISSING)

)
,'')     #Specified the blank condition outside

Silly mistake 😅, so got to know about this after some time and then fixed this part:

Updated:
split(outputs('Header'),',')[3]}": "@{if(contains(join(body('Select'),', '),split(outputs('Header'),',')[3]),'Holiday',if(contains(variables('WFOMembers'),items('Add_Team_Member_to_Excel')?['email']),'WFO',''))

And now when I executed the flow it was successful.

Just have one issue now for the first header i.e. 16-07, in the flow it did append WFO for that date i.e. 16-07-2024 but in the sheet it was not reflected

Updated Expression:

{
  "SNo": "@{variables('varCounter')}",
  "Employee Name": "@{items('Add_Team_Member_to_Excel')?['displayName']}",
  "Employee Email": @{items('Add_Team_Member_to_Excel')?['email']},
  "@{split(outputs('Header'),',')[3]}": "@{if(contains(join(body('Select'),', '),split(outputs('Header'),',')[3]),'Holiday',if(contains(variables('WFOMembers'),items('Add_Team_Member_to_Excel')?['email']),'WFO',''))}",
  "@{split(outputs('Header'),',')[4]}": "@{if(contains(join(body('Select'),', '),split(outputs('Header'),',')[4]),'Holiday',if(contains(variables('WFOMembers'),items('Add_Team_Member_to_Excel')?['email']),'WFO',''))}",
  "@{split(outputs('Header'),',')[5]}": "@{if(contains(join(body('Select'),', '),split(outputs('Header'),',')[5]),'Holiday',if(contains(variables('WFOMembers'),items('Add_Team_Member_to_Excel')?['email']),'WFO',''))}",
  "@{split(outputs('Header'),',')[6]}": "@{if(contains(join(body('Select'),', '),split(outputs('Header'),',')[6]),'Holiday',if(contains(variables('WFOMembers'),items('Add_Team_Member_to_Excel')?['email']),'WFO',''))}",
  "@{split(outputs('Header'),',')[7]}": "@{if(contains(join(body('Select'),', '),split(outputs('Header'),',')[7]),'Holiday',if(contains(variables('WFOMembers'),items('Add_Team_Member_to_Excel')?['email']),'WFO',''))}"
}

Almost there just need some help in debugging this last minor issue now!🙂

Regards,
Sidhant