Showing results for 
Search instead for 
Did you mean: 

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:




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:




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:




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:



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:




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:


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:

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;


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:



Flow Execution & Output

Trigger the flow by providing required input parameters:



Output email received on completion of flow execution:




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




Very nice!  Thanks for sharing! 💯💯💯💯💯



About the Author
  • Experienced Consultant with a demonstrated history of working in the information technology and services industry. Skilled in Office 365, Azure, SharePoint Online, PowerShell, Nintex, K2, SharePoint Designer workflow automation, PowerApps, Microsoft Flow, PowerShell, Active Directory, Operating Systems, Networking, and JavaScript. Strong consulting professional with a Bachelor of Engineering (B.E.) focused in Information Technology from Mumbai University.
  • I am a Microsoft Business Applications MVP and a Senior Manager at EY. I am a technology enthusiast and problem solver. I work/speak/blog/Vlog on Microsoft technology, including Office 365, Power Apps, Power Automate, SharePoint, and Teams Etc. I am helping global clients on Power Platform adoption and empowering them with Power Platform possibilities, capabilities, and easiness. I am a leader of the Houston Power Platform User Group and Power Automate community superuser. I love traveling , exploring new places, and meeting people from different cultures.
  • Read more about me and my achievements at: MCT | SharePoint, Microsoft 365 and Power Platform Consultant | Contributor on SharePoint StackExchange, MSFT Techcommunity
  • Encodian Owner / Founder - Ex Microsoft Consulting Services - Architect / Developer - 20 years in SharePoint - PowerPlatform Fan
  • Founder of SKILLFUL SARDINE, a company focused on productivity and the Power Platform. You can find me on LinkedIn: and twitter I also write at, so if you want some Power Automate, SharePoint or Power Apps content I'm your guy 🙂
  • I am the Owner/Principal Architect at Don't Pa..Panic Consulting. I've been working in the information technology industry for over 30 years, and have played key roles in several enterprise SharePoint architectural design review, Intranet deployment, application development, and migration projects. I've been a Microsoft Most Valuable Professional (MVP) 15 consecutive years and am also a Microsoft Certified SharePoint Masters (MCSM) since 2013.
  • Big fan of Power Platform technologies and implemented many solutions.
  • Passionate #Programmer #SharePoint #SPFx #M365 #Power Platform| Microsoft MVP | SharePoint StackOverflow, Github, PnP contributor
  • Web site – Youtube channel -