cancel
Showing results for 
Search instead for 
Did you mean: 
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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors