Quick Tips: Renaming All Tables’ Columns in One Go in Power Query

I previously wrote a blog post explaining how to rename all columns in a table in one go with Power Query. One of my visitors raised a question in the comments about the possibility to rename all columns from all tables in one go. Interestingly enough, one of my customers had a similar requirement. So I thought it is good to write a Quick Tip explaining how to meet the requirement.

The Problem

You are connecting to the data sources from Power BI Desktop (or Excel or Data Flows). The columns of the source tables are not user friendly, so you require to rename all columns. You already know how to rename all columns of a table in one go but you’d like to apply the renaming columns patterns to all tables.

The Solution

The solution is quite simple. We require to connect to the source, but we do not navigate to any tables straight away. In my case, my source table is an on-premises SQL Server. So I connect to the SQL Server instance using the Sql.Database(Server, DB) function in Power Query where the Server and the DB are query parameters. Read more about query parameters here. The results would like the following image:

As you see in the above image, the results include Tables, Views and Functions. We are not interested in Functions therefore we just filter them out. The following image shows the results after applying the filter:

If we look closer to the Data column, we see that the column is indeed a Structured Column. The structured values of the Data column are Table values. If we click on a cell (not on the Table value of the cell), we can see the actual underlying data, as shown in the following image:

As the above image illustrates, the selected cell contains the actual data of the DimProduct table from the source. What we are after is to rename all columns from all tables. So we can use the Table.TransformColumnNames(table as table, NameGenerator as function) function to rename all tables’ columns. We need to pass the values of the Data column to the table operand of the Table.TransformColumnNames() function. The second operand of the Table.TransformColumnNames() function requires a function to generate the names. In my example, the column names are CamelCased. So the NameGenerator function must transform a column name like EnglishProductName to English Product Name. As you see, I need to split the column name when the characters transit from lower case to upper case. I can achieve this by using the Splitter.SplitTextByCharacterTransition(before as anynonnull, after as anynonnull) function. So the expression to split the column names based on their character transition from lower case to upper case looks like below:

Splitter.SplitTextByCharacterTransition({"a".."z"}, {"A".."Z"})

As per the documentation , the Splitter.SplitTextByCharacterTransition() function returns a function that splits a text into a list of text. So the following expression is legitimate:

Splitter.SplitTextByCharacterTransition({"a".."z"}, {"A".."Z"})("EnglishProductName")

The following image shows the results of the above expression:

But what I need is not a list, I need a text that combines the values of the list separated by a space character. Such a text can be used for the column names. So I use the Text.Combine(texts as list, optional separator as nullable text) function to get the desired result. So my expression looks like below:

Text.Combine(
            Splitter.SplitTextByCharacterTransition({"a".."z"}, {"A".."Z"})("EnglishProductName")
            , " "
            )

Here is the result of the above expression:

So, we can now use the latter expression as the NameGenerator operand of the Table.TransformColumnNames() function with a minor modification; rather than a constant text we need to pass the column names to the Table.TransformColumnNames() function. The final expression looks like this:

Table.TransformColumnNames(
                    [Data]
                    , (OldColumnNames) => 
                    Text.Combine(Splitter.SplitTextByCharacterTransition({"a".."z"}, {"A".."Z"})(OldColumnNames)
                    , " ")
                    )

Now we can add a Custom Column with the preceding expression as shown in the image below:

The following image shows the contents of the DimProduct table with renamed columns:

The last piece of the puzzle is to navigate through the tables. It is very simple, just right click on a cell from the Columns Renamed column and click Add as a New Query from the context menu as shown in the following image:

And… here is the result:

Does it Fold?

This is indeed a fundamental question that you must always ask when dealing with the data sources that support Query Folding. And… the quick answer to that question is, yes it does. The following image shows the native query passed to the back-end data source by right-clicking the last step and clicking View Native Query:

If you are not familiar with the term “Query Folding”, I encourage you to learn more about it. Here are some good resources:

• Query folding basics
• Power Query query folding
• The importance of Query Folding
• Understanding folding with Query Diagnostics

Conclusion

As you see, we can use this technique to rename all tables’ columns in one base query. We should disable the query’s data load as we don’t need to load it into the data model. But keep in mind, we still need to expand every single table as a new query by right-clicking on each cell of the Columns Renamed column and selecting Add as a New Query from the context menu. The other point to note is that everyone’s cases can be different. In my case the column names are in CamelCase, this can be very different in your case. So I do not claim that we fully automated the whole process of renaming tables’ columns and navigating the tables. The table navigation part is still a bit laborious, but this technique can save a lot of development time.

As always, if you have a better idea I appreciate it if you can share it with us in the comments section below.