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

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
Highlighted
Microsoft
Microsoft

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

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

 

Highlighted
Microsoft
Microsoft

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

Highlighted
Power Participant
Power Participant

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

 

 

Highlighted
Microsoft
Microsoft

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 🙂

Highlighted
Helper III
Helper III

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

Highlighted
New Member

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

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

Anonymous
Not applicable

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?

Highlighted
Power Participant
Power Participant

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

@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
Power Participant
Power Participant

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

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

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors