Table.PrefixColumns
Adds a prefix and a dot to every column name in the table.
Table.PrefixColumns(table as table, prefix as text) as tableReturns: 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
- table: the table to prefix. Required.
- prefix: the text to put in front of each column name. The dot is added automatically. Required.
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
- The key columns get the prefix too. Join on the new names, not the old ones.
- Remove the prefix later with Table.RenameColumns if you do not want it in the report.
- Prefixing a table does not change its values, only its names.
- Query folding: check View Native Query. A prefix on a database table is an alias change and often folds, but a join that follows may not.
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.