Use Case

Someone posted a problem in community to track attendance in excel sheet and send reminder on MS teams channel with the list of defaulters and missing dates: Solved: Power automate flow for attendance tracking and Se… – Power Platform Community (microsoft.com)

Scenerio

We have an excel sheet for tacking the attendance for the whole month in SharePoint library. The weekends are marked as WO for the entire month:

The power automate cloud flow will run daily to tack the attendance for each user mentioned in the sheet. It will share the list of users and the dates for which attendance is not marked or missing starting from month start to till date.

Solution

1. Create a schedule flow and set the recurrence to daily. Choose the time zone and trigger time as per the need:

2. Next, add “Initialize variable” action to declare an array that holds the list of users & dates for which attendance is not marked. Set ‘defaulterList’ as its name and type as array:

3. Add “List rows present in a table” action to fetch all the records from the excel sheet. Set appropriate values input parameters (Location, Document library, File, Table):

4. Post fetching the records, add “Select” action to create an array of dates from the month start to till date. This will be used later in finding the missing attendance from the excel sheet. We need to write an expression to create that array. Expression needs to be added in the expression box as shown below:

range(1,int(utcNow('dd')))

Similarly, add another expression in the key textbox of Map parameter. Leave the value textbox empty or blank:

concat(item(),'-',utcNow('MMM'))

4. Add another “Select” action to create an array for the name of the columns in the source excel file. This is done to avoid getting reminder for future dates as those are empty and should not be send for reminder:

Expression used in ‘From’ parameter:

xpath(xml(json(concat('{"root":{"items":',body('Select'),'}}'))),'//items')

Click ‘Switch Map to text mode’ button on the right side of Map parameter:

Enter the below expression in the Map textbox:

xpath(item(),'name(/items/*)')

5. Now, add “Apply to each” action and pass value object from the output of list rows present in a table action. We will iterate each record from the excel sheet and check for empty entries or missing attendance:

Inside apply to each block, add “Select” action which will check the missing attendance for each user in a loop. We will be using XML & XPATH to return the array of missing entries for each user in the form of expression:

Expression used for ‘From’ parameter:

xpath(xml(json(concat('{"items":',createArray(item()),'}'))),concat('/items/*[contains(','''',join(body('Select_2'),','),'''',',local-name()) and .='''']'))

Click ‘Switch Map to text mode’ button on the right side of Map parameter:

Enter the below expression in Map textbox. This is to convert number back to number as XML node’s name becomes hexadecimal when name begins with a number:

Add “Condition” action to check if user is a defaulter by comparing the length of output of select array (previous action):

length(body('Select_3'))

In “Yes” block, add “Append to array variable” action to store the name of the user and dates:

{
"Name": @{items('Apply_to_each')?['Name']},
"Day(s)": @{join(body('Select_3'),', ')}
}

Just copy & paste the above JSON code in append to array variable action.

6. Finally, we will add “Condition” action to check if array variable contains any element or empty using an expression. Please note that this action should be added outside the “Apply to each” action block:

length(variables('defaulterList'))

If condition evaluates to true, add “Create HTML table” action for creating table that can be shared in MS teams channel. Dynamically, pass the value of variable to ‘From’ parameter:

Just after creating html table, add “Post message in a chat or channel” action to share the defaulters list in MS teams channel:

Output

Conclusion

Through this article, I have demonstrated how we can make use of XML & XPATH to solve the complex problems which otherwise would not be possible using standard actions.