Search value in another table and retrieving data

[Yester07]

Hello,

I have a complicated request. I want to check if the value of column B exists in column A. If it exists, I want to replace the value in the same rows of the column "COL D-B" with the value of the column "Col C-A". Hope it is clear I am new to Power Query and I tried for a few days...

Table : 

Col A	Col B	Col C-A	Col D-B
AZ	DF	Desc A1	Desc B1
AZE	AZ	Desc A2	Desc B2
AZER	AZERT	Desc A3	Desc B3
AZERT	FD	Desc A4	Desc B4

Results : 

Col A	Col B	Col C-A	Col D-B
AZ	DF	Desc A1	Desc A2
AZE	AZ	Desc A2	Desc B2
AZER	AZERT	Desc A3	Desc B3
AZERT	FD	Desc A4	Desc A3

For now, I added a column that put "True" if the value is found, and I also tried to put the column "Col B" and the col "Col D-B" in another table, in vain.

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("i45WcoxS0lFycQMRqcXJCo6GMJaToVKsDkjeFSgCUQVWYARXYARTEARW4RoUAldkDFdkDFcEknVzgSsxgSsxUYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Col A" = _t, #"Col B" = _t, #"Col C-A" = _t, #"Col D-B" = _t]),
    ColBBuffList = Source[Col B],
    #"Added Custom" = Table.ReplaceValue(Source,each [#"Col D-B"],each if List.Contains(ColBBuffList,[Col A]) then Source[#"Col C-A"]{List.PositionOf(ColBBuffList,[Col A])} else [#"Col D-B"],Replacer.ReplaceValue,{"Col D-B"})
in
    #"Added Custom"

[Yester07]

OMG, thank you so much !!
PS: I just changed the name of the columns, applied a filter, then create 2 other sources.