Tuesday, June 14, 2022
HomeBusiness IntelligenceA Energy Question Customized Operate to Rename all Columns at As soon...

A Energy Question Customized Operate to Rename all Columns at As soon as in a Desk


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

I’m concerned with a Energy BI improvement up to now few days. I bought some knowledge exported from varied techniques in numerous codecs, together with Excel, CSV and OData. The CSV recordsdata are knowledge export dumps from an ERP system. Working with ERP techniques might be very time consuming, particularly if you don’t have entry to the information mannequin, and also you get the information in uncooked format in CSV recordsdata. It’s difficult, as within the ERP techniques, the desk names and column names are usually not consumer pleasant in any respect, which is sensible. The ERP techniques are being utilized in varied environments for a lot of totally different clients with totally different necessities. So if we are able to get our fingers to the underlying knowledge mannequin, we see configuration tables holding column names. A few of the columns are customized constructed to cowl particular wants. The tables could have many columns that aren’t essentially helpful for analytical functions. So it’s fairly vital to have understanding of the underlying entity mannequin. Anyhow, I don’t wish to go off-topic.

The Drawback

So, right here is my situation. I obtained about 10 recordsdata, together with 15 tables. Some tables are fairly small, so I didn’t hassle. However a few of them are actually broad like having between 150 to 208 columns. Good!

Trying on the column names, they can’t be tougher to learn than they’re, and I’ve a number of tables like that. So I’ve to rename these columns to one thing extra readable, extra on this aspect of the story later.

Background

I emailed again to my buyer, asking for his or her assist. Fortunately they’ve a really good knowledge knowledgeable who additionally understands their ERP system in addition to the underlying entity mannequin. I emailed him all the present column names and requested if he can present extra user-friendly names. He replied me again with a mapping desk in Excel. Right here is an instance to indicate the Column Names Mapping desk:

Column Names Mapping

I used to be fairly proud of the mapping desk. Now, the subsequent step is to rename all columns relies on the mapping desk. Ouch! I’ve virtually 800 columns to rename. That’s actually a ache within the neck, and it doesn’t sound fairly proper to burn the mission time to rename 800 columns.

However wait, what about writing automating the rename course of? Like writing a customized operate to rename all columns directly? I recall I learn a superb weblog put up about renaming a number of columns in Energy Question that Gilbert Quevauvilliers wrote in 2018. I positively suggest taking a look at his weblog put up. So I need to do one thing much like what Gilbert did; making a customized operate that will get the unique columns names and brings again the brand new names. Then I take advantage of the customized operate in every desk to rename the columns. Simple!

The Answer

Earlier than we begin, I have to have my mapping desk in Energy BI. So I create a brand new desk utilizing the Enter Information performance. Then I copy the information from my mapping desk and paste it into the brand new desk. I title the brand new desk Column Names Mapping. The principle purpose to make use of the Enter Information performance as an alternative of getting knowledge immediately from the file (in my case an Excel file) is that if for any purpose sooner or later we miss the mapping file, the operate beneath can not discover the columns to map. Therefore, it brings again the unique column names. This implies if we created a knowledge mannequin on high of the brand new column names, then the entire mannequin breaks, which isn’t any good. I additionally disable load on the Column Names Mapping question as I don’t have to have it in my knowledge mannequin.

Creating fnRenameColumnsFromRefQuery Customized Operate

The operate reads by means of the Column Names Mapping desk and renames the columns of the question that we invoked the operate in when it finds the matching. If it doesn’t discover the matching, it leaves the column title is. Right here you go, that is the operate I got here up with.

//fnRenameColumnsFromRefQuery
(ColumnName as textual content) as textual content =>
let
    Supply = 
        if (
            Checklist.Incorporates(
                File.FieldNames(#sections[Section1]), 
                "Column Names Mapping"
                ) 
             ) = true 
        then #"Column Names Mapping" 
        else null,
    ColumnNewName = 
        strive 
            if Checklist.Incorporates(Supply[Column Name], ColumnName) = true 
            then 
                if Textual content.Trim(Desk.SelectRows(Supply, every ([Column Name] = ColumnName)){0}[Description]) = "" 
                then ColumnName 
                else Desk.SelectRows(Supply, every ([Column Name] = ColumnName)){0}[Description] 
            else Supply 
        in any other case ColumnName
in
    ColumnNewName

You’ll be able to obtain the previous expressions from right here.

Right here is the way it works:

  • The fnRenameColumnsFromRefQuery accepts a ColumnName parameter as textual content and the output of the operate is a textual content.
  • The Supply step checks the existence of a Column Names Mapping question. The Column Names Mapping is the mapping tables proven within the previous picture that holds the unique column names and their mapping. You might use a special title of alternative. If the Column Names Mapping question exists then Supply = #"Column Names Mappings" else Supply = null
  • The ColumnNewName step checks the contents of the Supply step which is both the Column Names Mapping desk or a null. If it’s the Column Names Mapping desk then it seems to be for the ColumnName parameter within the [Column Name] column throughout the Column Names Mapping desk. If it finds the matching worth then it makes use of the ColumnName parameter to filters the [Column Name]. Then it will get the corresponding worth from the [Description] column which comprises the brand new column title, in any other case it brings the unique column title again

The final step is to make use of the fnRenameColumnsFromRefQuery customized operate to rename the desk’s columns.

Invoking the fnRenameColumnsFromRefQuery Customized Operate

Energy Question has a operate to renamed column names in tables which is the Desk.TransformColumnNames(desk as desk, NameGenerator as operate) operate. The Desk.TransformColumnNames() operate accepts a NameGenerator as its second operand. So we are able to use our customized operate because the second operand throughout the Desk.TransformColumnNames() operate to rename all columns. The ultimate code should appear like this:

Desk.TransformColumnNames(PREVIOUS_STEP_NAME, fnRenameColumnsFromRefQuery)

Here’s a screenshot of the column names earlier than and after renaming the columns:

Renaming columns using a reference list in Power Query
Renaming columns utilizing a reference record in Energy Question

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

Conclusion

If we predict what we’re doing is kind of laborious and we’re burning loads of mission time doing that, it’s good to consider a strategy to automate the job. It’d look a bit complicated, complicated and time-consuming at first, however consider me, in lots of instances it’s value it and after you discover the best way you should utilize it in lots of different related eventualities which saves you loads of time. Having mentioned that, there isn’t a assure that we are able to automate our situation, so it could be higher to be ready for it. As an illustration, it’s a very long time since I’m pondering to automate the method of renaming all queries showing within the Question Editor, however I’m but to discover a answer for it. Right here is the problem, in some instances, we hook up with the information supply, and we’ve got many tables to be loaded to the mannequin, and the desk names are usually not at all times consumer pleasant. It’s much like renaming columns however on the question stage. So should you already know the answer, please share it with the group so everybody can profit out of your efforts. On the finish of the day, we study from one another. So sharing our information is a paying ahead to the subsequent particular person.

The opposite level is to at all times keep in mind to ask different specialists for assist. We is likely to be specialists in some areas however it’s not possible for somebody being an knowledgeable in all the pieces. Like in my case the shopper had an knowledgeable who offered me with the column names mapping desk, with out his assist I couldn’t get the job achieved.

As at all times, I’d like to know your opinion, so please share your ideas within the remark part beneath.

RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular

Recent Comments