I have a dataset currently flowing in from MongoDB which is essentially populated based on custom fields that are optionally set within the source. Each element in the array has the name and value of the custom field (ie. customField<0-N>_fieldName and customField<0-N>_fieldValue). The issue is that it's not always the same column for the same field as
we use this as an array of custom fields, and not as specific fields for specific custom fields.
To make it simpler for a user to find all custom fields with certain key, I am trying to add columns in the ETL to pull in values for a specific custom field, if it exists. As an example, add a column for Member_Info_Verified
. This follows a conditional value to check customField<0-N>_fieldName == 'Member_Info_Verified'
and if so, set the value tocustomField<0-N>_fieldValue
, otherwise leave empty.
Attaching a few screenshots of the various types of rows I see for Name/Value combinations. Type can be ignored. Open to any sort of advice whether it be using a combination of formula tiles or running a Python script.