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'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?
Solved! Go to 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
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.
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.
Covering 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.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |