PowerFx to Add Row Numbers to Data Tables Directly

The Problem

I recently hit a common but annoying issue while building a canvas app for Dynamics 365 Project Operations. The requirement was straightforward: display project tasks in the same order they appear in the D365 Project Tasks grid.

When I connected the Project Task table directly to a data table control, the rows showed up in creation order instead of the logical sequence users expect. Tasks that should appear at the top were buried at the bottom.

My first instinct was to sort by the msdyn_displaysequence column in ascending order. This got me closer, but revealed another issue: the D365 grid shows clean row numbers (1, 2, 3…) while msdyn_displaysequence contains decimal values like 1.5, 2.3, 4.7.

This makes perfect sense from a data perspective. When someone inserts a task between positions 1 and 2, the system assigns it 1.5 instead of renumbering every subsequent row. Smart design, but not great for user experience.

The Traditional Approach (And Why I Avoided It)

Most solutions I found online suggest creating a collection with row numbers added.

This works, but it creates a local copy of your data. That means dealing with refresh logic, potential sync issues, and the overhead of maintaining a collection that mirrors your data source.

I wanted a cleaner approach that works directly with the data source.

The Solution: PowerFx Row Numbering

After digging through community solutions, I found inspiration in Matthew’s blog post about generating row numbers in collections. I adapted his approach to work directly with data sources instead of creating separate collections.

Here’s the formula that solved my problem: (replace with your own dynamic)

ForAll(
    Sequence(
        CountRows(
            SortByColumns(
                Filter(
                    'Project Tasks',
                    Project.Project = [@ModelDrivenFormIntegration].Item.Project
                ),
                "msdyn_displaysequence",
                SortOrder.Ascending
            )
        )
    ),
    With(
        {
            sortedTasks: SortByColumns(
                Filter(
                    'Project Tasks',
                    Project.Project = [@ModelDrivenFormIntegration].Item.Project
                ),
                "msdyn_displaysequence",
                SortOrder.Ascending
            )
        },
        Patch(
            Last(FirstN(sortedTasks, Value)),
            { Index: Value }
        )
    )
)

And here's the end result.

How It Works

Let me break this down into digestible pieces:

Step 1: Create the sequence

Sequence(CountRows(filtered_and_sorted_tasks))

This generates a sequence from 1 to the total number of tasks. If you have 10 tasks, you get [1, 2, 3, 4, 5, 6, 7, 8, 9, 10].

Step 2: Process each position

ForAll(sequence, ...)

For each number in our sequence, we execute the patching logic.

Step 3: Get the task at each position

Last(FirstN(sortedTasks, Value))

This grabs the Nth task from our sorted list. FirstN(sortedTasks, 3) gets the first 3 tasks, then Last() gives us exactly the 3rd task.

Step 4: Add the row number

Patch(task, { Index: Value })

This adds an Index column to each task record with the appropriate row number.

Real-World Impact

This approach gave me exactly what I needed: tasks displayed in the correct sequence with clean, sequential row numbers that match what users see in D365. The row numbers automatically update when the underlying msdyn_displaysequence changes, and I don’t have to manage a separate collection.

The performance is reasonable for typical project sizes (50-200 tasks). For larger datasets, you might want to implement pagination or consider the collection approach after all.

When to Use This Pattern

This technique works well when:

• You need row numbers that reflect a specific sort order
• You want to avoid managing collections
• Your dataset is reasonably sized (under a few hundred records)
• The underlying data doesn’t change frequently during a single session

Avoid this approach if:

• You’re working with thousands of records
• The data changes constantly
• You need complex transformations beyond adding row numbers

The Bottom Line

Sometimes the best solution isn’t the most obvious one. Instead of fighting with collections or settling for confusing decimal sequences, a targeted PowerFx formula can solve the problem directly at the data source level.

The key insight here is using Sequence() with ForAll() and Patch() to systematically add computed columns to your data source. Once you understand this pattern, you can apply it to other scenarios where you need to augment data source records with calculated values.

Credit to Matthew Devaney for the original collection-based approach that inspired this solution.

Original Post http://linnzawwin.blogspot.com/2025/05/powerfx-to-add-row-numbers-to-data.html