Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

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!

Need Help
  • AnkitBI's avatar
    AnkitBI
    6 years ago

    Hi Anonymous 

     

    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.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous ，

    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
     
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks both, it works great!

  • AnkitBI's avatar
    AnkitBI
    Icon for Solution Sage rankSolution Sage
    6 years ago

    Hi Anonymous 

     

    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.