It is pretty easy to modify an objects properties in Power Automate; you could simply use a Select action and configure the mapping manually by hand. This would be a good approach if you wanted to update a collection of SharePoint items properties before exporting the data to a CSV file. However, what if the list schema changes and you add a column or remove a column. You would need to update the Flow.
Wouldn't it be a better design if we could configure our Flow so that the renaming of our objects was dynamic. To help illustrate this technique we are going to use the following use case: Exporting a SharePoint List of Items to CSV and emailing it a user. We will use a SharePoint list containing all the Zip Codes in the US and their Latitute and Longitude to help illustrate this.
Zip Code List Schema
- Title: Leverage the out of the box Title column to hold the zip code. Internal name is Title and Display Name is Zip Code.
- lat: Single line of text column to hold the latitude. Internal name is lat and Display Name is Latitude.
- long: Single line of text to hold the longitude. Internal name is long and Display Name is Longitude.
We are going to setup our Flow initially to get items from our list, create a csv table, and then email it us so we can see how CSV looks and the internal column headings.
The first modification we need to make to the flow is to create a variable to hold the items from SharePoint. We need to store the SharePoint items within a variable because we will be updating this variable with the new values.
Our next step will be to fetch the columns from SharePoint using the REST API. For this post we are going to simply call the fields endpoint and return all the fields on the list but you may want to get the fields on a content type or filter down the results to remove hidden or SharePoint specific columns like the LinkTitle, DocType and Edit fields.
Endpoint: _api/web/lists/GetByTitle('Zip%20Codes')/fields?$filter=(Hidden eq false) and (FieldTypeKind ne 12)
After we get the fields we need to loop through the results of that call so we can add each field to each item in our spItems variable with the friendly name and remove the properties with the original name. We will use Select actions to add and remove the properties for performance and to keep the updates to a single action. After we remove the property we need to update the spItems variable with our value to ensure we do not lose our updates.
Our addProperty function looks at the current item in our Select action and if the InternalName does not match the Title of the field it will add a property and return that item otherwise it returns the item without any modifications. We can access the current item in the select with the item function and the current field in our loop with the items function.
For the remove property select action we need to set our input to the output of the Select - Add Property action.
Our removeProperty function looks at the current item in our Select action and it will remove the property of the current field in our loop based on its InternalName if the InternalName differs from the title and return that modified item. Otherwise it will simply return the item without it modified at all.
We update our spItems variable by setting it to the output of our Select - Remove Property action.
Lastly, we need update our Create CSV Table action's input to be spItems and then we can test our Flow.
From here we can re-run our Flow and check the CSV to ensure the headers have been updated to the new names.
You can see that our Title, lat, and long headers have been updated to Zip Code, Latitude and Longitude.
We can take our flow a little bit further and make it more user friendly by adding in a feature to remove any of the default SharePoint columns like @odata.etag or ItemInternalId from our data set. We can do this by:
- Initializing an array variable to hold the properties we would like to remove from the items
- Filter out the columns from our list that appear in the array variable we created
- Remove any properties from items in our spItems property
- Update the apply to each loop to the output of the Filter array action that removes columns
The first step we need to do is initialize an array variable and populate it with the properties we want to remove.
The second step is to loop through the SharePoint columns and remove any columns whose Title is in our variable fieldsToRemove.
We can do this by leveraging the contains function.
Our last step is to loop through each property in the fieldsToRemove variable and remove it from each item in our spItems variable and update that variable with the modified items.
Since one of the properties we want to remove has a '.' in the property name we need to replace that property on every item so that we can remove it. We can do this with the following expression:
Then our Map expression will simply remove the field we are current on in our Apply to each loop from the items in spItems.
Lastly, you can see that we update the spItems variable with our output of this Select action.
Our last step is to update our Apply to each loop to loop over our filtered array of SP columns versus the original so we do not accidently add removed columns back into our object.
You can now see our final CSV has only the columns we want in it.