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...
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"
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"
OMG, thank you so much !!
PS: I just changed the name of the columns, applied a filter, then create 2 other sources.