I have made 1 collection as below:
ClearCollect(
DistinctCountries,
ForAll(
Distinct('MIS-EnquiryFormList-Stage', UserCountry),
{ CountryName: Value ,
MinYear: Text(Min(Filter('MIS-EnquiryFormList-Stage', UserCountry = Value), Year(DateValue(SubmittedOn)))),
MaxYear: Text(Max(Filter('MIS-EnquiryFormList-Stage', UserCountry = Value), Year(DateValue(SubmittedOn))))
} // 'Result' is the default column name from Distinct
)
);
Output of above Collection:
Category Master List: There is another list with name 'CategoryMaster-List' as shown below:
Requirement: I want to create collection using 'DistinctCollection' & 'CategoryMaster-List' in format as below:
As you can see in the output image Each country is would have ML & MIS values based on the MIN & MAX years range. This would be the required output format.
Issue After googling I have prepared a code to achieve required output:
ClearCollect(
FormattedCollection,
ForAll(
DistinctCountries,
With(
{ Country: CountryName,
MinYear: Value(MinYear),
MaxYear: Value(MaxYear) },
ForAll(
Sequence(MaxYear - MinYear + 1, MinYear),
Collect(
FormattedCollection,
ForAll(
Filter('CategoryMaster-List', ValuesFor = "MIS"),
{
Country: Country,
Year: MinYear - 1, // This correctly calculates the current year
MicroLink: Blank(),
MIS: ColumnValues
}
),
ForAll(
Filter('CategoryMaster-List', ValuesFor = "ML"),
{
Country: Country,
Year: MinYear - 1, // Same calculation for year
MicroLink: ColumnValues,
MIS: Blank()
}
)
)
)
)
)
);
But there would be some syntax or any logical error. I am new to power app therefore unable to identify.
You can combine your tables with an expression like the one below:
Clear(FormattedCollection);
ForAll(
DistinctCountries As dc,
With(
{
Country: dc.CountryName,
MinYear: Value(dc.MinYear),
MaxYear: Value(dc.MaxYear)
},
ForAll(
Sequence(MaxYear - MinYear + 1, MinYear) As years,
ForAll(
'CategoryMaster-List' As cml,
Collect(
FormattedCollection,
{
Country: Country,
Year: years.Value,
MIS: If (cml.ValuesFor = "MIS", cml.ColumnValues, Blank()),
MicroLink: If (cml.ValuesFor = "ML", cml.ColumnValues, Blank())
}
)
)
)
)
)
The two nested ForAll calls would go through all items of the two collections, and when adding the new records, it would populate either the MIS or MicroLink column, depending on the value of the CategoryMaster-List row.