Imagine a case where you have two tables/collections in a Power Apps canvas app, and you want to merge those tables in a single one to be used somewhere else. The common practice is to use collections and to collect all data from those tables in a new collection.
The problem of this approach
If this collection is used in a single place, with no data editing need, an additional third collection would be created only for this purpose. And also if you wanted to do duplicate detection, you would have to add additional logic to your collect formulas.
The alternative
In this example, two tables are stored in variables (or it could be also in collections) with two fields only, Email and DisplayName. And the need is to have a third table generated by merging the original two tables, plus obtaining only the distinct records (see the names Thaddeus and Daniel are in both tables, and duplicates were removed in the third one):
To help us with this approach, we can use the With function, in combination with the GroupBy, Ungroup and ShowColumns functions. In summary, what the final formula will do:
- Create a temporary Table where each item have a child table (that will be the tables 1 and 2)
- Ungroup the table above, so all child items are in the same table
- Group the table by the fields we want to keep distinct and use the ShowColumns formula to explicitly tell what columns have to be returned and then remove the unnecessary grouped data column
Here is the formula to do this trick:
With(
{
outputTable: Ungroup(
Table(/* Create the table with child tables. If you want to join more tables, add more items to this table and the formula will work the same way*/
{tableObject: Table1},
{tableObject: Table2}
),
"tableObject"
)/* Merges all the tables into a single one, but no duplicates removed */
},
ShowColumns( //Display only the columns needed
GroupBy(
outputTable,
"Email",//add the columns to extract distinct data. any column to be used has to be added here, and in the ShowColumns piece
"DisplayName",
"Grouped"//Last parameter can be any name as it's just the name for a child table with grouped data
),
//same columns used on the GroupBy piece except the last one (the child table name)
"Email",
"DisplayName"
)
)You can use this formula as the Items property for data tables, galleries or other controls, instead of collecting the data to a new collection and using it as the items source.
Conclusion
By using a single formula we can eliminate the need to create additional collections when we need to merge tables/collections and keep the app code cleaner. The same formula can be used with data coming directly from data sources (instead of the local data Table1/Table2 as mentioned), but be aware of possible delegation issues.
[…] Power Apps: Merge tables/collections and get distinct records (without using Collect) – michel… […]
Thank you very much! This was exactly what I was looking for?
Hi Brecht, glad it helped! 😃
You are god, trust me. You saved my life writing bad code 🙂
Haha Thanks!!! Great it helped you!
What about Sharepoint tables that have more then 2000 rows?
In order to use the lists in my app each large table has a column named Grp. I have assigned Grp2 to the first 2000, Grp4 to 2001-4000, and so on. This is how a created the collections of over 10K rows in the app.
Now I would like to combine them so I can perform math formulas on combined data.
How could we apply your approach with over 2000K rows?
Hi,
You can use the ForAll function in combination with the sequence function. The sample below considers only 20k rows as an example following the rule you described, but you can tweak the Sequence value as needed (for less or more), and also noted that the Grp field is a text field (tweak as needed):
Ungroup(
ForAll(
ForAll( Sequence(10, 2, 2 ), {Result: “Grp” & Value}) As GrpColumnPossibleValues,
{
tableObject: Filter ( ‘Your List’, Grp = GrpColumnPossibleValues.Result )
}
),
“tableObject”
)
Hi, I just want to let you know that your article really helped me. I have been trying lots of ways to merge 7 tables into one and yours by far is the easiest and the fastest. You saved my as* man!
Hey, thanks for the feedback 😁 Glad to hear this!
This is a great post and I hope I can use it to fit into my scenario but.
I am reaching out to you for help based on this post.
I am late to this post but hope that you still monitor the incomings.
What I am looking for? A nested table inside a column of a SharePoint List.
Column name = Funding
Nested table name = Funding Chart
Nested table(Funding Chart) columns = 6 {FY21, FY22, FY23, FY24,FY25,FY26}
Nested table(Funding Chart) rows max = 50
How can I achieve this using power app and have the info written back to a SharePoint List?
I have been looking on the Microsoft Docs site and the recommendation I got was to use HTML input control but I was not able to get it working. Any help from you is greatly appreciated.
Thank you in advance.
Hey David,
Thanks for the feedback😁
Related to the nested table case you are mentioning, will it be read/displayed only from Power Apps?
If this is the case you could use the JSON function to convert your table to JSON. Then save the data into a multiline text field (pure text).
You can use the MatchAll to parse your data back when needed.
Let me know if you have any questions.
Excellent contribution, Michael, thank you. For others like myself who also learn from watching, as opposed to only reading, here is a great YouTube contribution that credits Michael for his method and demonstrates it at the end – last five minutes or so: https://youtu.be/cSVDafX6dIk – lol, the best of both worlds, reading and viewing!!
Thank you for the feedback Gerard! 😁
Andrew Hess (the author of the video) messaged me to tell me about it. Really good video and channel, I recommend checking his content, great solutions!
Errata: last 2 mins of his video, not last 5 – gd
Brilliant!! Thanks Michel.
I used this on a dropdown where I needed to combine Distinct values from an existing List with about 4 new options that didn’t exist as yet. New options aren’t added very often so this was perfect for my need.
Cheers mate.
Hey Eddie,
No worries, great that it was helpful 😊
Amazing. Worked great. Thank you!
Awesome!! Thank you!