Answered

Adding row numbers to nested table

(0) Share

Report

Posted on by sniagsh

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)

Categories:

Building Power Apps

All responses (3)

Answers (1)

Sort by

Verified answer

RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!
@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.
sniagsh 35 on at

Like
a
(0)

Report
Copy link

Link copied!

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 35 on at

Like
a
(0)

Report
Copy link

Link copied!

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