cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Tuan
Helper III
Helper III

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

InvoiceCustomerValue
1000ABC100
1001Bee200
1002Cali300
1003Burrito

400

And my order data as 

 

InvoiceOrderCustomerValue
1000201ABC100
1001202Bee200
1002203Cali300
1003204Burrito400
 205ABC100
 206ABC200

 

 

I want the end data to look like 

 

Transaction TypeInv/OrderCustomerValue
Invoice1000ABC100
Invoice1001Bee200
Invoice1002Cali300
Invoice1003Burrito400
Order205ABC100
Order206ABC200

 

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

1 ACCEPTED SOLUTION
rajendran
Community Champion
Community Champion

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

View solution in original post

5 REPLIES 5
rajendran
Community Champion
Community Champion

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

View solution in original post

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

rajendran
Community Champion
Community Champion

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

Not yet, will try it! 

 

Thank you so much for the help!

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.

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors