Full Outer Join Gallery Table

[Ezzyy798]

Hi,
I want to merge my 2 tables into one Gallery list and i want the other value that has blank/different ID to be showed too (full join). I have this Dataverse table with >50+ column as table 1, and SharePoint list table with >40+ column as table 2. 

I already have tried this method on YouTube, but it makes my apps lagged so much and i can only do left join with those function.
https://www.youtube.com/watch?v=KeXLIzr-6e4&pp=ygUacG93ZXJhcHBzIG91dGVyIGpvaW4gdGFibGU%3D

Is there any way so that i can full outer join my tables other than using 'Add columns' function?

Thanks in advance

[Adrian_Celis]

Hi @Ezzyy798 ,

There was a recent update for the table function where you can merge two tables together. Can you try:

Table(Table1,Table2)

[Ezzyy798]

Thanks for the reply @Adrian_Celis, 

Unfortunately, the result is not what I expect. It does get all the data in both tables, but because it didn't use any formula to join the same IDs, the table result is

Header

Table1

Table2

It only joins the tables in a gallery list, not the data

[Adrian_Celis]

Hi @Ezzyy798 ,

Apologies as my SQL is rusty. If we try the full outer join then eventually we will be using the AddColumns function. Since your complaint is that it is very slow, perhaps it is because of the number of columns? If you only need a select number of columns, then have you already tried using DropColumns or ShowColumns function?

[Ezzyy798]

Hi @Adrian_Celis,

I have tried using Add Columns function:

AddColumns(Table1 As example, datajoin, LookUp(Table2, ID=ID))

But it returns left join for Table 1 only.

Do you think is there any way the function can return a full join data? If it's about the lagged when loading the data, i think I'll figure it out somehow.

[Adrian_Celis · Accepted solution]

Hi @Ezzyy798 

Not sure yet if this is exactly what you are looking for:

But I have 2 tables: Table1 and Table2

Then in Power Apps, insert a gallery and use this formula on the Items property:

SortByColumns(
Table(
Filter(AddColumns(Table1 As CurrentItem,LookUpColumn,LookUp(Table2,TempID=CurrentItem.TempID)),IsBlank(LookUpColumn)),
AddColumns(Table2 As CurrentItem,LookUpColumn,LookUp(Table1,TempID=CurrentItem.TempID))
)
,"TempID")

Is this what you are looking for or at least getting closer?

[Ezzyy798]

Sorry for the late reply,

So, I have tried the formula above but it still doesn't work with large dataset. On the other hand, I have tried with small dataset and it works fine. I think I'll figure out a way to make it work in large dataset later. Your formula gives me a hint though.

Thank you so much!