collect with show column and filtering

[JR-BejeweledOne]

I need to collect filtered data from a table into a collection.   I do not however need all the columns.  

If I do this: 

ClearCollect(HolidayCollectionPY, Filter(HolidaySchedules, DropdownCountry.Selected.Name in Country And Year = Text(varPreviousYear)))

It brings in the records but only 2 columns have data in them.  The remaining columns in the new collection are blank even though they aren't blank in the table.

Dataverse Table

HolidayCollection from dataverse table

If I do this however, I get an error on the filter and an unspecified error on the ShowColumns

ClearCollect(HolidayCollectionPY, ShowColumns(HolidaySchedules, "crc39_country", "crc39_region", "crc39_holiday", "crc39_prefix", "crc39_suffix", "crc39_halfday", "crc39_freebusy", "crc39_holidayname", (Filter(HolidaySchedules, DropdownCountry.Selected.Name in Country And Year = Text(varPreviousYear))));

Thanks in advance for the assist

[v-jefferni]

Could you please share more details about your table? What are the column types of those blank columns in the Dataverse table, LookUp type?

If they are LookUp type columns, then you will not be able to create collections with data in those columns. Below is a thread that talking about the same issue:

https://powerusers.microsoft.com/t5/Building-Power-Apps/Saving-lookup-field-in-collection/td-p/43467...

So, it is possible to create the collection with LookUp column value, with the workaround in below thread:

https://community.dynamics.com/365/b/linn-s-power-platform-notebook/posts/bug-using-the-clearcollect...

So, in your case, you need to modify your formula to be like:

ClearCollect(HolidayCollectionPY,
           DropColumns(
                       AddColumns(
                                   ShowColumns(
                                                Filter(HolidaySchedules, 
                                                     DropdownCountry.Selected.Name in Country And Year = Text(varPreviousYear),
                                                     "LookUpColumn1", " LookUpColumn2", ...
                                                ),
                                  "TempColumn",
                                  First(HolidaySchedules). LookUpColumn1
                       ),
            "TempColumn"
            )
)

Hope this helps.

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

[JR-BejeweledOne]

They are all text columns.

[JR-BejeweledOne · Accepted solution]

I was able to use your example to get the proper syntax for the Collect with ShowColumns and Filter and that worked.

ClearCollect(HolidaysTemp,

ShowColumns(

Filter(HolidaySchedules, DropdownCountry.Selected.Name in Country And Year = Text(varPreviousYear)),

"crc39_country", "crc39_holiday", "crc39_holidayname", "crc39_region", "crc39_prefix", "crc39_suffix", "crc39_halfday", "crc39_freebusy"

)

);