Help with Implementing Reiteration for Conditional Replacement of Values through "Coupled" Columns

Here's another method that seems to work a bit faster.

It uses List.Accumulate to iterate the Table.ReplaceFunction to check each pair of columns.

 

Note that I use List.RemoveMatchingItems function. As written, it will only remove if there is a "perfect" match, as you wrote. If you want to make the match case-insensitive, or include other imperfections,  you can include the optional  Equation Criteria. 

However, if your actual layout is different, and/or if you have other columns in your actual data that are not relevant to your problem. That algorithm will need to be changed.

 

Please read the code comments, explore the applied steps, and ask if you don't understand something.

 

let

//Change next line to reflect your actual data source
    Source =Table,

/*Written to accomodate any number of columns, but assumes they are all type text
  If you have other columns in your real data that are of a different type, this
  will need modification*/
    #"Changed Type" = Table.TransformColumnTypes(Source,
        List.Transform(Table.ColumnNames(Source), each {_, type nullable text})),

/*If your real data has more columns that are not to be checked
  this algorithm to get the column pairs will need to be changed*/
    #"Columns" = [a=List.Skip(Table.ColumnNames(Source)),
                  b=List.Split(a,List.Count(a)/2),
                  c=List.Zip(b)][c],

    #"Replace Matches" = List.Accumulate(
        #"Columns",
        #"Changed Type",
        (s,c)=> Table.ReplaceValue(
                    s,
                    each Record.Field(_,c{0}),
                    each Record.Field(_,c{1}),
                    (x,y,z) as nullable text=> [a=Text.Split(y,"#(lf)"),
                               b=Text.Split(z,"#(lf)"),
                               c=List.RemoveMatchingItems(a,b),
                               d=Text.Combine(c,"#(lf)")][d],
                    {c{0}}
        )
    )
in
    #"Replace Matches"

 Original

 

Results

 

 

Here is a bit of a mismash of AlienSx's solution and ronrsnfld's solution. Table.ToList converts each row to a list and applies the function supplied in second arg, similar to AlienSx. In the function we are splitting text, removing from A matching in B, then re-combining, similar to ronrsnfld. And buffering and iterating through each row's list is relatively faster, although it probably won't be noticable unless you are dealing with non-small data (100k+ rows in my testing).

 

let
    fix_func = (row as list, optional row_headers as number) as list =>  [ 
        row_b = List.Buffer( row ),
        row_header_count = row_headers ?? 1,
        row_data_setSize = (List.Count(row_b)-row_header_count)/2, // assume two data column sets of equal length
        row_header = List.FirstN( row_b, row_header_count ),
        row_data_setB = List.LastN( row_b, row_data_setSize ),
        row_data_setA_removeB = List.Generate(
            ()=>0, each _ < row_data_setSize, each _ + 1,
            each [
                item_a = row_b{row_header_count+_},
                item_b = row_b{row_header_count+row_data_setSize+_},
                removematch = 
                    if item_a = null or item_b = null 
                    then item_a 
                    else Text.Combine( 
                        List.RemoveMatchingItems( 
                            Text.Split( item_a,"#(lf)" ),
                            Text.Split( item_b,"#(lf)" )
                        ), 
                        "#(lf)" 
                    ) 
            ] [removematch]
        ),
        row_output = row_header & row_data_setA_removeB & row_data_setB
    ] [row_output],
    Source = Sample,
    FixRows = Table.ToList(Source, fix_func ),
    ToTable = Table.FromRows( FixRows, Value.Type(Source) )
in
    ToTable

 

To test, I used your same sample data, but I also added in the following row to validate case where lines of A are a proper subset of lines of B.

 

Added row in sample

(blank)	Column Ax	Column Ay	Column Az	Column Bx	Column By	Column Bz
Data6	Ax6			Ax6 Bx6

 

Target output (I'm assuming)

(blank)	Column Ax	Column Ay	Column Az	Column Bx	Column By	Column Bz
Data6				Ax6 Bx6

 

Here is the output with the sample and code

 

 

edit: updated row_data_setA_removeB step in fix_func to handle nulls