Renaming All Tables’ Columns in One Go in Energy Question

Date:


Renaming All Tables' Columns in One Go in Power Query

I beforehand wrote a weblog publish explaining the best way to rename all columns in a desk in a single go together with Energy Question. One in all my guests raised a query within the feedback in regards to the risk to rename all columns from all tables in a single go. Curiously sufficient, considered one of my prospects had an analogous requirement. So I believed it’s good to write down a Fast Tip explaining the best way to meet the requirement.

The Downside

You’re connecting to the info sources from Energy BI Desktop (or Excel or Information Flows). The columns of the supply tables usually are not consumer pleasant, so that you require to rename all columns. You already know the best way to rename all columns of a desk in a single go however you’d like to use the renaming columns patterns to all tables.

The Answer

The answer is kind of easy. We require to connect with the supply, however we don’t navigate to any tables right away. In my case, my supply desk is an on-premises SQL Server. So I connect with the SQL Server occasion utilizing the Sql.Database(Server, DB) perform in Energy Question the place the Server and the DB are question parameters. Learn extra about question parameters right here. The outcomes would really like the next picture:

The Results of Sql.Database() Function in Power Query
The outcomes of operating the Sql.Database(Server, DB) perform

As you see within the above picture, the outcomes embody Tables, Views and Capabilities. We aren’t curious about Capabilities subsequently we simply filter them out. The next picture exhibits the outcomes after making use of the filter:

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

If we glance nearer to the Information column, we see that the column is certainly a Structured Column. The structured values of the Information column are Desk values. If we click on on a cell (not on the Desk worth of the cell), we are able to see the precise underlying information, as proven within the following picture:

The Contents of a Table Value in Power Query
The Contents of a Desk Worth in Energy Question

Because the above picture illustrates, the chosen cell incorporates the precise information of the DimProduct desk from the supply. What we’re after is to rename all columns from all tables. So we are able to use the Desk.TransformColumnNames(desk as desk, NameGenerator as perform) perform to rename all tables’ columns. We have to cross the values of the Information column to the desk operand of the Desk.TransformColumnNames() perform. The second operand of the Desk.TransformColumnNames() perform requires a perform to generate the names. In my instance, the column names are CamelCased. So the NameGenerator perform should rework a column title like EnglishProductName to English Product Identify. As you see, I would like to separate the column title when the characters transit from decrease case to higher case. I can obtain this through the use of the Splitter.SplitTextByCharacterTransition(earlier than as anynonnull, after as anynonnull) perform. So the expression to separate the column names based mostly on their character transition from decrease case to higher case seems to be like beneath:

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

As per the documentation , the Splitter.SplitTextByCharacterTransition() perform returns a perform that splits a textual content into an inventory of textual content. So the next expression is professional:

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

The next picture exhibits the outcomes of the above expression:

Results of Running Splitter.SplitTextByCharacterTransition Function with Text Input in Power Query
Outcomes of Working the Splitter.SplitTextByCharacterTransition() Operate with Textual content Enter

However what I would like isn’t an inventory, I would like a textual content that mixes the values of the checklist separated by an area character. Such a textual content can be utilized for the column names. So I take advantage of the Textual content.Mix(texts as checklist, optionally available separator as nullable textual content) perform to get the specified consequence. So my expression seems to be like beneath:

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

Right here is the results of the above expression:

Using Text.Combine() function in Power Query
Utilizing Textual content.Mix() Operate

So, we are able to now use the latter expression because the NameGenerator operand of the Desk.TransformColumnNames() perform with a minor modification; somewhat than a relentless textual content we have to cross the column names to the Desk.TransformColumnNames() perform. The ultimate expression seems to be like this:

Desk.TransformColumnNames(
                    [Data]
                    , (OldColumnNames) => 
                    Textual content.Mix(Splitter.SplitTextByCharacterTransition({"a".."z"}, {"A".."Z"})(OldColumnNames)
                    , " ")
                    )

Now we are able to add a Customized Column with the previous expression as proven within the picture beneath:

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

The next picture exhibits the contents of the DimProduct desk with renamed columns:

The Contents of Table Values with Renamed Columns in Power Query
The Contents of Desk Values with Renamed Columns

The final piece of the puzzle is to navigate by means of the tables. It is rather easy, good click on on a cell from the Columns Renamed column and click on Add as a New Question from the context menu as proven within the following picture:

Navigating Through Table Values in Power Query
Navigating By Desk Values

And… right here is the consequence:

Table Value Expanded as a New Query in Power Query
The Desk Worth Expanded as a New Question

Does it Fold?

That is certainly a elementary query that you should all the time ask when coping with the info sources that assist Question Folding. And… the fast reply to that query is, sure it does. The next picture exhibits the native question handed to the back-end information supply by right-clicking the final step and clicking View Native Question:

View Native Query for Query Folding in Power Query Power BI
Native Question Handed to SQL Server

In case you are not acquainted with the time period “Question Folding”, I encourage you to study extra about it. Listed below are some good assets:

Conclusion

As you see, we are able to use this system to rename all tables’ columns in a single base question. We should always disable the question’s information load as we don’t have to load it into the info mannequin. However remember, we nonetheless have to increase each single desk as a brand new question by right-clicking on every cell of the Columns Renamed column and choosing Add as a New Question from the context menu. The opposite level to notice is that everybody’s instances will be completely different. In my case the column names are in CamelCase, this may be very completely different in your case. So I don’t declare that we absolutely automated the entire technique of renaming tables’ columns and navigating the tables. The desk navigation half remains to be a bit laborious, however this system can save a variety of growth time.

As all the time, in case you have a greater thought I respect should you can share it with us within the feedback part beneath.

You’ll be able to obtain the pattern PBIX file from right here.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Share post:

Subscribe

spot_imgspot_img

Popular

More like this
Related

Wells Fargo CTO joins Financial institution Automation Summit US 2023

Steve Hagerman, chief expertise officer at Wells Fargo,...

A Information for 2023 Tax Season Webinar — Q&A

In our current webinar, "A Information for 2023...

Monetary literacy 101: the one information you may want to start out managing your funds like a professional

Monetary literacy 101: the one information you may...

What Is A Listicle? Write One For Content material Technique Worth

These days, listicles typically get a nasty rap...
%d bloggers like this: