Appending specific rows from one table to another using DQ

[Anonymous]

Hi there,

I've run into a bit of an issue. I have two tables, one which lists all physical products in local currency and another which lists all transactions in the transaction currency. What I want is to append the rows containing service products (in bold) which are sold in local currency to the list of all the physical products. Also, I'm working with DirectQuery. (If not possible to do generally, there are only a dozen relevant products so doing it one-by-one by ie ProdID is feasible.) 

The relevant part of the tables look something like those under, though there are a number of unique columns to each:

PhysicalProducts

OrderNo	ProdID	ProdName	Customer	Revenue	CUR
100	259	Thingy	Bob	1203	NOK
101	262	Widget	Ken	9408	NOK
101	239	Gadget	Ken	2317	NOK
102	284	Whatnots	Rob	423	NOK

AllTransactions

OrderNo	ProdID	ProdName	Customer	Revenue	CUR
100	259	Thingy	Bob	112	USD
101	262	Widget	Ken	962	EUR
101	239	Gadget	Ken	255	EUR
101	230	Transportation	Ken	324	NOK
102	284	Whatnots	Rob	423	NOK

The outcome I'm after:

PhysicalProducts

OrderNo	ProdID	ProdName	Customer	Revenue	CUR
100	259	Thingy	Bob	1203	NOK
101	262	Widget	Ken	9408	NOK
101	239	Gadget	Ken	2317	NOK
101	230	Transportation	Ken	324	NOK
102	284	Whatnots	Rob	423	NOK

[camargos88]

Hi @Anonymous ,

Can't you use custom SQL with UNION ?

[Anonymous]

Sorry for the late reply, been trying (and failing) for a few days to make it work in every way I could imagine.

Unfortunately, SQL isn't exactly my speciality, so I'll have to wait for others who are better at it to return from vacation. 

Thank you for the suggestion!