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

[water-guy-5]

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!!!!!

[Jakinta]

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

[AlexisOlson]

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 · Accepted solution]

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)