Downloads
Power Query MTable: columnsIntermediate

Table.PrefixColumns

Adds a prefix and a dot to every column name in the table.

Syntax
Table.PrefixColumns(table as table, prefix as text) as table

Returns: The table with every column name prefixed.

What it does

Table.PrefixColumns adds a label in front of every column name, followed by a dot. When two tables have columns with the same name, such as Date or Amount, a flat join cannot keep both without a clash. Prefixing one of them gives every column a clear source, such as Bank.Date and GL.Date.

Syntax and arguments

Example

A bank file and a general ledger both have Date and Amount columns. Before the two are joined in one flat table, the bank columns are prefixed, so the names no longer clash.

let
    Bank = Excel.CurrentWorkbook(){[Name = "Bank"]}[Content],
    GL = Excel.CurrentWorkbook(){[Name = "GL"]}[Content],
    BankLabelled = Table.PrefixColumns(Bank, "Bank"),
    Joined = Table.Join(BankLabelled, "Bank.Reference", GL, "Reference", JoinKind.FullOuter)
in
    Joined

Every bank column is now named Bank.Date, Bank.Amount and so on. The join key is named with its new prefix, Bank.Reference, because the prefix applies to every column, the key included.

Example: a second, different use

The same labelling works for a lookup table, so the reviewer can tell at a glance which fields came from the lookup.

let
    Source = Excel.CurrentWorkbook(){[Name = "Customers"]}[Content],
    Labelled = Table.PrefixColumns(Source, "Cust")
in
    Labelled

Things to watch

In business terms

Mixing a ledger and a subledger in one table is a common reconciliation task. Clear column prefixes stop a reviewer from reading the bank amount when they meant the ledger amount.

Related functions

All Power Query M functions · View as Markdown · M basics