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

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

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