Merging Two Data Tables (Invoice and Orders)

[Tuan]

I'm trying to merge invoice and orders together with the exclusions of orders that already been invoiced.

I have my Invoice Data as

Invoice	Customer	Value
1000	ABC	100
1001	Bee	200
1002	Cali	300
1003	Burrito	400

And my order data as 

Invoice	Order	Customer	Value
1000	201	ABC	100
1001	202	Bee	200
1002	203	Cali	300
1003	204	Burrito	400
	205	ABC	100
	206	ABC	200

I want the end data to look like 

Transaction Type	Inv/Order	Customer	Value
Invoice	1000	ABC	100
Invoice	1001	Bee	200
Invoice	1002	Cali	300
Invoice	1003	Burrito	400
Order	205	ABC	100
Order	206	ABC	200

Is there a guide that shows how I can do this?

[Anonymous · Accepted solution]

If the order is invoiced you have invoice number, else you have just order number alone & invoice number is blank. So allrequied details are available in order table itself.

Create the below 2 columns and drag along with customer and value columns from the Order table. 

Invoice/order = IF( ISBLANK( Order[InvoiceNo]), Order[OrderNo],Order[InvoiceNo])

TransactionType= IF( ISBLANK( Order[InvoiceNo]), "Order","Invoice")

Hope this helps.

Thanks

Raj

[Tuan]

There is much more data then those listed columns. Just made it smaller as an example.

[Anonymous]

Still this will work. Did you try the above approach?

[Tuan]

Not yet, will try it! 

Thank you so much for the help!

[Tuan]

I actually wanted to append the tables together. But your formula help me think it through. I probably didn't do it in the best way. I essentially did a lookup if the invoice exist and filtered out the orders and then i renamed the columns to match together and append them.