cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Kudo Kingpin
Kudo Kingpin

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
Community Champion
Community Champion

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.

 

Pie ChartPie ChartVoteSumVoteSum

 

Hope this helps.

hpkeong

View solution in original post

6 REPLIES 6
Community Champion
Community Champion

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.

 

Pie ChartPie ChartVoteSumVoteSum

 

Hope this helps.

hpkeong

View solution in original post

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?

Community Champion
Community Champion

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

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.

Community Champion
Community Champion

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

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
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (10,587)