Power Apps – Bulk Updates With Duplicate Column Names

Will Cauthen

Dealing with data integration between two distinct sources that happen to share identical column names can often lead to confusion and frustration. Whether you’re working with patching, looking up, or collecting data, distinguishing between columns becomes essential.

In this blog post, we’ll explore several effective techniques to tackle this challenge, especially when dealing with real-world scenarios that involve irregular data and bulk updating.

To illustrate these methods, we’ll use a practical example involving a Gallery named Gallery_Materials, a Collection named colMaterials, and a Data Source named Materials. In all cases, the column names across these three sources are uniform, making the need for effective column differentiation important.

1) Use ‘As’ to Create Aliases

For bulk patching a collection of data with *some* user input in a gallery, using As, to create an alias is one of the easiest things you can do. When creating an alias, you can use any word. The most important part is to keep it simple and create a standard for yourself. I like using _item because it stands out however you could use something like STUFF as well, meaning that the first column in the example below would be: ‘Material Name’: STUFF.’Material Name’,

Patch(
    Materials,
    Gallery_Materials.AllItems,
    ForAll(
        Gallery_Materials.AllItems As _item,
        {
            'Material Name': _item.'Material Name',
            'Material Type': _item.'Material Type',
	     Quantity: TextInput.Text,
	     Price: _item.Price,
             Color: _item.Color,
            'Part Number': _item.'Part Number'
        }
    )
);

// in working with a collection instead of a gallery, you would replace Gallery_Materials.AllItems with the name of your collection.

2) Wrap Column Names in Brackets with ‘@’

Wrapping column names in ‘@’ works similar to an alias. I prefer doing this method when creating new records, dealing with unconventional column names and when I need to maintain consistency with the original data source. It’s also helpful when doing complex lookups. I use this method when patching default records in instances where ForAll is required.

ForAll(colMaterials,
     Patch(
        Materials,
          Defaults(Materials),
        {
            'Material Name': colMaterials[@'Material Name'],
            'Material Type': colMaterials[@'Material Type'],
	     Quantity: TextInput.Text,
	     Price: colMaterials[@Price],
             Color: colMaterials[@Color],
            'Part Number': colMaterials[@'Part Number']
        }
    )
);

3) Rename Columns

Another method is to rename the columns. When you do this, typically it’s cleaner if you create a temporary collection.

ClearCollect(colMaterials_temp, RenameColumns(colMaterials, "Material Name","New Material","Price","New Price"));

Patch(
    Materials,
    colMaterials_temp,
    ForAll(
        colMaterials_temp As _item,
        {
            'Material Name': 'New Material',  
	     Price: 'New Price'
           
        }
    )
);

4) ThisRecord

Similar to ThisItem with a gallery, you can reference a record in a collection using ThisRecord. Typically, I like doing this when I need to do bulk updates without dynamic changes or row level changes/input.

ClearCollect(colMaterials, Materials);

ForAll(
    colMaterials,
    Patch(Materials, ThisRecord,
        {
            'Material Status': "Received"
           //'Material Status': TI_Shipment_Status.Text
           
        }
    )
);

Final Thoughts

These methods also work when updating single records. For single records, the concept is the same however your source would change to reference a single record in a gallery, collection, or data source.

1. Gallery
    ThisItem
2. Record via lookup
    LookUp(ColMaterials, 'Material Name' = "Nails") 
3. Record variable 
    varRecord
        Set(varRecord,  LookUp(ColMaterials, 'Material Name' = "Nails"))
Subscribe
Notify of
0 Comments
Inline Feedbacks
View all comments