Power Platform Community Forum Thread Details

Using Variable as a DataSource

Hi,

I would like to use a variable as a datasource in a ForAll loop but cannot work out if this is possible or not.

This is what I have so far - I am creating a collection which has the variable value in it that I need to use.

The variable needs to be in the FILTER Datasource - below it shows as 'Shipments-FY2024' - it could be 'Shipments-FY2019' 2020, 2021 etc. Each of the Datasources exist as their own dataverse tables in my app.

ClearCollect(
    colDS,
    Filter(
        tblYears,
        IsActive = true
    )
);
Clear(colTest);
ForAll(
    colDS As DataLoop,
    Collect(
        colTest,
        Sort(
            Filter(
                'Shipments-FY2024',
                Solution = "CompanyName"             ),
            NetRevenueUSD,
            SortOrder.Ascending
        )
    )
)
 
I have no errors with the above but the datasource is static - I need it to change for each year.
 
The following gives me a Invalid Data Type which then of course errors on the Solution = "CompanyName" as Solution does not exist.
 
ForAll(
    colDS As DataLoop,
    Collect(
        colTest,
        Sort(
            Filter(
                DataLoop.YearDataSource,
                Solution = "CompanyName"             ),
            NetRevenueUSD,
            SortOrder.Ascending
        )
    )
)
 
I have tried changing the value that is stored in the tblYears e.g. single quotes in the field 'Shipments-FY2024' or just plain Shipments-FY2024.
 
Any hints from anyone on how to make the variable be seen as a dataverse table(datasource) is appreciated.
 
I looked at using SET / UpdateContext but they are not allowed in a ForAll.
 
My current work around is using a Switch for each year - but this will require maintenance for each year.

Categories:

Design & build

@Gargoolgala

You're trying to dynamically use a variable as a data source in a ForAll loop in Power Apps, where each value in your colDS collection represents a different Dataverse table name (e.g., 'Shipments-FY2024', 'Shipments-FY2019', etc.). Unfortunately, Power Apps does not support dynamic table references directly using variable values inside ForAll or Filter.

❌ Why Your Approach Fails

In Power Apps, data sources must be statically defined at design time. You cannot use a string variable like DataLoop.YearDataSource to dynamically reference a table name. This is why you're getting an invalid data type error—Power Apps is treating DataLoop.YearDataSource as a string, not as a table.

✅ Workaround Options

1. Use a Switch Statement (Your Current Approach)

Although it requires maintenance, this is currently the only supported method to handle dynamic table selection:

Switch(

DataLoop.YearDataSource,

"Shipments-FY2024", Filter('Shipments-FY2024', Solution = "CompanyName"),

"Shipments-FY2019", Filter('Shipments-FY2019', Solution = "CompanyName"),

...

)

You can wrap this inside a function or reuse it across your app to reduce duplication.

2. Use a Unified Table

If possible, consider consolidating all yearly tables into a single Shipments table with a Year column. This allows you to filter dynamically:

Filter(Shipments, Year = DataLoop.Year && Solution = "CompanyName")

This is the recommended architectural approach in Dataverse to avoid fragmentation and improve maintainability.

3. Use Power Automate

If you need dynamic table access, you can offload the logic to a Power Automate flow:

Pass the table name as a parameter.
Use HTTP or Dataverse connector to query the correct table.
Return the results to Power Apps.

This adds complexity but gives you full control over dynamic logic.

🧠 Tips

Avoid using Set() or UpdateContext() inside ForAll—they’re not allowed because ForAll runs in a separate evaluation context.
Use With() to scope expressions if needed, but it won’t help with dynamic table names.