cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Meneghino
Community Champion
Community Champion

Solved: union operator or function to append one table to another

Hi, does anyone know if it possible to create the union of two tables?  I.e. to be able to append TableB to TableA?

 

I know that you can use Collect (or ClearCollect) to do this, but this requires calling an action.  This is not what I am asking for.

 

Ideally I would like to be able to make then Items property of my gallery something like:

 

TableA+TableB

 

or

 

Table(TableA, TableB)

 

But netiher of these work.

 

Any ideas?

1 ACCEPTED SOLUTION

Accepted Solutions
Meneghino
Community Champion
Community Champion

Got it!  Here is how you do it:

 

Ungroup(Table({MyTables: TableA},{MyTables: TableB}), "MyTables")

I am beginning to think you can do anything with PowerApps

 

PS This will not work if either of the two tables has no rows, i.e. is an empty table.

View solution in original post

8 REPLIES 8
Meneghino
Community Champion
Community Champion

Got it!  Here is how you do it:

 

Ungroup(Table({MyTables: TableA},{MyTables: TableB}), "MyTables")

I am beginning to think you can do anything with PowerApps

 

PS This will not work if either of the two tables has no rows, i.e. is an empty table.

View solution in original post

martinav
Community Champion
Community Champion

How could you handle this if you wanted no duplicate line items?  Provided there were completely duplicated rewords in either table?

Meneghino
Community Champion
Community Champion

I would just wrap a Group() function around the whole lot to eliminate duplicates

martinav
Community Champion
Community Champion

Thats a good suggestion.  I ended up doing it this way:

 

ClearCollect(
    MDL_All,
    List1,
    Filter(
        List2,
        Not(Id in List1.Id)))

Props to @CarlosFigueira

Anonymous
Not applicable

Hi @Meneghino and @martinav , 

 

I am trying to simulate a union of two sharepoint list to be used in one collection.   I tried both of the methods with the "Ungroup" and the ClearCollect with the filter based on no duplicate IDs.  

For this one what does the first "Table" represent? 

Ungroup(Table({MyTables: TableA},{MyTables: TableB}), "MyTables")

 

For this one is there a way to do a collection from two different list using an && ?  The two list that I have are the same but we're separating to accomadate for the row limitation in PowerApps.  

ClearCollect(
MDL_All,
List1,
Filter(
List2,
Not(Id in List1.Id)))

martinav
Community Champion
Community Champion

@Anonymous,

 

Please forgive me.  I'm not sure I fully understand what you are asking.  

 

I think you are saying that your ungroup function performs the same as the one I posted.  

 

Then your second statement.  using AND to combine them?  

 

The row limitation issue I have is in the my filters not being able to search the entire sharepoint list items. (<2000)   Getting them into the collection can be done in 2,000 item chunks.  The method I used was to get the first 2000, and the last 2000, and then doing the merge together, eliminating duplicates using the Not() command for List 2.  Then, I can do all of the filter statements I want in the collection without delegation issues.  

 

I'm not entirely sure this helps.  Please try to restate your question if you need too.

 

Regards,

Rod

Anonymous
Not applicable

Hi @martinav

 

I was able to resolve my issue.   We were using two separate SharePoint list to get around the 2000 limit and I needed a way to union the two list together into one collection.  I was able to do that using ClearCollect for the first list and then a Collect to append the records from my second list the collection.   I didn't know that "Table" was a function when I sent my initial quesiton.  

With the above solutions I was getting list of TableA visible but only Blank Cells for Items of TableB. I figured the reason was my tables had more than one column.

Therefore I used a bit of a work around:

First I made a collection (on the on visible property of the screen) which is:
ClearCollect(combined, {Comb:Distinct(TableA,DesiredColumnName)},{Comb:Distinct(TableB,DesiredColumnName)})

 

Then I used a Dropdown control to view the combined data by ungrouping the collection:

Set Item property of the Dropdown control to:

 

Ungroup(combined,"Comb")

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,774)