Replace value from one table to other by id

[konwes97]

Hi all,

I have to update values as a preswent in the tables here. i dont have idea how to complete it. 

Table 1

col1	col2
1	null
2	null
3	null
4	null

Table 2

Colour	Replacement
1	M
2	F
3	F
4	M

And i want to have this values in my table 1

Table 1

col1	col2
1	M
2	F
3	F
4	M

[V-pazhen-msft · Accepted solution]

@konwes97 

You can either merge the table1 and table2 in power query or create a calculate Column = RELATED('Table (2)'[Replacement]). 

Replace from another table is pretty complex in terms of logic, you may refer to:

Solved: Power Query - Replace values from another table wi... - Microsoft Power BI Community

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[selimovd]

Hey @konwes97 ,

I think the easiest would be a new calculated column in Table 1 with the following formula:

Replacement =
-- Save the value of col1 in the variable vCol1CurrentRow
VAR vCol1CurrentRow = 'Table 1'[col1]
RETURN
-- Get the value from column 'Table 2'[Replacement] where 
-- 'Table 2'[Colour] equals the value of current row
    CALCULATE(
        MAX( 'Table 2'[Replacement] ),
        'Table 2'[Colour] = vCol1CurrentRow
    )

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[konwes97]

hey
Thanks fot help, but the problem is that i need to replace the values, and not to add new.