Replacing value depending on the value of another sheet/query

[Yester07]

Hello guys,

I want to replace values in table2 depending on the following instructions:

1. Look if the value of the column A can be founded in the column A2, if it is founded, then

2. If the column B in the same row is the same as the column B2 then

3. replace the column C2 by the column C:

Table1: 

Col A	Col B	Col C
A1	B111	C1
A2	B2	C2
A3	B3	C3

Table2: 

Col A2	Col B2	Col C2
A1	B111	C111
A222	B222	C222
A3	B333	C333

Result:

Col A2	Col B2	Col C2
A1	B111	C1
A222	B222	C222
A3	B333	C333

I have a lot of rows...i tried to copy past the column in the same table but it is complicated, then i tried to create a query where a source is the sheet of the table 2 but I did not found how to retrieve value from another query...

Thank you in advance !

[Vijay_A_Verma]

Please validate the condition before a solution can be provided

1. If ColA = ColA2 and ColB=ColB2 then ColC should be replaced with ColC2 - This is your first row in Result

2. If ColA=ColA2 and ColB<>ColB2 then ColB should be replaced with ColB2 and ColC should be replaced with ColC2 - This is your third row in Result

3. Now, question about Row 2 in Result. So here A2 doesn't exist in Table 2. How are you replacing A2 with A222 row? 

[Yester07]

Hello, no sorry i just modified the header of the table's result. I want to replace the values in table2. Therefore:

1. If ColA = ColA2 and ColB=ColB2 then ColC2 should be replaced with ColC - first row

2. If ColA=ColA2 and ColB<>ColB2 then do nothing: keep the values of table2

3. if ColA <> ColA2 then do nothing - row 2

I must replace the ColC2 only if colA and ColB are the same as ColA2 and ColB2. the problem is that table2 is in another sheet...

Thank you

[Vijay_A_Verma · Accepted solution]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjRU0lFyMjQEUc4gKlYHKGhkZAQShlDOIAosbAwSNDYGUc4gKjYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Col A1" = _t, #"Col B2 " = _t, #"Col C2" = _t]),
    #"Merged Queries" = Table.NestedJoin(Source, {"Col A1", "Col B2 "}, Table1, {"Col A", "Col B "}, "Table1", JoinKind.LeftOuter),
    #"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Table1", {"Col C"}, {"Col C"}),
    Custom1 = Table.ReplaceValue(#"Expanded Table1",each [Col C2],each if [Col C]<>null then [Col C] else [Col C2]
,Replacer.ReplaceValue,{"Col C2"}),
    #"Removed Columns" = Table.RemoveColumns(Custom1,{"Col C"})
in
    #"Removed Columns"