cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
UB400
Level 10

Help with a formula to extract data from one Collection into another

I'm building a Survey App and would like to show the resuts in a simple Pie Chart.

 

The Data Source (Sharepoint) looks like the following:

 

 

QuestionID

Status

Responder

Answer

1

Submitted

John

Yes

1

Submitted

Jane

Yes

1

Submitted

David

No

 

I would like to transform it (bring it into a new Collection) so that it looks as below

 

QuestionID

Answer

Votes

1

Yes

2

1

No

1

 

 

 

 

Basically I want to get all the distinct values of answers for each question, and the count by answer type, so that I can display a Pie Chart for each question.

 

Resulting Pie Chart should look like:

 

PieChart.jpg

Any help would be appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Help with a formula to extract data from one Collection into another

Hi @UB400

 

I would suggest you to use:

GroupBy( Table, ColumnName1 [, ColumnName2, ... ], GroupColumnName )

  • Table - Required. Table to be grouped.
  • ColumnName(s) - Required. The column names in Table by which to group records. These columns become columns in the resulting table.
  • GroupColumnName - Required. The column name for the storage of record data not in the ColumnName(s).

https://powerapps.microsoft.com/en-us/tutorials/function-groupby/

 

Create a Button.OnSelect (or even, Screen,OnVisible, Timer1.OnTimerEnd,......to trigger GroupBy, as you wish).

 

ClearCollect(VoteDS, GroupBy(TableDS, 'Answer', 'Votes')); ClearCollect(VoteSum, AddColumns(VoteDS, 'Sum of Votes', Sum(Votes, QuestionID)))

- First ClearCollect will collect all the Answer into a new GroupColumnName (based on Yes and No) inside a table called VoteDS

Screenshot (246).pngScreenshot (247).png

 

- Subsequently, second ClearCollect will create another Table, called = VoteSum,  where it will AddColumns into VoteDS, a new column named = 'Sum of Votes', which contains the Sum of Votes based on Answer: Yes & Answer No, by total up corresping QuestionID

 

Then, add a Pie Chart, with Items = VoteSum.

 

Screenshot (245).pngPie ChartScreenshot (248).pngVoteSum

 

Hope this helps.

hpkeong
6 REPLIES 6
Super User
Super User

Re: Help with a formula to extract data from one Collection into another

Hi @UB400

 

I would suggest you to use:

GroupBy( Table, ColumnName1 [, ColumnName2, ... ], GroupColumnName )

  • Table - Required. Table to be grouped.
  • ColumnName(s) - Required. The column names in Table by which to group records. These columns become columns in the resulting table.
  • GroupColumnName - Required. The column name for the storage of record data not in the ColumnName(s).

https://powerapps.microsoft.com/en-us/tutorials/function-groupby/

 

Create a Button.OnSelect (or even, Screen,OnVisible, Timer1.OnTimerEnd,......to trigger GroupBy, as you wish).

 

ClearCollect(VoteDS, GroupBy(TableDS, 'Answer', 'Votes')); ClearCollect(VoteSum, AddColumns(VoteDS, 'Sum of Votes', Sum(Votes, QuestionID)))

- First ClearCollect will collect all the Answer into a new GroupColumnName (based on Yes and No) inside a table called VoteDS

Screenshot (246).pngScreenshot (247).png

 

- Subsequently, second ClearCollect will create another Table, called = VoteSum,  where it will AddColumns into VoteDS, a new column named = 'Sum of Votes', which contains the Sum of Votes based on Answer: Yes & Answer No, by total up corresping QuestionID

 

Then, add a Pie Chart, with Items = VoteSum.

 

Screenshot (245).pngPie ChartScreenshot (248).pngVoteSum

 

Hope this helps.

hpkeong
UB400
Level 10

Re: Help with a formula to extract data from one Collection into another

Thank you @hpkeong your answer solved my problem. Thank you for your clear explanation and the Screen Shots were really helpful in understanding how to go about it.

 

It took me a while to understand why i couldn't make your solution to work, the reason was that in your example your Value for Question ID was "1" whereas in my table it was greater than "1" (as I had many questions), and as a result the value I was getting for the Sum, was wrong. I added another Column in the DataSource which has a Value of "1", and this is the Column that I now do the Sum on, and it displays the correct Value.

 

By the way how did you build/display the Table next to the Pie Chart, on the Screen?

Super User
Super User

Re: Help with a formula to extract data from one Collection into another

Hi

You are most welcome and glad that problems are solved.

Those table I showed you are in the Collections. You can view them, but I did not display them in any table.
Those are just to show you how the GroupBy calculates and helps to understand how do they work.

If you wish to show them (just like Table of data next to Chart in Excel), you can just add a Gallery to show those data as in the collections.

Please always refer to DS as created with ClearCollect, such as VoteSum, VoteDS in your Gallery.Items.

Hope this helps.
hpkeong
UB400
Level 10

Re: Help with a formula to extract data from one Collection into another

Thanks @hpkeong I did end up displaying the Collection in a Gallery, I was just checking to see if there was any other way of displaying a "Table" on -screen.

Super User
Super User

Re: Help with a formula to extract data from one Collection into another

Welcome.

There maybe hundred of method better than mine.
I am just sharing whatever I know and within my knowledge.
Glad to have you share with me if you have other nice presentation.

Good night
hpkeong
Rob_Kearnes
Level: Power Up

Re: Help with a formula to extract data from one Collection into another

Thanks for the tutorial on how to do this.

Followed the steps and got the right result, so again thanks.

 

I have however reached a point today where the pie chart will not process any more than 500 records (my data source is a Sharepoint List).

 

Do you have any suggestions to get around this from your provided example or do we just have to wait for delegation to be rolled out in a more complete fashion?

 

Thanks

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

SecondImage

PowerApps Monthly Community Call

Next Wednesday, September 18th at 8am PDT

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Top Kudoed Authors
Users Online
Currently online: 272 members 4,654 guests
Please welcome our newest community members: