sharepointpowerappspower-platform

Power Apps: Collection passed through JSON function dropping columns with null values


I have an app and power automate combo where I am collecting columns from the selected gallery item in order to email the details to the user. When I check my collections, all desired columns are shown, including columns with a null value. When I check the ProfileData variable passed through the JSON function, the columns with null values have been dropped out. Is there a way to get columns with null to stick in the ProfileData variable? I'm open to ideas on different approaches as well. Any help is much appreciated.

Edit: Updated code below - I learned that pointing the app at an excel spreadsheet will include null attributes, but pointing it at a sharepoint list will excluse null attributes. Any ideas on how to include attributes with null values from a sharepoint list?

//Clear the existing profile collection
Clear(ProfileContent);

// Loop through each item in ProfileContent and collect the file data
Collect(ProfileContent,ShowColumns(CurrentItem,
First,Last,Middle,Phone,Class,Score,Grade)
);

// Set the ProfileData variable with the JSON representation of ProfileContent
Set(ProfileData, JSON(ProfileContent,JSONFormat.FlattenValueTables))

Solution

  • Inside your ForAll you are referring to the selected item in the gallery - which will give you the same record over and over. If you change it to ThisRecord, you will get the records from the ProfileCollection as (I imagine) you intend.

    Also, you are right, there seems to be an issue with SharePoint data sources where null values are not being emitted; to work around that, instead of using ShowColumns you can create the record explicitly:

    ...
    // Collect selected items from RecordsGallery1 into ProfileCollection
    Collect(ProfileCollection,RecordsGallery1.AllItems);
    
    // Loop through each item in ProfileCollection and collect the file data
    ForAll(
      ProfileCollection,
      Collect(
        ProfileContent,
        {
          Title: ThisRecord.Title,
          'Last Name': ThisRecord.'Last Name',
          'First Name': ThisRecord.'First Name',
          'Middle Initial': ThisRecord.'Middle Initial',
          etc..
        }
      )
    );
    ...