Easy way to auto rename columns in Power Query

OK, let’s face facts. A lot of data exported from IT systems contain column names intended to be computer-readable, but only partially human-readable. As a result, column names are often in camel case or filled with underscores. We want fully human-readable column names in the data model in Power BI. Usually, this would mean renaming every column individually. Instead, I’ve created a custom function to auto rename columns so they are easier to read.

Table of contents

• The problem
• Custom function
• Add function to your query
• Using the custom function
• Wrap-up

The problem

Renaming manually is a reasonable option if you have only a small number of columns. However, if you have 20 tables with 20 columns each, that’s 400 columns. Nobody wants to do that job!

Below is a screenshot of the column names we might find in a database. If you’ve spent any time with the AdventureWorks data set these names may be familiar to you.

Taking OrderQuanity, ExtendedAmount, UnitPriceDiscountPct as examples, we know what these columns are intended to be, but they are not easy to read.

This scenario is an ideal candidate for a custom function.

Custom function

The function changes the common database field naming styles (camel case, underscores, and numbers) and changes them into easy-to-read, proper case headings.

Making a general rule for auto renaming all column names will always fail to meet needs; there will be scenarios where specific columns should be excluded. To solve this, the function has two optional parameters:

• A list of column names
• A toggle to state whether the listed items are included or excluded from the transformation

So, here it is. This is my solution for auto rename columns to be easier to read. I’ve called the function fxCleanColumnNames.

(Table as table, optional columnNamesList as list, optional listTypeIsInclude as logical) as table =>

let

//Apply the Table.TransfromColumnNames transformation
Result = Table.TransformColumnNames(Table,(columnName as text) as text =>

  //Sub function to clean all column names
  let

  //Replace underscore
  replaceUnderScore = Text.Replace(columnName,"_"," "),

  //Split text on lower to upper case transition
  splitAtUpper = Splitter.SplitTextByCharacterTransition({"a".."z"},
    {"A".."Z"})(replaceUnderScore),
  combineSplitAtUpper = Combiner.CombineTextByDelimiter(
    " ", QuoteStyle.None)(splitAtUpper),

  //Split text on text to number transition start
  splitAtNumberStart = Splitter.SplitTextByCharacterTransition(
    {"0".."9"}, (c) => not List.Contains(
    {"0".."9"}, c))(combineSplitAtUpper),
  combineSplitAtNumberStart = Combiner.CombineTextByDelimiter(
    " ", QuoteStyle.None)(splitAtNumberStart),

  //Split text on text to number transition end
  splitAtNumberEnd = Splitter.SplitTextByCharacterTransition(
    (c) => not List.Contains({"0".."9"}, c), 
      {"0".."9"})(combineSplitAtNumberStart),
  combineSplitAtNumberEnd = Combiner.CombineTextByDelimiter(
    " ", QuoteStyle.None)(splitAtNumberEnd),

  //Apply Proper Case
  returnValue = Text.Proper(combineSplitAtNumberEnd),

  //Check is columnNamesList provided.
  //If no list, return clean name, or if on list return clean name
  finalVal = if columnNamesList is null 
    then returnValue
    else if List.Count(List.FindText(columnNamesList,columnName)) >= 1 
      and listTypeIsInclude = true
    then returnValue 
    else if List.Count(List.FindText(columnNamesList,columnName)) = 0 
      and listTypeIsInclude = false
    then returnValue
    else columnName

  in
  finalVal
  )

in
Result

To learn more about creating custom functions, check out this post – Power Query: Custom Functions

Syntax

The syntax of the function is as follows:

=fxCleanColumnNames(
    Table as table,
    optional columnNamesList as list, 
    optional listTypeIsInclude as logical
) as table

The arguments are:

• Table: The name of the Table to be transformed.
• columnNamesList: an optional argument to list the columns to include or exclude from the scope of the function. If excluded, all column names are transformed.
• listTypeIsInclude: a logical value where:
  • true = the columnNamesList argument contains items to be included in the scope of the transformation
  • false = the columnNamesList argument contains items to be excluded from the scope of the transformation

Add function to your query

To add the custom function into your Power Query:

• Create a new blank query by licking Home > New Source (drop-down) > Blank Query
• Open the Advanced Editor by clicking View > Advanced Editor
• Delete all the code in the Advanced Editor and paste in the code above
• Click Done to accept the change and close the Advanced Editor

Give the query a name; I’ve gone for fxCleanColumnNames

The function is now ready to use 👍

Using the custom function

Everything is set up and ready to go. So, let’s put this function to work.

Unlike many other custom functions, this is not applied as a Custom Column, but as a Table transformation. The easiest way to do this is by clicking the fx icon next to the formula bar and entering the code directly.

Assuming the previous step in our query is called Promoted Headers, the following are examples of applying the function.

Basic usage

The M code statement below applies the custom function to all columns in the Promoted Headers step.

= fxCleanColumnNames(#"Promoted Headers")

You can see this in action in the screenshot below. The function applies an auto rename columns transformation.

Include only specific columns

The example below applies the custom function to only the SalesOrderNumber and UnitPrice columns.

= fxCleanColumnNames(#"Promoted Headers",
  {"SalesOrderNumber","UnitPrice"},true)

Exclude specific columns

The formula below applies the custom function to all columns except the SalesOrderNumber and UnitPrice columns.

= fxCleanColumnNames(previousStepName,
  {"SalesOrderNumber","UnitPrice"},false)

The difference between this and the previous function is the last argument. This toggles whether the listed columns are included or excluded.

Wrap-up

In this post, we have seen it is possible to auto rename columns using a custom function. While we may not use this in every project, it is a useful addition to our custom function library to pull out at the right time.

Other resources

Looking for more custom functions or want to know more about renaming columns? Check out these resources:

• Remove Spaces in Power Query
• Get FX rates into Power BI
• Automating column name renames – Ben Gribaudo
• Automatically or bulk renames columns in Power BI – BI Accountant
• Reference: List.Count