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
arify Established Member
Established Member

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

 

arify Established Member
Established Member

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

mike_honey
Advisor

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

 

 

arify Established Member
Established Member

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

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

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

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?

mike_honey
Advisor

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

 

mike_honey
Advisor

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

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 340 members 3,348 guests
Please welcome our newest community members: