Power Query (M) - if column header contains specific text, rename that column header

[Tamerlane]

Hi everyone,

I am trying to format some imported column headers to my desired output (we are migrating from Dynamics AX2012 to Finance and Operations).

I imported a maintenance table where all the column headers were all in uppercase letters. First step I did was to Capitalize all column headers, thanks to this query in M (advanced editor within Power Query):

let

    Source = Sql.Database("secret.database.windows.net", "AxDW", [Query="SELECT * FROM MaintenanceTable"]),

    #"Renamed Columns" = Table.TransformColumnNames(Source, Text.Proper)

in

    #"Renamed Columns"

Now I want to rename each column header that contains the word "key"  (this will always be at the end of a header name), to the same name, but with a '%' sign inserted at the start of that name. If the word "key" was not found, just leave the column name as it is.

Anyone who knows the solution? Thanks in advance!

[v-yiruan-msft]

Hi @Tamerlane ，

If there are not much column names needs to be modified, you can directly select the related column name, right-click and choose rename option to directly modify the column name.
 

In addition, you can write m query code to achieve it dynamically by the following steps. Here is the related sample PBIX file.

1. Insert one Step
 

2. Write the codes just like as @AnkitBI provided in formula bar

Best Regards

Rena
 

[Tamerlane]

Thanks both, it works great!

[AnkitBI · Accepted solution]

Hi @Tamerlane 

Try below. Custom1 will cater for all combination like Key,key etc. I have changed order for Text.Proper as we need it after Custom1.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXICYmcgdlGKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [account = _t, Name = _t, PrimaryKey = _t, ProductKey = _t]),
    Custom1 = Table.TransformColumnNames(Source, each if Text.Contains(Text.Upper(_),"KEY") then "%" & Text.Replace(Text.Upper(_),"KEY","") else _),
    Custom2 = Table.TransformColumnNames(Custom1,Text.Proper)
in
    Custom2

Thanks
Ankit Jain
Do Mark it as solution if the response resolved your problem. Do Kudo the response if it seems good and helpful.