How to transform data stored in SharePoint list using Power Automate?

Use Case

In this article, we will learn how we could transform data in SharePoint List using power automate cloud flow. We will take an example of transforming data in SharePoint to generate timecard or timesheet.

Scenario & Sample Input data

All tasks & corresponding hours performed by the users are stored in custom SharePoint list "TaskDetails". Using flow, we will transform these raw entries into a shape of timecard or timesheet.

The typical task entries of a user for a week are shown below:

Solution

1. Create a manual flow with 'StartDate' & 'UserEmail' as the input parameters. First parameter will accept the start date of the week (generally Monday). The other parameter will take the email address of the user whose timecard needs to be created:

2. Next, add "Get items" action to fetch list items from SharePoint. We will apply filter query to fetch entries for a particular week and a user:

WorkDate ge '@{triggerBody()['date']}' and WorkDate le '@{addDays(triggerBody()['date'],4,'yyyy-MM-dd')}' and User/EMail eq '@{triggerBody()['text']}'

3. Now, using "Select" action we will select only 3 fields or columns which are required for creating timecard or timesheet:

4. Add "Compose" action to create XML using expression from the output of "Select" action. Expression needs to be added in the expression window as highlighted below in the screenshot:

xml(json(concat('{Root:{Item:',body('Select'),'}}')))

5. Add another "Select" action to collect all the tasks in an array. Pass output of previous Select action in the 'From' parameter:

Click 'Switch Map to text mode' button:

Enter the below expression in the box:

item()?['Task']

6. Similarly, add another "Select" action to collect all the dates in an array from the SharePoint list:

Expression used in Map parameter in the above screenshot:

item()?['Date']

7. Add "Compose" action to get the unique dates from the SharePoint list. We will apply union function in an expression to get the unique values:

union(body('Select_3'),body('Select_3'))

8. Add "Select" action to transform an array to get the desired result. Expressions are used in all input parameters:

Expression used for "From" parameter:

union(body('Select_2'),body('Select_2'))

Except for first map key value, for all keys & values expression has been used:

Key	Value
Task	item()
formatDateTime(outputs('Compose_2')?[0],'MMM dd, ddd')	xpath(outputs('Compose'),concat('string(//Task[text()=','''',item(),'''',']/..//Date[text()=','''',outputs('Compose_2')?[0],'''',']/..//Hours/text())'))
formatDateTime(outputs('Compose_2')?[1],'MMM dd, ddd')	xpath(outputs('Compose'),concat('string(//Task[text()=','''',item(),'''',']/..//Date[text()=','''',outputs('Compose_2')?[1],'''',']/..//Hours/text())'))
formatDateTime(outputs('Compose_2')?[2],'MMM dd, ddd')	xpath(outputs('Compose'),concat('string(//Task[text()=','''',item(),'''',']/..//Date[text()=','''',outputs('Compose_2')?[2],'''',']/..//Hours/text())'))
formatDateTime(outputs('Compose_2')?[3],'MMM dd, ddd')	xpath(outputs('Compose'),concat('string(//Task[text()=','''',item(),'''',']/..//Date[text()=','''',outputs('Compose_2')?[3],'''',']/..//Hours/text())'))
formatDateTime(outputs('Compose_2')?[4],'MMM dd, ddd')	xpath(outputs('Compose'),concat('string(//Task[text()=','''',item(),'''',']/..//Date[text()=','''',outputs('Compose_2')?[4],'''',']/..//Hours/text())'))

 

9. Now, add "Create html table" action to create timecard table:

Optional: To give nice formatting to table, add another "Compose" action & enter the css:

<style>
Table {
 font-family: Arial, Helvetica, sans-serif;
 border-collapse: collapse;
 width: 100%;
}

Table td, Table th {
 border: 1px solid #ddd;
 padding: 3px 3px;
}

Table th {
 font-size: 14px;
 font-weight: bold;
 padding-top: 12px;
 padding-bottom: 12px;
 text-align: left;
 background-color: #1C6EA4;
 color: white;
}
</style>

 

10. Finally, add send an email action to share the timecard or timesheet to the user:

Enter the below text in Subject:

Time Entries - @{formatDateTime(triggerBody()['date'],'MM/dd/yyyy')} to @{addDays(triggerBody()['date'],4,'MM/dd/yyyy')}

Enter the below text in Body:

@{outputs('Compose_5')}@{body('Create_HTML_table')}

 

Flow Execution & Output

Trigger the flow by providing required input parameters:

 

Output email received on completion of flow execution:

 

Conclusion

Following are the takeaway:

• Compose & select actions with expression could be used to transform view of the data.
• Method to generate the timecard or timesheet from the entries stored in SharePoint list.
• This flow could be used with powerapps trigger which would deliver the timecard or timesheet to user mailbox from button click in canvas app