Replacing part data in a table in another table

[visitprateek]

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

[sanimesa · Accepted solution]

@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])

[visitprateek]

Thanks a ton :). Problem resolved.