Power Community

Power Community

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:

ManishSolanki_2-1702729611349.png

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:

ManishSolanki_3-1702729698197.png

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:

ManishSolanki_4-1702729759459.png

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:

ManishSolanki_5-1702729824073.png

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:

ManishSolanki_6-1702729862760.png

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:

ManishSolanki_7-1702729943546.png

Click ‘Switch Map to text mode’ button:

ManishSolanki_8-1702729978856.png

Enter the below expression in the box:

ManishSolanki_9-1702730021261.png

item()?['Task']

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

ManishSolanki_10-1702730072251.png

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:

ManishSolanki_11-1702730132479.png

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:

ManishSolanki_12-1702730177145.png

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:

KeyValue
Taskitem()
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:

ManishSolanki_0-1702731673265.png

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

ManishSolanki_0-1702731776349.png

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

ManishSolanki_1-1702731859073.png

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:

ManishSolanki_2-1702731968547.png

Output email received on completion of flow execution:

ManishSolanki_3-1702731996031.png

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

This post was originally published on this site

- Advertisement -spot_img

LEAVE A REPLY

Please enter your comment!
Please enter your name here

- Advertisement - Advertisement

Latest News

Adjusting the time zone in Power BI DAX – Quick Tip

Hi Folks, Here is the use case, I was actually pulling data from Model Driven App. As I was testing...

More Articles Like This

- Advertisement -spot_img