Reply
Advisor
Posts: 58
Registered: ‎11-12-2015

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

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
Established Member
Posts: 192
Registered: ‎01-08-2016

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

[ Edited ]

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, "_", ""))

 

Established Member
Posts: 192
Registered: ‎01-08-2016

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

Advisor
Posts: 281
Registered: ‎06-28-2015

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

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

 

 

Established Member
Posts: 192
Registered: ‎01-08-2016

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

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

Member
Posts: 60
Registered: ‎01-28-2016

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

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 "?

ks1 Occasional Visitor
Occasional Visitor
Posts: 1
Registered: ‎02-23-2017

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

[ Edited ]

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

Regular Visitor
Posts: 46
Registered: ‎07-21-2016

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

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

Advisor
Posts: 281
Registered: ‎06-28-2015

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

[ Edited ]

@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", ""})

 

Highlighted
Advisor
Posts: 281
Registered: ‎06-28-2015

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

[ Edited ]

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