Forum Discussion
Merging Two Data Tables (Invoice and Orders)
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?
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
5 Replies
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
There is much more data then those listed columns. Just made it smaller as an example.
