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

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:

The Results of Sql.Database() Function in Power Query
The results of running the Sql.Database(Server, DB) function

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:

Filtering out SQL Server Functions After Connecting from Power Query
Filtering out SQL Server Functions

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:

The Contents of a Table Value in Power Query
The Contents of a Table Value in Power Query

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:

Results of Running Splitter.SplitTextByCharacterTransition Function with Text Input in Power Query
Results of Running the Splitter.SplitTextByCharacterTransition() Function with Text Input

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:

Using Text.Combine() function in Power Query
Using Text.Combine() Function

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:

Adding a Custom Column Containing Tables with Renamed Columns in Power Query
Adding a Custom Column Containing Tables with Renamed Columns

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

The Contents of Table Values with Renamed Columns in Power Query
The Contents of Table Values 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:

Navigating Through Table Values in Power Query
Navigating Through Table Values

And… here is the result:

Table Value Expanded as a New Query in Power Query
The Table Value Expanded as a New Query

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:

View Native Query for Query Folding in Power Query Power BI
Native Query Passed to SQL Server

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

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 if you can share it with us in the comments section below.

You can download the sample PBIX file from here.

2 thoughts on “Quick Tips: Renaming All Tables’ Columns in One Go in Power Query

  1. Is it possible to include all the columns of a table or view of a database on report visual …in the same order as source ?

Any thoughts? Share it with us here:

This site uses Akismet to reduce spam. Learn how your comment data is processed.