cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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
Highlighted
Community Support
Community Support

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

Hi @Noah_S2 :

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
Highlighted
Community Support
Community Support

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

Hi @Noah_S2 :

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

Highlighted
Frequent Visitor

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

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,

 

 

 

Highlighted
Community Support
Community Support

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

Hi @Noah_S2 :

Your method is good without any problems.

Could you tell me:

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

Best Regards,

Bof

Highlighted
Frequent Visitor

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

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
Ignite

Microsoft Ignite

This will be a conference that you do not want to miss!

secondImage

New Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (4,146)