Business Intelligence - BI, Excel, Power BI, Power BI Desktop, Power BI Service, Power Query, Self-Service BI

A Power Query Custom Function to Rename all Columns at Once in a Table

A Power Query Custom Function to Rename all Columns at Once in a Table

I am involved with a Power BI development in the past few days. I got some data exported from various systems in different formats, including Excel, CSV and OData. The CSV files are data export dumps from an ERP system. Working with ERP systems can be very time consuming, especially when you don’t have access to the data model, and you get the data in raw format in CSV files. It is challenging, as in the ERP systems, the table names and column names are not user friendly at all, which makes sense. The ERP systems are being used in various environments for many different customers with different requirements. So if we can get our hands to the underlying data model, we see configuration tables keeping column names. Some of the columns are custom built to cover specific needs. The tables may have many columns that are not necessarily useful for analytical purposes. So it is quite critical to have a good understanding of the underlying entity model. Anyhow, I don’t want to go off-topic.

The Problem

So, here is my scenario. I received about 10 files, including 15 tables. Some tables are quite small, so I didn’t bother. But some of them are really wide like having between 150 to 208 columns. Nice!

Looking at the column names, they cannot be more difficult to read than they are, and I have multiple tables like that. So I have to rename those columns to something more readable, more on this side of the story later.

Background

I emailed back to my customer, asking for their help. Luckily they have a very nice data expert who also understands their ERP system as well as the underlying entity model. I emailed him all the current column names and asked if he can provide more user-friendly names. He replied me back with a mapping table in Excel. Here is an example to show the Column Names Mapping table:

Column Names Mapping

I was quite happy with the mapping table. Now, the next step is to rename all columns is based on the mapping table. Ouch! I have almost 800 columns to rename. That is literally a pain in the neck, and it doesn’t sound quite right to burn the project time to rename 800 columns.

But wait, what about writing automating the rename process? Like writing a custom function to rename all columns at once? I recall I read an excellent blog post about renaming multiple columns in Power Query that Gilbert Quevauvilliers wrote in 2018. I definitely recommend looking at his blog post. So I must do something similar to what Gilbert did; creating a custom function that gets the original columns names and brings back the new names. Then I use the custom function in each table to rename the columns. Easy!

The Solution

Before we start, I need to have my mapping table in Power BI. So I create a new table using the Enter Data functionality. Then I copy the data from my mapping table and paste it into the new table. I name the new table Column Names Mapping. The main reason to use the Enter Data functionality instead of getting data directly from the file (in my case an Excel file) is that if for any reason in the future we miss the mapping file, the function below cannot find the columns to map. Hence, it brings back the original column names. This means if we created a data model on top of the new column names, then the whole model breaks, which is no good. I also disable load on the Column Names Mapping query as I don’t need to have it in my data model.

Creating fnRenameColumnsFromRefQuery Custom Function

The function reads through the Column Names Mapping table and renames the columns of the query that we invoked the function in when it finds the matching. If it doesn’t find the matching, it leaves the column name is. Here you go, this is the function I came up with.

//fnRenameColumnsFromRefQuery
(ColumnName as text) as text =>
let
    Source = 
        if (
            List.Contains(
                Record.FieldNames(#sections[Section1]), 
                "Column Names Mapping"
                ) 
             ) = true 
        then #"Column Names Mapping" 
        else null,
    ColumnNewName = 
        try 
            if List.Contains(Source[Column Name], ColumnName) = true 
            then 
                if Text.Trim(Table.SelectRows(Source, each ([Column Name] = ColumnName)){0}[Description]) = "" 
                then ColumnName 
                else Table.SelectRows(Source, each ([Column Name] = ColumnName)){0}[Description] 
            else Source 
        otherwise ColumnName
in
    ColumnNewName

You can download the preceding expressions from here.

Here is how it works:

  • The fnRenameColumnsFromRefQuery accepts a ColumnName parameter as text and the output of the function is a text.
  • The Source step checks the existence of a Column Names Mapping query. The Column Names Mapping is the mapping tables shown in the preceding image that holds the original column names and their mapping. You may use a different name of choice. If the Column Names Mapping query exists then Source = #"Column Names Mappings" else Source = null
  • The ColumnNewName step checks the contents of the Source step which is either the Column Names Mapping table or a null. If it is the Column Names Mapping table then it looks for the ColumnName parameter in the [Column Name] column within the Column Names Mapping table. If it finds the matching value then it uses the ColumnName parameter to filters the [Column Name]. Then it gets the corresponding value from the [Description] column which contains the new column name, otherwise it brings the original column name back

The last step is to use the fnRenameColumnsFromRefQuery custom function to rename the table’s columns.

Invoking the fnRenameColumnsFromRefQuery Custom Function

Power Query has a function to renamed column names in tables which is the Table.TransformColumnNames(table as table, NameGenerator as function) function. The Table.TransformColumnNames() function accepts a NameGenerator as its second operand. So we can use our custom function as the second operand within the Table.TransformColumnNames() function to rename all columns. The final code must look like this:

Table.TransformColumnNames(PREVIOUS_STEP_NAME, fnRenameColumnsFromRefQuery)

Here is a screenshot of the column names before and after renaming the columns:

Renaming columns using a reference list in Power Query
Renaming columns using a reference list in Power Query

You can download the PBIX sample here.

Conclusion

If we think what we are doing is quite laborious and we are burning a lot of project time doing that, it is good to think about a way to automate the job. It might look a bit complex, confusing and time-consuming at first, but believe me, in many cases it is worth it and after you find the way you can use it in many other similar scenarios which saves you a lot of time. Having said that, there is no guarantee that we can automate our scenario, so it would be better to be prepared for it. For instance, it is a long time since I’m thinking to automate the process of renaming all queries appearing in the Query Editor, but I am yet to find a solution for it. Here is the challenge, in some cases, we connect to the data source, and we have many tables to be loaded to the model, and the table names are not always user friendly. It is similar to renaming columns but at the query level. So if you already know the solution, please share it with the community so everyone can benefit from your efforts. At the end of the day, we learn from each other. So sharing our knowledge is a paying forward to the next person.

The other point is to always remember to ask other experts for help. We might be experts in some areas but it is impossible for someone being an expert in everything. Like in my case the customer had an expert who provided me with the column names mapping table, without his help I couldn’t get the job done.

As always, I would love to know your opinion, so please share your thoughts in the comment section below.

Discover more from BI Insight

Subscribe to get the latest posts sent to your email.

Published by Soheil Bakhshi

21 thoughts on “A Power Query Custom Function to Rename all Columns at Once in a Table”

  1. Absolutely fantastic. Excellent job! Very useful without a doubt.
    In my case I have CSV files that do not contain headers. How could you adapt in this case?

    If you can help me now, thank you.

    Best regards

    Reply

    1. Hi UAlg,

      Welcome to BIInsight.com.
      I’m happy to hear you found this blog helpful.
      In some cases there is a csv file containing the columns while the rest of files don’t.
      In those cases you can append the csv files. If that’s not your case I suspect you have to ask the business for supply the column names.

      Cheers.

      Reply

      1. Further to this, I have enhanced the fnRenameColumnsFromRefQuery to match on a partial string, rather than a full string:

        = (ColumnName as text) as text =>
        let
        Source =
        if (
        List.Contains(
        Record.FieldNames(#sections[Section1]),
        “Terminology Pack”
        )
        ) = true
        then #”Terminology Pack”
        else null,
        ColumnNewName =
        try // if there is a terminology string to replace
        if List.Contains(Source[Original], ColumnName, (x as text, y as text) => Text.Contains(y, x)) = true // x is string to replace, y is string being searched
        then // select row from the terminology mapping and replace the text string with the new string from the mapping table
        Text.Replace(ColumnName, Table.SelectRows(Source, each Text.Contains(ColumnName, [Original]) = true){0}[Original], Table.SelectRows(Source, each Text.Contains(ColumnName, [Original]) = true){0}[New])
        else ColumnName
        otherwise ColumnName
        in
        ColumnNewName

        Reply

  2. Agree with UAlg, this is very useful and almost exactly answers the problem I have. My additional requirement is that I want to make this applicable to multiple tables and mappings.

    What would this look like if you were to pass the “Column Name Mapping” table in as a parameter?

    Reply

    1. Hi Stuart,

      Your question inspired me to write a new blog post that can help the developers having a similar problem.
      Check this out.
      I think a combination of the technique I explained in the new blog post and the one in the current post can help you solve your problem.

      Cheers.

      Reply

  3. Hi Soheil,
    Really nice article, but I cannot download the pbix file as it states the file is not available anymore. Would you please be so kind to check if it still works. Because what I am struggling with is where I need to copy paste this code. Because in the M-editor I also need to connect to the server to pull the data. E.g. where in the code should I paste it? E.g.
    let
    Source = Odbc.Query(“XXXXXXX”, “SELECT * FROM “”DB””.””table_name”””)
    in
    Source
    And should it be done in the mapping table or in the data table?
    Thanks in advance,
    Catherine
    PS in this particular case I did not used and insert table, but imported both tables, as the mapping table is also part of the DB (and just a floating excel/csv file)

    Reply

    1. Hi Catherine,

      Thanks for letting me know about the download issue. You could get the M expressions from my GitHub as well.
      In that case, you simply add a blank query, open the Advanced Editor and paste the copied code from GitHub to the Editor.
      In my scenario, I decided to use the Enter Data functionality for the reason I explained in the blogpost.
      But if in your case the mapping Excel file is stable and will not be changed in the future you can use the Excel file instead.

      Hopefully that helps.

      Cheers.

      Reply

  4. Hi Soheil,

    Wonderful article, I was searching for this.
    I would like to plug it into my requirement, where users want the changed names to reflect in the table visuals. But the tables break every time the names change. Can we perform the renaming without breaking the reference in table visuals. Would appreciate your input.

    Thanks,
    Naveen

    Reply

        1. I am doing something wrong, the expression is correct. I only see the difference with the example that the columns are numeric. Thanks for your kind reply.

          Reply

  6. Hi Soheil,

    Thank you for this fantastic guide. It seems to be the best way in PowerBI.
    How would you get the tables headers when you use a SQL Data source (with dbo scheme) ?

    I am missing the previous step in order to use your function.

    Best Regards
    Jim

    Reply

    1. Hi Jim,

      I’m happy that you found this post helpful.
      To get the column names in SQL Server you can query INFORMATION_SCHEMA like below:

      SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
      FROM INFORMATION_SCHEMA.COLUMNS

      Please note that to work with the method I explained in this post, you require to have a mapping table to map the old column name to its new names.

      Hopefully that helps.
      Cheers

      Reply

  7. This is great! Thank you!
    I was able to get it to work using tables that contain fields of data type as text only. But when I try to use it on a table that has a column as data type text and another column as data type number, it does not seem to work. I appreciate your response!

    Reply

    1. Try this (Replacing “Source” by “ColumnName” near the end):

      //fnRenameColumnsFromRefQuery
      (ColumnName as text) as text =>
      let
      Source =
      if (
      List.Contains(
      Record.FieldNames(#sections[Section1]),
      “Column Names Mapping”
      )
      ) = true
      then #”Column Names Mapping”
      else null,
      ColumnNewName =
      try
      if List.Contains(Source[Column Name], ColumnName) = true
      then
      if Text.Trim(Table.SelectRows(Source, each ([Column Name] = ColumnName)){0}[Description]) = “”
      then ColumnName
      else Table.SelectRows(Source, each ([Column Name] = ColumnName)){0}[Description]
      else ColumnName
      otherwise ColumnName
      in
      ColumnNewName

      Worked for me 🙂

      Reply

      1. Shiverz is the man! Thanks for your reply!

        Near the end… “else Source otherwise ColumnName”, change to… “else ColumnName otherwise ColumnName”.

        Fixed the error! (Expression.Error: We cannot convert a value of type Table to type Text.)

        Reply

Leave a Reply

Your email address will not be published. Required fields are marked *


The reCAPTCHA verification period has expired. Please reload the page.