Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
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,
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.