cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Is there a way to combine 2 tables and perform a FULL OUTER JOIN?

HI.

May I ask one more question?
I'm Korean
Understand that English is awkward.
Thanks for every help!
 
UPC dataform # 1 data and # 2 data were combined to give the result of # 3. However, I want to make comparison data with all the combined UPCs like # 4. Is there a concise syntax like # 3?
 
I made # 4 with the knowledge I know. Please refer.
See Group Button and Data Format Syntax.
If there is a better way, can you ask me for an idea?
 
Have Nice Day!!!!
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Hi @Anonymous :

Firstly,currently powerapps does not support full join.

Secondly, similar functions can be achieved by a combination of functions.I've made a test for your reference:

My data source(for some reason, I cannot download the attachment you provided):

collection1/collection2

 

ClearCollect(collection1,{UPC:"A",LOCbyQty:53},{UPC:"B",LOCbyQty:4},{UPC:"C",LOCbyQty:4},{UPC:"D",LOCbyQty:2});
ClearCollect(collection2,{UPC:"A",PackbyQty:51},{UPC:"B",PackbyQty:4},{UPC:"C",PackbyQty:5},{UPC:"E",PackbyQty:5})

 

1\add a datatable control and set it's items property to:

 

RenameColumns( /*Renamed "Result" column to "UPC"*/
    AddColumns(  /*Add two fields LOCbyQty and PackbyQty, use lookup to find the corresponding values in the two tables*/
        Distinct(/*Get the UPC values of the two tables, and get the single column list of UPC after deduplicating the results*/
            Split(
                Concatenate(
                    Concat(
                        collection1,
                        UPC,
                        "#"
                    ),
                    "#",
                    Concat(
                        collection2,
                        UPC,
                        "#"
                    )
                ),
                "#"
            ),
            Result
        ),
        "LOCbyQty",
        LookUp(
            collection1,
            UPC = Result
        ).LOCbyQty,
        "PackbyQty",
        LookUp(
            collection2,
            UPC = Result
        ).PackbyQty
    ),
    "Result",
    "UPC"
)

 

 

 

1.jpg

Best Regards,

Bof

View solution in original post

4 REPLIES 4
Community Support
Community Support

Hi @Anonymous :

Firstly,currently powerapps does not support full join.

Secondly, similar functions can be achieved by a combination of functions.I've made a test for your reference:

My data source(for some reason, I cannot download the attachment you provided):

collection1/collection2

 

ClearCollect(collection1,{UPC:"A",LOCbyQty:53},{UPC:"B",LOCbyQty:4},{UPC:"C",LOCbyQty:4},{UPC:"D",LOCbyQty:2});
ClearCollect(collection2,{UPC:"A",PackbyQty:51},{UPC:"B",PackbyQty:4},{UPC:"C",PackbyQty:5},{UPC:"E",PackbyQty:5})

 

1\add a datatable control and set it's items property to:

 

RenameColumns( /*Renamed "Result" column to "UPC"*/
    AddColumns(  /*Add two fields LOCbyQty and PackbyQty, use lookup to find the corresponding values in the two tables*/
        Distinct(/*Get the UPC values of the two tables, and get the single column list of UPC after deduplicating the results*/
            Split(
                Concatenate(
                    Concat(
                        collection1,
                        UPC,
                        "#"
                    ),
                    "#",
                    Concat(
                        collection2,
                        UPC,
                        "#"
                    )
                ),
                "#"
            ),
            Result
        ),
        "LOCbyQty",
        LookUp(
            collection1,
            UPC = Result
        ).LOCbyQty,
        "PackbyQty",
        LookUp(
            collection2,
            UPC = Result
        ).PackbyQty
    ),
    "Result",
    "UPC"
)

 

 

 

1.jpg

Best Regards,

Bof

View solution in original post

Anonymous
Not applicable

hi @v-bofeng-msft 

 

Thanks for the answer. That solution is well used. Thank you every time.

The solution provided by the respondent can get a quick answer.!!

Could you please consider the method I used?
I've used a function like this in an unopened file I sent earlier.

 

Button on Select : 

Collect(Collection3,AddColumns(GroupBy(collection1,"UPC","ByUPC"),"LOCbyQty",Sum(ByUPC,LOCbyQty)),AddColumns(GroupBy(collection2,"UPC","Byupc2"),"PackbyQty",Sum(Byupc2,PackbyQty)))

 

Dataform Item : 

AddColumns(GroupBy(Collection3,"UPC","Fullouter"),"LocbyQty",Sum(Fullouter,LOCbyQty),"PackbyQty",Sum(Fullouter,PackbyQty))

 

Will it be a problem part?

 

Best Regards,

 

 

 

Hi @Anonymous :

Your method is good without any problems.

Could you tell me:

  • Is your app working now?
  • What is your problem now?

Best Regards,

Bof

Anonymous
Not applicable

hi @v-bofeng-msft 

 

The app works fine and there are no problems.
With the solution provided by the respondents, we were able to move forward with one step.
Thank you !! Have a nice day ~!

Helpful resources

Announcements
New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

Top Solution Authors
Top Kudoed Authors
Users online (14,885)