Power Platform Community Forum Thread Details

I have a gallery of checkboxes, allow users select years of interest to chart, how can I dynamically add those columns to a new collection for bar graphs?

MyCollection

SType 2019 2020 2021 2022 ...
A 2 1 5 6
B 1 1 3 2
C 1 1 7

Checkbox's OnCheck = Collect(CollYears,ThisItem) to get a list of years of interest, how can I create a new collection from MyCollection through AddColumns() or something else, the difficult part is AddColumns() need hard coded column names.

Categories:

Building Power Apps

Hi @wild2023 ,

I've made a test for your reference:

1\I assume there is a collection:

ClearCollect(
 MyCollection,
 {Stype:"A",'2019':2,'2020':1,'2021':5,'2022':6},
 {Stype:"B",'2019':1,'2020':1,'2021':3,'2022':2},
 {Stype:"C",'2019':1,'2020':0,'2021':1,'2022':7}
)

2\Add a gallery(Gallery1) and set it's Items property to:

Sequence(4,2019,1)

3\Add a checkbox(Checkbox1) into Gallery1 and set it's Text property to:

ThisItem.Value

4\Set Gallery1's OnSelect property to:

With(
{
 AString:JSON(LookUp(MyCollection,Stype="A")),
 BString:JSON(LookUp(MyCollection,Stype="B")),
 CString:JSON(LookUp(MyCollection,Stype="C")) 
 },
Set(DataSetA,Concat(ForAll(Filter(Gallery1.AllItems,Checkbox1.Value),First(Split(Last(Split(AString,""""&ThisRecord.Checkbox1.Text&""":")).Value,","))),Value,","));
Set(DataSetB,Concat(ForAll(Filter(Gallery1.AllItems,Checkbox1.Value),First(Split(Last(Split(BString,""""&ThisRecord.Checkbox1.Text&""":")).Value,","))),Value,","));
Set(DataSetC,Concat(ForAll(Filter(Gallery1.AllItems,Checkbox1.Value),First(Split(Last(Split(CString,""""&ThisRecord.Checkbox1.Text&""":")).Value,","))),Value,","));
)

5\Add an image control and set it's Image property to:

"https://quickchart.io/chart?c="&EncodeUrl("{
 type: 'bar',
 data: {
 labels: ["&Concat(Filter(Gallery1.AllItems,Checkbox1.Value),Checkbox1.Text,",")&"],
 datasets: [
 {
 label: 'A',
 backgroundColor: 'rgba(255, 99, 132, 0.5)',
 borderColor: 'rgb(255, 99, 132)',
 borderWidth: 1,
 data: ["&DataSetA&"],
 },
 {
 label: 'B',
 backgroundColor: 'rgba(54, 162, 235, 0.5)',
 borderColor: 'rgb(54, 162, 235)',
 borderWidth: 1,
 data: ["&DataSetB&"],
 },
 {
 label: 'C',
 backgroundColor: 'rgba(54, 162, 25, 0.5)',
 borderColor: 'rgb(54, 162, 235)',
 borderWidth: 1,
 data: ["&DataSetC&"],
 },
 ],
 },
 options: {
 title: {
 display: true,
 text: 'Bar Chart',
 },
 plugins: {
 datalabels: {
 anchor: 'center',
 align: 'center',
 color: '#666',
 font: {
 weight: 'normal',
 },
 },
 },
 },
}")