Forum Discussion

mike_honey's avatar
mike_honey
Memorable Member
10 years ago

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

Today I was grappling with shaping a series of Queries based on a SQL Datawarehouse.  They use what's probably the best column naming standard (IMO) e.g. Sales_Amount.  However PBI Desktop doesnt see...
Tips and Tricks
DonalMc's avatar
DonalMc
Advocate II
6 years ago

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!

mike_honey's avatar
mike_honey
Memorable Member
6 years ago

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.

  • DonalMc's avatar
    DonalMc
    Advocate II
    6 years ago

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