Unanswered

To create a data table with following sharepoint list

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

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)

Sort by

victorcp 2,350 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
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... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

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
a
(0)

Report
Copy link

Link copied!
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... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

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... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

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... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

I have solved this issue, thanks again

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

Like
a
(0)

Report
Copy link

Link copied!
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