cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mike_honey
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
DonalMc
Helper II
Helper II

hi - completely new to M so please bear with me

 

If I enter the below as a column I =in the PBI Query editor, I get "Expression.Error: The name 'table' wasn't recognized. Make sure it's spelled correctly."

= Table.TransformColumnNames(table, (columnName as text) as text => Text.Replace(columnName, "_", ""))

 

So I presumbed that I was to replace table with my table name and entered the below and I get "Expression.Error: A cyclic reference was encountered during evaluation."

= Table.TransformColumnNames(#"Project Cache", (columnName as text) as text => Text.Replace(columnName, "_", ""))

 

I just need to get past the first hurdle!

 

Also can Table.TransformColumnNames add spaces before capitals?, e.g. transform "EstDurationAtCompletion" into "Est Duration At Completion"

 

Thanks!

You are so close - you just need to replace "table" with the previous STEP name (not table/query name). If that step name has spaces or special characters it will need to be enclosed e.g. #"My Previous Step Name".

 

It's a weird feature of the M language. It's not what I'd call a "script" language where the sequence of the lines of code controls the flow. In the code generated by the Power Query UI, there's actually a reference to the prior line of code, on every line.  Technically that returns a table with all the data "as at" that prior step.

mike - thanks a bunch - I should have seen that. 

Anonymous
Not applicable

This is good. I guess there's no way to generically reference WhateverThePreviousStepsNameIs ? No steps linked list or anything?

@Anonymous - I've never seen one, but that doesnt mean it doesnt exist - there is so much obscure syntax in PQL ... 

 

The whole step names thing is such a pain - I've been programming since the 80s and never struck anything so clunky.

arify
Microsoft
Microsoft

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

 

Nice code anyway 🙂

cdknuth
Frequent Visitor

Hi @arify,

 

How would I accomplish this:

 

I'd like to rename my columns either on import or immediately following.

 

Example name that comes in: [DIM A].[YEAR].[YEAR].[MEMBER_CAPTION]

 

Name I want to display: Year

I'm struggling to understand from the documentation how Table.TransformColumnNames works.  Any chance you could provide a simple example, e.g. prefixing every column of a table called DimDate with the string "Order "?

mike_honey
Power Participant
Power Participant

@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 ...

Hi @mike_honey,

 

We're still working on updating our documentation. Next time you needed a library function that doesn't appear in the documentation, try typing

= #shared

to the Power Query's formula bar like I suggested to greggyb. But 99% of the time, the function is already mentioned in a nicer way in the documentation, so that should be the first try.

 

About how to use it, there's some explanation and an example when you type

= Table.TransformColumnNames

to the PQ formula bar. In your case, it should probably look like this:

= Table.TransformColumnNames(table, (columnName as text) as text => Text.Replace(columnName, "_", ""))

 

ks1
Frequent Visitor

Hi What's the right syntax for replacing several text strings?

 

I tried:

 

= Table.TransformColumnNames(table, (columnName as text) as text => Text.Replace(columnName, {"_", ""}, {"other text", ""}))

 

 ...but that didn't work

mike_honey
Power Participant
Power Participant

@ks1 You've probably figured this out monts ago, but you would need to nest Text.Replace, e.g.

 

Text.Replace(Text.Replace(columnName, {"_", ""}), {"other text", ""})

 

mike_honey
Power Participant
Power Participant

thanks @arify - at least that function is exposed now.  I found your last example in the thread above much more useful than the one posted in the documentation. That scenario (special characters in column headings) seems very unlikely. 

 

Anyway I had to loop back to this to do something similar - inserting spaces for "Camel Case" column names e.g. CustomerName.  So I have built a couple of examples using Table.TransformColumnNames and added it to my list of demo files (link below) for future reference - it's the one named:

 

Power Query demo - Renaming Columns automatically

 

http://1drv.ms/1AzPAZp

 

 

@mike_honey - would love to see this demo file.  I checked, but perhaps it's no longer in the OneDrive location?  Any way for me to access it?

 

Thanks,

Ryan

@thephotobus  - its still there.  Try the button at top-right to change to list view.

@mike_honey I just downloaded it and tried it out, it's pretty cool! I want to look around at that folder some time 🙂

greggyb
Resident Rockstar
Resident Rockstar

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.

Hi @greggyb,

as you say the Power Query Editor is not really helpfull. This why I created my own Editor, including IntelliSense and synthax highlighting using Notepad++. Follow this link. Maybe this is helpfull for you:

http://power-bi-usergroup.blogspot.de/2015/11/creating-editor-for-power-query-with.html?m=0

Regards,
Lars

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors