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
News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

Power Apps Community Call

Power Apps Community Call- January

Mark your calendars and join us for the next Power Apps Community Call on January 20th, 8a PST

PP Bootcamp Carousel

Global Power Platform Bootcamp

Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.

secondImage

Power Platform Community Conference On Demand

Watch Nick Doelman's session from the 2020 Power Platform Community Conference on demand!

Top Solution Authors
Top Kudoed Authors
Users online (5,779)