cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Power Participant
Power Participant

Auto-rename all Query columns e.g. to remove underscore

Today I was grappling with shaping a series of Queries based on a SQL Datawarehouse.  They use what's probably the best column naming standard (IMO) e.g. Sales_Amount.  However PBI Desktop doesnt seem to have any built-in way to change those in bulk - I wanted to replace underscores with space e.g. "Sales Amount".

 

After a bit of a wrestle I came up with a Query Step Function that will do just that:

 

Table.RenameColumns(#"Changed Type", Table.ToRows(Table.AddColumn(Table.FromList(Table.ColumnNames( #"Changed Type")), "New Column Name", each Text.Replace ( [Column1] , "_" , " " ))))

 

The easiest way to use this is to manually rename a single column - that will generate a "Rename Columns" step.  Then copy and paste that formula to replace the generated formula.  It assumes the prior Step was called "Changed Type", so edit that to suit. 

 

It is totally dynamic so it will adjust to any table or to any edits you make in prior steps.  You could extend it e.g. with Text.Proper to tidy up column names that are all upper or lower case.

25 REPLIES 25
Highlighted
Resident Rockstar
Resident Rockstar

Re: Auto-rename all Query columns e.g. to remove underscore

Nice to see a programmatic solution to this. I would likely have done something with using headers as first row, transposing, doing a replace on the now-column of headers, re-transposing, and promoting to headers again.

 

Yours seems much more elegant.

Highlighted
Power Participant
Power Participant

Re: Auto-rename all Query columns e.g. to remove underscore

Thats a nice solution! Personally I prefer using the UI rather than coding (for ease of understanding, debugging and maintenance).

 

But I tried it on a large-ish table (170,000 rows) and it stalled on the first Transpose.  I imagine at that point it has to load all the rows and generate 170,000 columns ...

 

So I'll probably stick with my method, unless it's guaranteed that there wont be too many rows.

Highlighted
Microsoft
Microsoft

Re: Auto-rename all Query columns e.g. to remove underscore

Meanwhile the author of "Table.TransformColumnNames" (hint: me) is crying somewhere :'( I think it came out in July '15 update.

 

Nice code anyway 🙂

Highlighted
Resident Rockstar
Resident Rockstar

Re: Auto-rename all Query columns e.g. to remove underscore

I've gotta be honest, Power Query is not too discoverable on its own, @arify. This is not a criticism of you, but the UI guys. The latest iteration of the DAX editor in PBI is pretty decent. The Power Query editor is pretty dismal. There's no intellisense or anything similar to help identify which functions are available. It's clunky to cross back and forth from PQ to the formula reference online and back.

Highlighted
Microsoft
Microsoft

Re: Auto-rename all Query columns e.g. to remove underscore

You're right, I wish it had something like Intellisense too, but I'm not sure if it'll happen anytime soon. Instead, normally I'd recommend people to discover functions at 2 places

Highlighted
Resident Rockstar
Resident Rockstar

Re: Auto-rename all Query columns e.g. to remove underscore

@arify, may I ask why you don't see any sort of intellisense coming soon? This is well out of my wheelhouse, but it seems like with the strong similarities to F#, a lot of the work will have already been done by th VS team.

 

Is it just low priority or am I missing something?

Highlighted
Microsoft
Microsoft

Re: Auto-rename all Query columns e.g. to remove underscore

@greggyb, intellisense for M is definitely coming. It's got a slightly lower priority but we constantly talk about it. 

 

There's also an user voice thing for it:

https://ideas.powerbi.com/forums/265200-power-bi/suggestions/7202913-improve-the-advanced-query-edit...

 

 

 

Highlighted
Resident Rockstar
Resident Rockstar

Re: Auto-rename all Query columns e.g. to remove underscore

@pqian, thanks much. I don't get enough time to review the user voice ideas that much. I've added some votes to that item.

 

I'm actually still learning Power Query, but I like very much what I see. I'm still learning its performance characteristics and what good idioms are, but it's a fun process.

Highlighted
Power Participant
Power Participant

Re: Auto-rename all Query columns e.g. to remove underscore

@arify - I hadn't heard of that one and nothing like that came up after a lot of searching.  Now you have named a specific function that sounds great but ... I just tried searching for "Table.TransformColumnNames" and I didnt get any relevant results (besides your mention on this thread). I can't see it listed in the MSDN PQ function doco either.  Do you have any info you can share on it?

 

My 2c would be that developing code changes without any published mention or doco (including several practical examples) is basically pointless ...

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors