Forum Discussion
Solved
Replacing part data in a table in another table
Hello
I want to replace some values in table 1 of table 2 depending on the invoice no. for example;
| Table 1 | ||
| Invoice date | Invoice No. | Amount |
| 01-01-2020 | GOOD-01 | 100 |
| 02-02-2020 | GOOD-02 | 200 |
| 03-02-2020 | RAJ-03 | 300 |
| 04-02-2020 | GOOD-05 | 400 |
| 05-02-2020 | GOOD-09 | 600 |
| 31-03-2020 | RAJ-10 | 900 |
| Table 2 | |||
| Invoice date | Original invoice number. | New bill No. | Revised amount |
| 01-01-2020 | GOOD-01 | GOOD-06 | 100 |
| 04-02-2020 | GOOD-05 | GOOD-10 | 500 |
| 05-02-2020 | GOOD-09 | GOOD-11 | 1000 |
| 31-03-2020 | RAJ-10 | RAJ-15 | 900 |
In the example above, I want there to be no new invoice. and the amount reviewed in Table 1 when there is a change in the original invoice.
Reqult required as follows:
| Result required | ||
| Invoice date | Not the correct invoice. | Amount |
| 01-01-2020 | GOOD-06 | 100 |
| 02-02-2020 | GOOD-02 | 200 |
| 03-02-2020 | RAJ-03 | 300 |
| 04-02-2020 | GOOD-10 | 500 |
| 05-02-2020 | GOOD-11 | 1000 |
| 31-03-2020 | RAJ-15 | 900 |
Please lead.
Thank you.
Best regards
Prateek
visitprateekYou can use LOOKUPVALUE to retrieve the revised invoice number and amount, if any available to build columns in the first table. Then use the new columns in your visuals.
For example:
Invoice No New = LOOKUPVALUE(Table2[New bill No.], Table2[Original invoice number.], Table1[Invoice No.])Invoice No Final = IF(ISBLANK(Table1[Invoice No New]), Table1[Invoice No.], Table1[Invoice No New])Amount New = LOOKUPVALUE(Table2[Revised amount], Table2[Original invoice number.], Table1[Invoice No.])Amount Final = IF(ISBLANK(Table1[Amount New]), Table1[Amount], Table1[Amount New])
2 Replies
sanimesaPost Prodigy
visitprateekYou can use LOOKUPVALUE to retrieve the revised invoice number and amount, if any available to build columns in the first table. Then use the new columns in your visuals.
For example:
Invoice No New = LOOKUPVALUE(Table2[New bill No.], Table2[Original invoice number.], Table1[Invoice No.])Invoice No Final = IF(ISBLANK(Table1[Invoice No New]), Table1[Invoice No.], Table1[Invoice No New])Amount New = LOOKUPVALUE(Table2[Revised amount], Table2[Original invoice number.], Table1[Invoice No.])Amount Final = IF(ISBLANK(Table1[Amount New]), Table1[Amount], Table1[Amount New])Thanks a ton :). Problem resolved.
