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

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

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

Highlighted
Frequent Visitor

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

@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

Highlighted
Power Participant
Power Participant

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

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

Highlighted
Advocate I
Advocate I

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

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!

Highlighted
Power Participant
Power Participant

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

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.

Highlighted
Advocate I
Advocate I

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

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

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

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

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors