Skip to main content

Notifications

Power Automate - General Discussion
Unanswered

Appending Text to an excel row based on value comparison

(0) ShareShare
ReportReport
Posted on by 986

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

Sidhant_02_0-1721046402368.png

Sidhant_02_1-1721046910741.png

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

Sidhant_02_2-1721046985575.png

Sidhant_02_3-1721047004187.png


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:

Sidhant_02_4-1721047181626.png

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.

Update:
So I did try to update my existing flow with the changes that I mentioned above like:
Added a initialize variable action : With 3 strings (emails)

Sidhant_02_0-1721119526033.png

 

Then in the Add Members to Excel loop (Apply to each):

Previous code:
(In Row action)
//Here I was only checking if the current week if there are any Holidays as per the Holiday List then append Holiday for that column else leave it blank
if(contains(join(body('Select'),', '),split(outputs('Header'),',')[3]),'Holiday','')

//Now in the else block I modified it slightly:
if(contains(join(body('Select'),', '),split(outputs('Header'),',')[3]),'Holiday',if(contains(variables('WFO_Members'),items('Add_Team_Member_to_Excel')?['email']),'WFO'),'')

Working:
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 whole expression looks like:

{
 "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 my flow and tested (manually), got this issue (in the loop)

Sidhant_02_1-1721119866179.png


Is there anything that I missed?

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.

  • Sidhant_02 Profile Picture
    Sidhant_02 986 on at
    Appending Text to an excel row based on value comparison
    Hi,
    I even tried adding a IF-Block (that checks whether the current user Email is part of the WFO mode) like:




    Hybrid: (That are not listed in the Select array which is defined before the loop)


    So in this case as well it skips the first date column in the Add a row action in its outputs like:
    I tested the flow so first date was 31/07/2024 WFO was not appended for the members in WFO members list and for other days i.e. 01/08 to 04/08 it is added (which what I mentioned earlier)

     
  • Sidhant_02 Profile Picture
    Sidhant_02 986 on at
    Appending Text to an excel row based on value comparison
    Hi,
    If anyone has any idea why the 1st day of the week is being missed in the Add row excel action do let me know, for more information do check my last reply (wherein I have corrected the part that I had missed which is answer to main query question)

    And this was also present in the add a row into excel but in the sheet it's not been added (the WFO text)

    So I am confused what the issue is and till now have not found a solution for it, if you can suggest me any solution it will of good help.

    Regards,
    Sidhant.
  • Sidhant_02 Profile Picture
    Sidhant_02 986 on at
    Re: Appending Text to an excel row based on value comparison

    Hi @SudeepGhatakNZ ,
    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.

     

    In the flow I could see the 'WFO' text was added:

    Sidhant_02_0-1721134398768.png

    But in the sheet for 16/07/2024 it was not added (for rest it was there)

    Sidhant_02_1-1721134515914.png

     

     

    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',''))}"
    }

     

    Have got the solution working just need some small help regarding this issue .🙂

    Regards,
    Sidhant.

  • SudeepGhatakNZ Profile Picture
    SudeepGhatakNZ 14,231 on at
    Re: Appending Text to an excel row based on value comparison

    @Sidhant_02 try this

     

    {
    "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',''))}"
    }
  • Sidhant_02 Profile Picture
    Sidhant_02 986 on at
    Re: Appending Text to an excel row based on value comparison

    Hi @SudeepGhatakNZ ,
    Thanks for verifying, I have posted an updated in my post wherein I am facing one issue can you please have a look at it.

    Regards,
    Sidhant.

  • SudeepGhatakNZ Profile Picture
    SudeepGhatakNZ 14,231 on at
    Re: Appending Text to an excel row based on value comparison

    @Sidhant_02 

    Your syntax is correct.

    contains(variables('ArrayString'),triggerBody()['text']).
    I have tested it and it works.
    SudeepGhatakNZ_0-1721094871852.png

     

     

     

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,445

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,741

Leaderboard