powerquery - Table.TransformColumns by Columns' Value

Question

I have been stuck on something on PowerQuery for hours now and I cannot seem to find a solution to my problem.

Context

I try to use "Table.TransformColumns" to change the table.

My code is

= Table.TransformColumns(table,{"A Only",
      each Table.SelectRows(_, (X)=> Text.Contains(X[Customer Search Term], [Word A]))})

and the error msg is

Expression.Error: The column 'Word A' of the table wasn't found.

If I change the [Word A] into "AAA", the code can process normally.

Does anyone know how to use the columns[Word A] instead of the string "AAA"?

question from:https://stackoverflow.com/questions/65887249/table-transformcolumns-by-columns-value

Answer 1

The problem here is that Table.Transform applies a transformation to a single column and cannot reference other columns without some sort of workaround.

See this post for more information: Power Query Transform a Column based on Another Column

The simplest approach is to define a new custom column (which can reference multiple existing columns) and then delete the existing one that it replaces.

To do it without needing an extra column, you can use the approach in the linked post and use a row transformation, which has the other columns as part of the record you're transforming:

= Table.FromRecords(Table.TransformRows(table,
      (r) => Record.TransformFields(r, {"A Only",
          each Table.SelectRows(_, (X) => Text.Contains(X[Search Term], r[Word A]))
      })))

Note the r in front of [Word A]. This is the context that was missing with Table.Transform.