Collab365

Collection that returns arrays from Power Automate to Power Apps

Q: Why not use direct Excel connection in Power Apps?

Direct Excel connections hit delegation limits (max 2,000 rows), missing data in large tables. Power Automate processes full datasets server-side and returns filtered JSON arrays.

Q: How do I handle spaces in Excel column names?

Use single quotes in Power Fx: ThisRecord.'Site Name'. JSON keys are case-sensitive; match exactly to avoid blank values.

Q: Can I return multiple arrays from one flow?

Yes, format as composite JSON: {"sites": string(...), "departments": string(...)}. Parse nested arrays in Power Apps with Table(ParseJSON(var).sites).

C

Collab365AuthorPublished Apr 22, 2026

4

At a Glance

Target Audience: Power Apps Developers
Problem Solved: JSON parse errors and fragile join() methods when returning arrays from Power Automate flows to Power Apps collections.
Use Case: Populating dynamic dropdowns/galleries in employee onboarding forms with filtered Excel data via Power Automate.

To return an array from Power Automate to Power Apps in 2026, stringify the List rows output in the Respond action, then use ParseJSON and ForAll inside a ClearCollect—no more join() hacks.

We recently reviewed an internal Collab365 forum thread where developer Beth Beck hit a wall. Beth was pulling employee site data (specifically siteName and sitePAU) from an Excel document via a Power Automate flow. This flow was triggered from a Power Apps NewForm. She used an outdated method—join(outputs('Compose_PAU'), '##')—to send the data back.1 The result? Constant JSON parse errors and failed Split/With attempts in the canvas app. Forum contributors James Williams and Larysa Rozmetaniuk correctly pointed out the modern fix. This guide serves as the definitive replacement for that thread, providing complete steps, error tables, and context for 2026.

Key Takeaway: The days of splitting joined strings are over. Passing native JSON arrays from Power Automate and parsing them directly in Power Apps is now the fastest, most reliable method for populating collections.

The Definitive Quick Start

For developers who need the immediate solution to Beth's problem, here is the exact sequence to implement.

TL;DR Box

Follow these 5 steps to successfully return and parse an array:

Filter Excel in flow: Use the Excel Online connector (Data > List rows present in a table) and apply an OData filter query to isolate the required rows.
Format the Respond action: In the "Respond to a Power App or flow" action, return the array wrapped in a string function: string(outputs('List_rows')?['body/value']).
Set variable to flow result: In Power Apps, call the flow and capture the text response: Set(varFlowResult, YourFlow.Run(employeeID).result).
Parse into a Collection: Use this exact formula to parse the JSON and type-cast the columns: ClearCollect(colSites, ForAll(ParseJSON(varFlowResult), {SiteName: Text(ThisRecord.'Site Name'), SitePAU: Text(ThisRecord.'Site PAU')})).
Dropdown Items: colSites.SiteName; OnChange: UpdateContext({selectedPAU: LookUp(colSites, SiteName=Dropdown.Selected.SiteName, SitePAU)}).

Key Takeaway: Wrapping the Excel output in the string() function within Power Automate is the crucial step that prevents "Invalid JSON" errors when the data reaches Power Apps.

What Changed? Old Join vs New ParseJSON

To understand why Beth's app failed, we must look at the history of array handling between these two platforms. Back in 2022, a popular SharePains blog post outlined a workaround for returning arrays without premium licensing.2

The old method involved taking an array of data, isolating the necessary fields, and joining them together using a delimiter that supposedly would never appear naturally in the data, such as ## or ###.2 The flow would return a massive block of text like London##SiteA###Paris##SiteB. Inside Power Apps, developers had to use complex, nested Split() functions to tear this string apart and rebuild it into a table.2

This method was incredibly fragile. If an end-user accidentally typed ## into the Excel spreadsheet, the entire app would crash. Furthermore, handling spaces in column names or managing numeric values required even more convoluted code. The syntax often looked like Index(Split(ThisItem.Result,"###").Result,1).Result, which is a nightmare to maintain.2

