Unanswered

To create a data table with following sharepoint list

(0) Share

Report

Posted on by Community Power Pla...

Hi!

I have a sharepoint list as shown below, so i want to display a data table (shown as 'result' below). When users filter the drop down filter bar by choosing a choice based on Item[1] column in lists, the app will count the number of times a unique code appears either in Code[1] or Code[2] or Code[3] and display it in the data table, the code with the most count will fall on the top of the table.

Thanks in advance for any help!

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (0)

victorcp 2,350 Moderator on at

Like (0)

Report
Hi,

One way to what you want is creating a collection with all the codes and then count how many time each one appear in this collection, like this:

Clear(colAux); // the collection that will store all the codes ForAll( Filter( SharePointList, // the name of your ShP Item = "A" // The filter to select the Item ), Collect(colAux,{Code:Code1}); // adding code1 to the collection Collect(colAux,{Code:Code2}); // adding code2 to the collection Collect(colAux,{Code:Code3}); // adding code3 to the collection ); ClearCollect( colResult, // the result collection Sort( AddColumns( GroupBy( // this will group the same codes colAux, "Code", "Group" ), "Count", CountRows(Group) // and this will count how many time each code appear ), Count, SortOrder.Descending ) )

I hope it helps 🙂

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Thanks for the help! I do have another problem, what if i do not want the collection to count when the code is 'N/A'?

Was this reply helpful? Yes No
victorcp 2,350 Moderator on at

Like (0)

Report
You can filter to remove the N/A like this:

Clear(colAux); // the collection that will store all the codes ForAll( Filter( SharePointList, // the name of your ShP Item = "A" // The filter to select the Item ), Collect(colAux,{Code:Code1}); // adding code1 to the collection Collect(colAux,{Code:Code2}); // adding code2 to the collection Collect(colAux,{Code:Code3}); // adding code3 to the collection ); ClearCollect( colResult, // the result collection Sort( Filter( AddColumns( GroupBy( // this will group the same codes colAux, "Code", "Group" ), "Count", CountRows(Group) // and this will count how many time each code appear ), Code <> "N/A" // Removing N/A ), Count, SortOrder.Descending ) )

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

I see thank you so much for the help, may i know if i am using a SharePoint List of more than 2000 items, will i run into any delegation issue running these codes?

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Also I need to add the condition of not count those with no values, i tried (Code<>"" ) but it does not work

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

I have solved this issue, thanks again

Was this reply helpful? Yes No
victorcp 2,350 Moderator on at

Like (0)

Report
it will be an issue if this filter return more than 2000 items,

Filter( SharePointList, // the name of your ShP Item = "A" // The filter to select the Item )

if it returns I suggest add another condition to make sure it will get less than that

Was this reply helpful? Yes No