cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sniagsh
Frequent Visitor

Adding row numbers to nested table

I would like to number the rows of a table that is inside another table. The aim is to create a single text file via a flow, where the nested tables appear as CSV inbetween some headers and footers. 

 

Here's some example data to show how I sort by Account and Week (for the headers and footers), and then use a table called groupedItems that will be converted to CSV in a flow:

ClearCollect(
    TestCollection,
    {Account: "3090011",Week: 45,Project: "1102",Days: 13,rowNumber:0},
    {Account: "3090013",Week: 46,Project: "2100",Days: 5,rowNumber:0},
    {Account: "3090013",Week: 46,Project: "3102",Days: 2,rowNumber:0},
    {Account: "3090013",Week: 46,Project: "4101",Days: 2,rowNumber:0},
    {Account: "3090013",Week: 47,Project: "3102",Days: 9,rowNumber:0}
);
ClearCollect(
    ForSorting,
    AddColumns(
        TestCollection,
    "AccountWeek",
    Account & ";" & Text(Week)
    )
);
ClearCollect(SortedCollection,
    AddColumns(
        GroupBy(
            ForSorting,
            "AccountWeek",
            "GroupedItems"
        ),
        "Rows",
        CountRows(GroupedItems),
        "WeekGroup",
        First(GroupedItems).Week,
        "AccountGroup",
        First(GroupedItems).Account
    )
);

 

So in week 46 there are 3 rows that have the same account and week. How do i number these rows?

 

I've found some code that will incrementally number rows based on Patch, which I figure is what I need. Here's one such attempt that doesn't work:

ForAll(SortedCollection As parent,
    With(
        {records: parent.GroupedItems},
        ForAll(
            Sequence(CountRows(records)),
            Patch(
                Last(
                    FirstN(records,Value)
                ),
                {rowNumber: Value}
            )
        )
    )
);

 (inspired by http://powerapps.co.nz/auckland/index.php/our-blog/35-add-row-numbers-to-a-power-apps-collection)

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User
Super User

@sniagsh 

I would suggest getting rid of the collections!  You are duplicating data over and over and no collection is needed.

Your formula can be reduced to the following:

With({_items:
    Table(
       {Account: "3090011",Week: 45,Project: "1102",Days: 13},
       {Account: "3090013",Week: 46,Project: "2100",Days: 5},
       {Account: "3090013",Week: 46,Project: "3102",Days: 2},
       {Account: "3090013",Week: 46,Project: "4101",Days: 2},
       {Account: "3090013",Week: 47,Project: "3102",Days: 9}
    )
    },
    
    AddColumns(
        GroupBy(
            AddColumns(_items, 
                "AccountWeek", Account & ";" & Text(Week)
            ),
            "AccountWeek",
            "GroupedItems"
        ),
        "Rows", CountRows(GroupedItems),
        "WeekGroup", First(GroupedItems).Week,
        "AccountGroup", First(GroupedItems).Account,
        "_numberedItems", 
            With({_rows: GroupedItems},
                ForAll(Sequence(CountRows(_rows)),
                    Patch(Last(FirstN(_rows, Value)), {rowNumber: Value})
                )
            )
    )
);

 

I am not sure what the plan was that you have for submitting multiple grouped sets of items to your flow, but the above will at least give you the basics and will produce a table that has all of the grouped items with row numbers.

 

I hope this is helpful for you.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

3 REPLIES 3
RandyHayes
Super User
Super User

@sniagsh 

I would suggest getting rid of the collections!  You are duplicating data over and over and no collection is needed.

Your formula can be reduced to the following:

With({_items:
    Table(
       {Account: "3090011",Week: 45,Project: "1102",Days: 13},
       {Account: "3090013",Week: 46,Project: "2100",Days: 5},
       {Account: "3090013",Week: 46,Project: "3102",Days: 2},
       {Account: "3090013",Week: 46,Project: "4101",Days: 2},
       {Account: "3090013",Week: 47,Project: "3102",Days: 9}
    )
    },
    
    AddColumns(
        GroupBy(
            AddColumns(_items, 
                "AccountWeek", Account & ";" & Text(Week)
            ),
            "AccountWeek",
            "GroupedItems"
        ),
        "Rows", CountRows(GroupedItems),
        "WeekGroup", First(GroupedItems).Week,
        "AccountGroup", First(GroupedItems).Account,
        "_numberedItems", 
            With({_rows: GroupedItems},
                ForAll(Sequence(CountRows(_rows)),
                    Patch(Last(FirstN(_rows, Value)), {rowNumber: Value})
                )
            )
    )
);

 

I am not sure what the plan was that you have for submitting multiple grouped sets of items to your flow, but the above will at least give you the basics and will produce a table that has all of the grouped items with row numbers.

 

I hope this is helpful for you.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Thank you, this looks very elegant. I don't understand where the columns are added though. How would you view and process this new table further? I don't see any new collection being made here.

 

The TestCollection is what is used by the app. I regrouped and sorted only to generate input to a different program, and I prefer to generate more or less complete output in Power Apps rather than in flow.

sniagsh
Frequent Visitor

Sorry, please accept my ignorance - I figured it out. Thanks a lot!

Helpful resources

Announcements
PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PWREduCon768x460.png

Join us at PWR EduCon - A Power Platform Conference

Learn from the top Power BI, Power Apps, Power Automate & Power Virtual Agents experts!

Top Solution Authors
Top Kudoed Authors
Users online (3,168)