在Power Automate中实现两个SharePoint列表的左连接：寻求更高效解决方案

Great question—dealing with relational data in Power Automate can get tricky when performance is a concern, especially with nested loops dragging things down. Let’s break down a optimized approach that avoids common performance pitfalls and mimics a MySQL left join effectively:

Core Optimized Workflow #

The key here is to preprocess and group your List B data upfront, so you avoid repeated lookups or nested loops that kill performance. Here’s a step-by-step implementation:

1. Fetch Data Efficiently #

• For both List A (main list) and List B (related list), use the Get items action.
  • Enable Pagination in the action settings (set the threshold to the maximum allowed, e.g., 5000) to minimize SharePoint API calls.
  • Use the Select columns option to only pull the fields you need (no need to load unnecessary data, which slows down data transfer).

2. Group List B by Base_ID #

Add a Compose action to group all List B entries by their Base_ID field. This creates a lookup map where each key is a Base_ID, and the value is an array of matching List B items. Use this expression:

groupBy(body('Get_items_from_List_B'), 'Base_ID', 'RelatedItems')

This transforms your flat List B data into a structured, queryable set—no more repeated filtering for every List A entry.

3. Build the Left Join with Select #

Use a Select action to merge List A with its related List B entries:

• Set the From value to the output of your List A Get items action.
• For each field from List A, map it directly (e.g., Title → @{item()?['Title']}, ID → @{item()?['ID']}).
• Add a new field (e.g., RelatedListBItems) and use this expression to pull matching entries from your grouped List B data:

  if(
    empty(filter(outputs('Compose_Grouped_List_B'), @equals(item()?['Base_ID'], string(items('Select')?['ID'])))),
    createArray(),
    first(filter(outputs('Compose_Grouped_List_B'), @equals(item()?['Base_ID'], string(items('Select')?['ID']))))?['RelatedItems']
  )
  

  This checks for a matching Base_ID (mapped to List A’s ID), returns the array of related List B items if found, and an empty array if not—exactly how a left join works.

Additional Performance Tips #

• Avoid nested Apply to each loops: These are the biggest performance killers for relational data. The grouped lookup approach cuts down processing time from O(n*m) to O(n+m), which is night-and-day for large datasets.
• Cache grouped data: If you need to reuse the grouped List B data elsewhere in your flow, store it in a variable instead of re-running the groupBy expression.
• Batch processing for extreme datasets: If you’re dealing with 10k+ items, split List A into batches using Skip/Take expressions, process each batch separately, then combine results at the end.

When to Consider Alternatives #

If your lists are massive (100k+ items), you might want to offload the join logic to Power Query (via Excel Online or Dataverse) first, then pull the merged dataset into Power Automate. But for most use cases (including your 1:10 relationship), the workflow above will be fast and reliable.

内容的提问来源于stack exchange，提问作者Danielx64