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.
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.
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

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
User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Power Apps Community Call Jan. 2022 768x460.png

Power Apps Community Call

Please join us on Wednesday, January 19th, at 8a PDT. Come and learn from our amazing speakers!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Solution Authors
Top Kudoed Authors
Users online (1,593)