In 2023, Microsoft introduced the ParseJSON function as an experimental feature, which allowed canvas apps to ingest valid JSON strings and convert them into untyped objects.3 By 2026, this feature is standard, fully documented, and deeply integrated into the Power Fx language.4

Key Takeaway: The ParseJSON function parses a valid JSON string and returns a Dynamic value representing the JSON structure, allowing direct mapping without manual text splitting.4

Understanding the Architecture of Untyped Objects

Before we jump into the steps, it helps to understand what happens when Power Apps receives this JSON data. When ParseJSON reads a string, it creates an "Untyped object".5 Microsoft defines this as a data type in Power Fx that can hold any data structure, complex or simple.5

Because it is untyped, the application does not inherently know if a field is text, a number, or a boolean value. It simply holds the structure in memory. It cannot be used directly in any Power App control (like a gallery or a dropdown) without explicit conversion.5 Fields in records within an untyped object are accessed using dot notation, and the existence of fields is only verified at runtime.5

This explains why you might see warning symbols in your formula bar during development. Power Apps is warning you that it cannot verify the data type until the app actually runs.

Comparison Table: Legacy vs Modern Approach

The difference between the two approaches is stark. The modern method is faster, cleaner, and strictly adheres to data standards.

Feature	Old Method (2022 SharePains Blog)	Modern Method (2026 ParseJSON)
Data Format	Custom delimited string (Val1##Val2)	Standard JSON Array (``)
Flow Complexity	High (Requires Select and Join actions)	Low (Directly pass Excel output)
App Complexity	High (Nested Split and Index functions)	Low (Single ParseJSON and ForAll)
Type Casting	Manual text conversion only	Native support for Text, Value, Boolean 6
Reliability	Low (Breaks if delimiter exists in raw data)	High (Follows strict ECMA-404 JSON standards) 4
Performance	Slow (Heavy client-side string processing)	Fast (Native Power Fx memory allocation)

When Beth's app failed, it was because the Split/With attempts could not correctly format the dynamic data coming from the Excel connector. We hit this exact snag building a physician onboarding app last year. We used join()—errors everywhere. We switched to ParseJSON—smooth operations immediately.

Key Takeaway: If an existing app still relies on delimited strings to pass arrays, upgrading to ParseJSON should be a priority to prevent unhandled exceptions caused by unpredictable user inputs.

Step 1: Build Your Power Automate Flow

The first phase of the solution requires setting up a Power Automate V2 flow that safely extracts the data from Excel and formats it for transport.

We begin with the PowerApps (V2) trigger. This modern trigger allows developers to explicitly define the inputs required from the canvas app. In this scenario, we define a single text input named employeeID. This ensures the flow only runs when the app provides the correct context. The V2 trigger is much easier to manage than the old V1 trigger, which relied on the "Ask in PowerApps" dynamic content that often caused naming conflicts.

Next, we add the Excel Online (Business) connector. Navigate to Data > List rows present in a table.7 Select the specific site, document library, file, and table containing the employee site data. Note that you must use a formatted table in Excel; the connector cannot read raw worksheet cells.9

To ensure we only retrieve the sites associated with the specific employee, we utilise the Filter Query field. Assuming the Excel column is named HR_EED, the query should be written as HR_EED eq '@{triggerBody()['text']}'. This OData filter pushes the processing load to the server, preventing the flow from downloading the entire spreadsheet into memory.

Key Takeaway: Always use OData filter queries in the "List rows present in a table" action. Pulling thousands of rows into Power Automate and filtering them later consumes excessive memory and causes severe performance degradation.10

The Critical Response Configuration

The most frequent point of failure occurs in the final step: sending the data back. Add the "Respond to a Power App or flow" action. Create a new text output variable. We will name it result.

Do not simply drop the dynamic content for "List rows" into this field. If developers do this, Power Automate attempts to send a raw JSON object, which the Power Apps connector cannot interpret natively, leading to schema mismatch errors.11 The error message will often complain about an expected string but receiving an object.12

Instead, the array must be explicitly converted into a string. Open the expression editor and type: string(outputs('List_rows_present_in_a_table')?['body/value']).13 This expression does two things. First, it targets the value array inside the response body of the Excel action, which strips away the unnecessary OData wrapper that Excel includes. Second, it forces that raw array into a strict text format.

Key Takeaway: The string() wrapper is the secret to this entire architecture. It packages the complex array into a single text variable that safely traverses the boundary between Power Automate and Power Apps.

Dealing with Complex Data in Excel

It is worth noting that Excel Online returns data in very specific ways. Dates, for instance, are often returned as serial numbers (like 44123) rather than ISO date strings. If your Excel table contains dates, you might need to use a Select action in Power Automate before the final response to map and format those dates correctly using the addDays function. For Beth's scenario, we are only dealing with simple text strings (Site Name and Site PAU), so direct stringification of the body/value is perfect.

Step 2: Call Flow from Power Apps OnNewForm

With the flow built and saved, the focus shifts to the Power Apps canvas. Beth was attempting to trigger her data refresh from the OnNewForm property. This is a standard pattern when opening a form to register a new record and requiring dynamic dropdown options based on the user's identity.

To execute the flow and store the resulting JSON string, we use the Set() function to create a global variable. The exact formula is:

Set(varFlowResult, YourFlow.Run(employeeID).result);

We recommend executing this on OnVisible of the screen or OnNewForm of the form control. It is important to note that flow calls are asynchronous. Power Apps will pause execution of the current formula chain until the flow returns a response. Because the heavy lifting (filtering the Excel data) is delegated to the Excel Online connector via the flow, the app itself remains highly responsive.

Key Takeaway: Using a global variable (Set) rather than a context variable (UpdateContext) allows the raw JSON string to be inspected easily on temporary debugging screens during development.

Why Not Just Use ClearCollect Directly?

You might wonder why we separate the flow call into a Set variable before using ClearCollect. While you can technically run the flow directly inside the ParseJSON function, separating it is a best practice for error handling.

By storing the raw string in varFlowResult, you can add a temporary text label to your app and set its text property to varFlowResult. This allows you to visually inspect the exact JSON payload the flow delivered. If the app fails to parse the data, checking this raw string is the fastest way to determine if the error lies in the flow (e.g., it sent back a blank string or an error message) or in the app's parsing logic.

This is where the magic happens and where the forum comments from James and Larysa proved correct. We now have a text variable (varFlowResult) that contains a valid JSON string representing an array of Excel rows.

We must parse this string into an Untyped Object, iterate through it, and cast the required fields into a strongly typed Power Fx collection.4

Here is the full, robust formula:

ClearCollect(colSites, ForAll(ParseJSON(varFlowResult), {SiteName: Text(ThisRecord.'Site Name'), SitePAU: Text(ThisRecord.'Site PAU')}));

Let us break down exactly what this formula does, as understanding the mechanics of Power Fx types is essential for debugging.

ParseJSON(varFlowResult): This function reads the text string and converts it into a Power Fx Untyped Object.4 Because the original Excel data was an array of rows, this object functions as an array in memory.
ForAll(...): We loop through the array. In earlier versions of Power Apps, developers had to wrap the parsed JSON in a Table() function first.3 However, a recent update allows ForAll to iterate directly over untyped objects without requiring the Table() conversion, simplifying the code.15
ThisRecord: Inside the ForAll loop, ThisRecord represents the current JSON object (the current Excel row).16
Column Names with Spaces: Beth's Excel document had spaces in the column headers (Site Name and Site PAU). When accessing properties of an untyped object, Power Fx requires single quotes around any names containing spaces: ThisRecord.'Site Name'.6
Type Casting: Untyped objects cannot be used directly by UI controls like dropdowns.5 They must be explicitly converted to recognised Power Fx types. We use the Text() function to cast the untyped data into standard text strings.4

Key Takeaway: Never attempt to bind an untyped object directly to a gallery or dropdown. Explicit type conversion using functions like Text(), Value(), or Boolean() is strictly required.6

Once the ClearCollect executes, the colSites collection holds a perfectly formatted, strongly typed table.

To populate the dropdown, select the control and set its Items property to colSites. In the properties pane, set the primary text value to SiteName.

If the application requires auto-populating secondary fields based on this selection (for instance, automatically filling in the PAU code when a site is chosen), utilise the OnChange property of the dropdown:

UpdateContext({selectedPAU: LookUp(colSites, SiteName = Dropdown.Selected.SiteName, SitePAU)});

You can then set the Default property of another text input on your form to selectedPAU. This creates a smooth, dynamic user experience where selecting a site instantly populates the associated PAU code, all driven by the clean data retrieved from the flow.

Handling Other Data Types

While Beth only needed text strings, your arrays might contain numbers or true/false values. The untyped JSON is mapped to the type with specific rules: columns that are both in the type and JSON must be coercible.4

If your Excel table has a column for Capacity, you would cast it using the Value() function: Capacity: Value(ThisRecord.Capacity). If you have an IsActive column, use the Boolean() function: ActiveStatus: Boolean(ThisRecord.IsActive).6 Explicitly declaring these types ensures your collection behaves exactly like a native Dataverse or SharePoint table.

Common Errors & Fixes

When implementing this architecture, developers frequently encounter specific roadblocks. According to Collab365, these are the most common failures and their exact fixes.

Error Symptom	Technical Cause	2026 Resolution
"Invalid JSON" error in Power Apps	The flow returned a raw object instead of a string.17	Wrap the dynamic content in the Respond action with string(...).13
Formula editor shows ? next to property name	Power Fx cannot verify the existence of the property at design time because the object is untyped.5	This is expected behaviour. Ignore the warning. Ensure spelling and case perfectly match the source JSON.
Blank values in the resulting Collection	The property name used in ThisRecord.PropertyName does not exist, or case does not match.4	Check the raw JSON string. JSON keys are strictly case-sensitive. Ensure single quotes are used for spaces.
"Expected a Table" error on ForAll	Legacy app settings or complex nested JSON preventing direct iteration.	Wrap the ParseJSON call in a Table() function: ForAll(Table(ParseJSON(var)),...).19
Flow returns 0 rows but Excel has data	The OData filter query is incorrectly formatted.	Check the flow run history. Ensure the employee ID variable is passing correctly and field names match the Excel table definition.

Key Takeaway: If Power Apps cannot parse the data, always inspect the varFlowResult variable on a temporary text label. Validating the raw JSON text visually is the fastest way to spot missing brackets or malformed data.

The Dangers of Null Values

One edge case to watch for is null values in your JSON. If Beth's Excel sheet had a row where the Site PAU column was empty, the JSON array would either omit that key or pass a null value. When Power Apps attempts to parse this, the untyped object will fill in missing columns with a blank.4

However, explicitly coercing a null value into a Text or Value type can sometimes throw runtime errors depending on your exact formula structure. To safeguard against this, you can wrap your type casting in an IfError or Coalesce function, providing a default fallback string if the data is missing.

2026 Updates: Copilot, Premium Connectors, and Alternatives

The Power Platform has evolved significantly since Beth first posted her question. The tooling available in 2026 changes how we approach flow building and data retrieval. Microsoft's continued investment in the intelligence ecosystem has fundamentally shifted the developer experience.

Copilot in Flows and Designer Resiliency

Microsoft has deeply integrated generative AI into the flow creation process. The "Next Best Action" suggestion feature constantly analyses workflow construction and suggests appropriate subsequent actions.20 If a developer struggles to formulate the string() expression, Copilot generative answers can generate the exact syntax required directly within the designer UI.21

Furthermore, the new designer features designer resiliency. Previously, if an expression was malformed, the flow could not be saved. Now, the designer automatically saves a copy of the flow to browser storage even upon a failed save with errors, allowing developers to safely exit and return without losing work.22 This is a massive quality-of-life improvement for developers writing complex JSON schemas.

Excel Online Connector Enhancements

The Excel Online (Business) connector has strict limitations that developers must understand. By default, the "List rows present in a table" action only returns 256 rows.24

To process larger datasets, pagination must be enabled in the action settings. When pagination is activated, the threshold can be increased to 100,000 rows.10 However, pulling massive datasets into Power Automate simply to send them to Power Apps is an anti-pattern.

If your organisation uses Conditional Access policies, ensure that the requirements are consistent between Excel and Power Automate. If policies differ, users might see authentication errors when the flow attempts to read the Excel file.26

Key Takeaway: Always use the OData filter query to restrict the data volume at the source. If pagination is required to return thousands of rows, the architecture should likely be migrated from Excel to Microsoft Dataverse or Azure SQL.27

Why not use the direct Excel Connector in Power Apps?

A common question is why Beth did not simply connect Power Apps directly to the Excel file. Excel as a data source in canvas apps suffers from severe delegation limits.29 By default, non-delegable queries only process the first 500 records.28 While this can be increased to 2,000 in settings, it remains a hard ceiling.30

If Beth's Excel table had 3,000 employees, a direct Filter(EmployeeSites, HR_EED = employeeID) in Power Apps would silently fail to search the bottom 1,000 rows, returning incomplete data.30

By pushing the query to Power Automate, the flow executes the search against the entire Excel table (up to 100,000 rows with pagination) and returns only the necessary subset. The resulting JSON array effortlessly bypasses the Power Apps delegation limits because the data arrives as an in-memory collection rather than a live query.31

Alternatives: Office Scripts and Office 365 Users

While the ParseJSON method is excellent for Excel data, developers should always consider if Excel is the right tool. For employee data, querying the Office365Users connector directly from Power Apps is often more efficient than maintaining a separate Excel spreadsheet.

If you must use Excel and find the standard actions slow, using Office Scripts called via Power Automate can vastly accelerate row updates and data extraction.32 However, for simple array retrieval, the standard "List rows" action combined with ParseJSON remains the most accessible method.

Frequently Asked Questions

When presenting this architecture in workshops, several specific questions consistently arise regarding edge cases and scale.

Why not use the direct Excel connection in Power Apps?

As noted earlier, connecting directly to Excel from Power Apps subjects the application to stringent delegation limits. If the Excel table exceeds 2,000 rows, a direct filter query in Power Apps will silently fail to search records beyond that limit, returning incomplete data.30 Utilising Power Automate allows the Excel Online connector to execute the search server-side across the entire dataset, returning only the filtered JSON array to the app. This offloads the processing power from the user's browser directly to Microsoft's servers.

How do I handle spaces in Excel column names?

When ParseJSON creates an untyped object, it strictly respects the original JSON keys. If the Excel column is named Site Name, the resulting JSON key contains a space. In Power Fx, you must access this property using single quotation marks: ThisRecord.'Site Name'.6 If you omit the quotes, the formula editor will register a syntax error. We strongly recommend removing spaces from your database columns wherever possible to simplify coding, but if you must use them, the single quotes are mandatory.

Can I return multiple different arrays from one flow?

Yes. If the flow needs to return lists of sites, departments, and roles simultaneously, you can format the response as a composite JSON object. Create a custom JSON string in the Respond action: { "sites": string(outputs('List_Sites')?['body/value']), "departments": string(outputs('List_Depts')?['body/value']) } In Power Apps, parse the main object, and then parse the nested arrays individually using Table(ParseJSON(varFlowResult).sites).36 This technique is excellent for loading all reference data when an app first starts, drastically reducing the number of flow calls required.

How does Copilot help with this in 2026?

The 2026 updates introduce generative answers and next best action suggestions directly in the Power Automate designer.21 If a developer is unsure how to extract the body/value from an array, they can simply ask Copilot, "How do I format the Excel list rows output as a string?" Copilot will generate the exact expression required, reducing the need to memorise syntax.21 Copilot can also help generate the ClearCollect formula within Power Apps itself, auto-mapping the JSON keys to your collection schema.

Will this scale to thousands of rows?

Yes, but with caveats. The Power Automate Excel connector can handle up to 100,000 rows if pagination is enabled.25 However, returning an array of 100,000 rows to Power Apps will crash the user's browser due to memory limits. The architecture is designed to search a massive dataset in the cloud but only return a small, targeted array (e.g., the 5 to 50 sites specific to one employee) to the canvas app. Always restrict the output size using the OData filter query to ensure the payload remains lightweight and performant.