What is the difference between [column] and Table.Column(Table, “column”) in M/PowerBI/PowerQuery

Question

Ciao there!

I have a problem with a difference between [column] and Table.Column(Table, "column") in M/PowerBI/PowerQuery.

---

Example Table:
'#____column
1_______a
2_______b
3_______c

Desired Result:
'#____column
1_______TEST
2_______TEST
3_______TEST

---

So, I currently have the following code:

= Table.ReplaceValue(PrevQueryTable, each Table.Column(PrevQueryTable, "column"), 
            "TEST", 
        Replacer.ReplaceValue, {"column"})

which does not work. Result:

'#____column
1_______a
2_______b
3_______c

---

This however:

= Table.ReplaceValue(PrevQueryTable, each [column], 
            "TEST", 
        Replacer.ReplaceValue, {"column"})

does work. Result:

'#____column
1_______TEST
2_______TEST
3_______TEST

---

Why? And how can I make sth. like the first do work? (Currently writing a function which uses this with column names as strings.)

Answer 1

Table.Column returns a list from taking one table column.

[column] returns the value in that column for the current row.

In this case, I find Table.TransformColumns more flexible than Table.ReplaceValue.

If you used the GUI to transform several columns to UPPERCASE it would generate code that looks like this:

= Table.TransformColumns(
    PrevQueryTable,
    {{"Col1", Text.Upper, type text},
     {"Col2", Text.Upper, type text},
     {"Col3", Text.Upper, type text}})

This can serve as a template for how we want to write our own transformation. Suppose we have a list of column names ColumnList (from Table.ColumnNames for example). We could transform that list by adding the transformation functions to each element like so:

= Table.TransformColumns(
    PrevQueryTable,
    List.Transform(
        ColumnList,
        each {_, each "TEST", type text}
    )
  )

E.g. "col1" gets transformed into {"col1", each "TEST", type text}