Group and Concatenate CSV Data Using XML in Power Automate

Table of Contents

• Introduction
• Business Scenario
• CSV Data Structure
• Step-by-Step Implementation
  • 1. File Upload Trigger
  • 2. Get File Content
  • 3. Parse CSV Data
  • 4. Filter Data
  • 5. Extract Unique Combinations
  • 6. XML Grouping
  • 7. Concatenate Users
  • 8. Create SharePoint Items
• Complete Flow Overview
• References

Introduction

This guide demonstrates how to use Power Automate to group CSV data and concatenate users by Team and Channel using XML processing techniques to save to a SharePoint list.

What you’ll accomplish:

• Process CSV files containing team/channels and user data
• Group users by Team and Channel combinations
• Use XML and XPath to concatenate related data
• Create consolidated SharePoint list items
• Automate file processing workflows

Business Scenario

You have a CSV file containing team membership data that needs to be consolidated into SharePoint list items. Instead of creating separate items for each user, you want to group users by their Team and Channel, then create single items with concatenated user lists.

Example transformation:

• Input: Multiple rows per team/channel combination
• Output: Single SharePoint item per team/channel with all users concatenated

CSV Data Structure

The input CSV file contains the following columns:

Sample CSV data:

TeamName,ChannelName,Action,Role,UserEmail
Marketing Team,General,Add,Owner,john@company.com
Marketing Team,General,Add,Owner,mary@company.com
Sales Team,General,Add,Owner,bob@company.com
Marketing Team,Campaigns,Add,Owner,john@company.com

Step-by-Step Implementation

1. Trigger: File Upload

• Use the “When a file is created (properties only)” SharePoint trigger to monitor the folder where your files are uploaded.

2. Settings (Parse JSON)

• Initialize settings like site URL, list name, batch size, and oData level.

Sample Content:

{
  "tenantBaseUrl": "4g6zf4.sharepoint.com",
  "sitePath": "sites/Products",
  "listName": "OwnersMapping",
  "batchSize": 1000
}

Sample Schema:

{
    "type": "object",
    "properties": {
        "tenantBaseUrl": {
            "type": "string",
            "title": "Base URL of SharePoint Site",
            "description": "Base URL of SharePoint Site"
        },
        "sitePath": {
            "type": "string",
            "title": "Relative path of the site"
        },
        "listName": {
            "type": "string",
            "title": "Sharepoint List",
            "description": "List where items are to be batch created"
        },
        "batchSize": {
            "type": "integer",
            "title": "Batch Size",
            "description": "Number of items to send in a single batch"
        }
    }
}

3. Get File Content

• Use “Get file content” to retrieve the uploaded file’s data.
• Site Address: @{concat('https://',body('Settings')?['tenantBaseUrl'],'/',body('Settings')?['sitePath'])}
• File Identifier: @{triggerOutputs()?['body/{Identifier}']}

4. Parse CSV

• For CSV: Use a Compose action: @{base64ToString(body('Get_file_content')?['$content'])}

4. Filter Data

Filter the CSV rows to include only records with “Owner” role and non-empty team names.

Action: Filter array Configuration:

• From: @{skip(split(trim(outputs('ParseCSV_data')),decodeUriComponent('%0A')),1)}
• Filter Query:

@{and(
  not(equals(trim(split(item(), ',')[0]), '')),
  equals(trim(split(item(), ',')[3]), 'Owner')
)}

Expression breakdown:

• skip(..., 1) - Skips the header row
• split(..., '%0A') - Splits by newlines
• trim(split(item(), ',')[0]) - Gets the team name (first column)
• split(item(), ',')[3] - Gets the role (fourth column)

5. Extract Unique Combinations

Create unique Team/Channel combinations to avoid duplicates.

Step 5a: Select Team and Channel Action: Select Configuration:

• From: @{body('Filter_array')}
• Map:
  • Team: @{split(item(),',')[0]}
  • Channel: @{split(item(),',')[1]}

Step 5b: Get Unique Combinations Action: Compose Name UniqueTeamChannel Expression: @{Union(body('Select'),body('Select'))}

What this does: The Union function removes duplicate Team/Channel combinations, ensuring each unique pair is processed only once.

6. Generate SharePoint Data Structure

Prepare the filtered data for XML processing.

Action: Select Name GenerateSPData Configuration:

• From: @{body('Filter_array')}
• Map:
  • Team: @{split(item(),',')[0]}
  • Channel: @{split(item(),',')[1]}
  • User: @{split(item(),',')[4]}

7. XML Grouping

Convert the data to XML format for advanced grouping operations.

Step 7a: Create Root Structure Action: Compose Name Compose_Root Configuration:

{
  "root": {
    "values": @{body('GenerateSPData')}
  }
}

Step 7b: Convert to XML Action: Compose Name XML Expression: @{xml(outputs('Compose_Root'))}

Why XML? XML format allows us to use XPath queries for sophisticated data grouping and filtering operations that aren’t easily achievable with standard Power Automate functions.

8. Concatenate Users by Team/Channel

Group users by Team/Channel combination using XPath queries.

Action: Select Name ConcatenatedUsers Configuration:

• From: @{outputs('UniqueTeamChannel')}
• Map:
  • Team: @{item()['Team']}
  • Channel: @{item()['Channel']}
  • Users:

@{join(
  xpath(outputs('xml'), 
    concat('//root/values[Team="',item()['Team'],'" and Channel="',item()['Channel'],'"]/User/text()[1]')
  ),
  ';'
)}

XPath explanation:

• //root/values[...] - Finds all values elements under root
• Team="..." and Channel="..." - Filters by specific team and channel
• /User/text()[1] - Extracts the user email text
• join(..., ';') - Concatenates all matching users with semicolon separator

9. Create SharePoint Items

Create SharePoint list items with the grouped and concatenated data.

Add Apply to each action referenced the output from previous actions

Action: Apply to each Configuration:

• Select output from previous steps: @{body('ConcatenatedUsers')}

Add create item action

Inside the loop - Create item:

• Site Address: @{concat('https://',body('Settings')?['tenantBaseUrl'],'/',body('Settings')?['sitePath'])}
• List Name: @{body('Settings')?['listName']}
• Title: @{item()?['Team']}
• Channel: @{item()?['Channel']}
• Owners: @{item()?['Users']}

Result: Each SharePoint item represents one Team/Channel combination with all users concatenated in a single field.

Complete Flow Overview

The complete flow performs these key operations:

1. File Detection → Monitors for CSV uploads
2. Data Parsing → Converts CSV to processable format
3. Data Filtering → Extracts only Owner records
4. Deduplication → Identifies unique Team/Channel pairs
5. XML Conversion → Enables advanced grouping
6. User Concatenation → Groups users by Team/Channel
7. SharePoint Creation → Creates consolidated list items

References

• Power Automate Group By, Sum & Count Without Apply to Each – Efficient Expense Workflow

• PowerAutomate
• SharePoint
• XML
• CSV
• DataGrouping