Aggregate JSON/XML data in Power Automate

Written by Micah Groom | Mar 27, 2025 11:15:16 PM

A recent project requirement led me on a path of collecting timesheets from a timekeeping platform, and generating reports so clients can see a weekly per person view of hours worked on various projects. Now, you might think that a quick pivot table or power query in Excel might do the trick. That's until we add in the fact that this is a weekly report. Nobody has time to pull reports every week. And what would the client think if we missed a week. That's where this automation comes in. I'll now be able to collect, process, build, and send these reports to the clients all without lifting a finger. No user errors. No missed weekly reports.

In this guide, I’ll walk through how I automated the process using Power Automate, saving hours of manual work each week.

The Challenge

My team tracks time in ClickTime, but we needed a way to:
✔ Aggregate hours by Job ID for billing.
✔ Sync summarized data to Airtable for reporting.
✔ Avoid duplicate entries when reprocessing weeks.

The Solution: Power Automate Flow

Here’s how the flow works at a high level:


Step 1: Fetch User Records from Airtable

The flow starts by pulling all active users from Airtable, then processes each one in a loop.

  • Airtable HTTP request

  • Parse the returned JSON

  • Loop through the returned records in an "Apply to each" loop


Step 2: Get ClickTime Timesheets per User

For each user, the flow:

  1. Calls the ClickTime API to fetch entries for the specified week.

  2. Parses the JSON response to extract JobIDs, Hours, and Dates.

  • HTTP GET to ClickTime API

  • Parsed JSON output


Step 3: Calculate Total Hours per JobID

This is where the magic happens! The flow:

  1. Uses Select to extract unique JobIDs.

  2. Converts the dataset to XML for XPath queries.

  3. Sums hours per job with:

  • "Select – JobID Repeating" action (showing unique JobIDs).

  • Extract the Unique JobIDs.
  • union(body('Select_-_JobID_Repeating'),body('Select_-_JobID_Repeating'))

  • XML conversion setup

  • XML conversion step
    • xml(variables('varXMLSetup'))

  • Select the matching JobIDs and add the total hours

  • Use this xpath query to collect the hours.
    • xpath(xml(outputs('Set_variable_-_XML_Setup')), concat('sum(//array[contains(JobID, "', item(), '")]/Hours)'))

 


Step 4: Update Airtable with Aggregated Data

Finally, the flow:

  1. Formats the data for Airtable.

  2. Uses a composite key (JobID | WeekNumber | UserID) to prevent duplicates.

  3. Performs a PATCH request to upsert records.

  • Airtable PATCH action


     

Key Learnings

  1. XPath is powerful but tricky: Converting data to XML enabled flexible aggregations.

  2. Concurrency matters: Running loops sequentially (repetitions: 1) avoided API throttling.

  3. Cleanup is critical: Resetting variables after each run prevented data leaks.

Before & After Results

Metric Before (Manual) After (Automated)
Time Spent 3 hours/week 10 minutes/week
Error Rate ~5% 0%

 

Got questions? Ask in the comments!

About the Author

Micah Groom is an IT Consultant who automates everything. Connect on LinkedIn.

Final Flow Screenshot:
View full post