Forum Discussion
Search value in another table and retrieving data
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.
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"
2 Replies
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"