Forum Discussion

Nonsensely's avatar
Nonsensely
Advocate I
6 years ago

DAX Equivalent to Left Exception Join

I two tables from two systems that I want to compare to determine location/part/quantity variances. 

 

If writing an sql I would utilize a left exception join that would pull only variant records:

 

select a.partnumber, a.location, a.quantity, b.quantity from ERP a

left exception join ExternalSys b on a.partnumber = b.partnumber and a.location = b.location and a.quantity = b.quantity

 

I've done a crazy amount of data slinging/merging/navigating, etc., to get the tables into PowerBI (the external system only allows 2,000 rows at a time to import via JSON, and there are approx 32k active parts in the parts table and 20k active locations in the location/qty tables), and now just want to compare VARIANT ERP quantities to the ExternalSys quantities and send an HTTP POST to synch the externalsystem to the values in the ERP. 

 

Is there an easy way to do this in power query?

Tips and Tricks

2 Replies

  • edhans's avatar
    edhans
    Community Champion
    6 years ago

    Power Query, yes, if I understood your question. Not DAX, which is in your post subject.

     

    Merge the tables, but in the join type, pick one of the Anti-joins.

     

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    Hi Nonsensely ,

     

    If you have get the Table  A (ERP) in Power Query editor, and the http post sent to Table B (External Sys) has limitation. We can try to add a custom column in the Table A if the API sent to External Sys can put some parameter to filter the response: 

     

    Table.AddColumn( #"Name Of Last Step Of Table A", 
    each let p = [partnumber],
             l = [location],
             q = [quantity]
         in
             Web.Contents("URL_To_Table_B_API", 
               [RelativePath = "Partnumber="& p & "&location=" & l & "&quantity=" & q]
             )
)


    Best regards,