Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
water-guy-5
Helper III
Helper III

How to change column names for a query with 200+ columns

Hello,

I have a table with about 200 columns, and I do not want to have to manually change all of my entries.

About 15 columns are individual, where the column name starts with a unique text string, however the other 185 columns begin with "field_" and then has the column name. For the sake of reporting, I would like some sort of M code that allows me to remove the first 6 characters when 'field_' is present or remove 'field_' or something along those lines. Thanks!

I have very little knowledge when it comes to Power Query... the most I have done is seperate row entries with delimiters, I feel like this is slightly similar but don't know how since it has to do with a column...thanks!!!!!

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

Here's an example that you can paste into the Advanced Editor:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUaoAYkMQNgASRnqGJkqxOtFKSUBOJUgAiE1BsnqWFkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, field_Col3 = _t, field_Col4 = _t, field_Col5 = _t]),
    NewColNames = List.Transform(Table.ColumnNames(Source), each if Text.Start(_, 6) = "field_" then Text.AfterDelimiter(_, "_") else _),
    ChangeColNames = Table.FromColumns(Table.ToColumns(Source), NewColNames)
in
    ChangeColNames

 

 

This takes the Table.ColumnNames as a list and transforms is according to the rule you specified. It then splits the table into columns and recombines them back into a table using the new column names.

 

The key bits of code are the list transformation rule (the underscore represents each column name):

each if Text.Start(_, 6) = "field_" then Text.AfterDelimiter(_, "_") else _

and the recombination

Table.FromColumns(Table.ToColumns(Source), NewColNames)

 

View solution in original post

3 REPLIES 3
Jakinta
Solution Sage
Solution Sage

Here is another approach.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Jc+xFcMwDEPBXVS7MAnASWbx8/5rhF8uoApHUve9ah2rJ5p4ksk1+Uy+k9+kTh6aRbXoFuXKeo6ZAShEQQrTmN7TMY1pTLOhEY1oRCN07mHCaJ+EEUYYYYQRRhizxQgjvH+B8HuZMcYYY0wwwQQTTPbX2RJEEEEEcc1lzx8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Field_10 = _t, Column2 = _t, Field_11 = _t, Field_12 = _t, Field_13 = _t, Field_14 = _t, Field_15 = _t, Column3 = _t, Field_16 = _t, Field_17 = _t, Field_18 = _t, Field_19 = _t, Field_20 = _t, Field_21 = _t]),
    Cols = Table.ColumnNames(Source),
    New = List.Transform(Cols, each Replacer.ReplaceText(_,"Field_","")),
    Custom1 = Table.RenameColumns(Source,List.Zip({Cols,New}))
in
    Custom1

Ooh, nice use of List.Zip. I often overlook that function since I forget exactly what it does and the MS documentation examples for that function aren't very helpful.

 

Better documentation:

https://excel.city/2017/11/how-to-use-list-zip-in-power-query/

 

A similar renaming solution using List.Zip:

https://datachant.com/2017/01/26/power-bi-pitfall-6/

 

AlexisOlson
Super User
Super User

Here's an example that you can paste into the Advanced Editor:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUaoAYkMQNgASRnqGJkqxOtFKSUBOJUgAiE1BsnqWFkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, field_Col3 = _t, field_Col4 = _t, field_Col5 = _t]),
    NewColNames = List.Transform(Table.ColumnNames(Source), each if Text.Start(_, 6) = "field_" then Text.AfterDelimiter(_, "_") else _),
    ChangeColNames = Table.FromColumns(Table.ToColumns(Source), NewColNames)
in
    ChangeColNames

 

 

This takes the Table.ColumnNames as a list and transforms is according to the rule you specified. It then splits the table into columns and recombines them back into a table using the new column names.

 

The key bits of code are the list transformation rule (the underscore represents each column name):

each if Text.Start(_, 6) = "field_" then Text.AfterDelimiter(_, "_") else _

and the recombination

Table.FromColumns(Table.ToColumns(Source), NewColNames)

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors