Forum Discussion

cgkas's avatar
cgkas
Icon for Helper V rankHelper V
7 years ago
Solved

Rename all column headers removing certain text

Hello to all,

 

I want to remove some text ("ABC.XYZ_") from all column headers.

 

The code I have so far is like this but for the List of second step I get error.

let
    Source = Table.FromRows({{1,2,3,4}},{"ABC.XYZ_4_NB.5", "ABC.XYZ_2_NB.9","ABC.XYZ_7_NB.3", "ABC.XYZ_K_MJ.3"}),
    RenameHeaders = List.Transform(Table.ColumnNames(Source), each {_, Text.Remove(_,"ABC.XYZ_")}),
    Output = Table.RenameColumns(Source, RenameHeaders)
in
    Output

Maybe some could help me with this.

 

Thanks

Need Help
  • d_gosbell's avatar
    d_gosbell
    7 years ago

    Try changing the Text.Remove to Text.Replace and replace with an empty string

     

    eg.

     

    let
    Source = Table.FromRows({{1,2,3,4}},{"ABC.XYZ_4_NB.5", "ABC.XYZ_2_NB.9","ABC.XYZ_7_NB.3", "ABC.XYZ_K_MJ.3"}),
    RenameHeaders = List.Transform(Table.ColumnNames(Source), each {_, Text.Replace(_,"ABC.XYZ_","")}),
    Output = Table.RenameColumns(Source, RenameHeaders)
in
    Output

5 Replies

  • d_gosbell's avatar
    d_gosbell
    Icon for Super User rankSuper User
    7 years ago

    Try changing the Text.Remove to Text.Replace and replace with an empty string

     

    eg.

     

    let
    Source = Table.FromRows({{1,2,3,4}},{"ABC.XYZ_4_NB.5", "ABC.XYZ_2_NB.9","ABC.XYZ_7_NB.3", "ABC.XYZ_K_MJ.3"}),
    RenameHeaders = List.Transform(Table.ColumnNames(Source), each {_, Text.Replace(_,"ABC.XYZ_","")}),
    Output = Table.RenameColumns(Source, RenameHeaders)
in
    Output
    • spags09's avatar
      spags09
      New Member
      3 years ago

      is there a way to use an OR operator or remove multiple characters from the headers...

       

      Trying to replace the underscore and NonAgg from my headers. I am able to remove the NonAgg but not able to remove the underscore and would like to. 

       

      here is my code

      let
      Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
      RenameHeaders = List.Transform(Table.ColumnNames(Source), each {_, Text.Replace(_,"NonAgg"," ")}),
      Output = Table.RenameColumns(Source, RenameHeaders)
      in
      Output

      • d_gosbell's avatar
        d_gosbell
        Icon for Super User rankSuper User
        3 years ago
        spags09 wrote:

        is there a way to use an OR operator or remove multiple characters from the headers...

         

        I don't think you can use OR in Text.Replace, but there are a couple of other approaches

         

        Option 1: you could use List.Accumulate to loop over a list of unwanted characters/strings and remove them


        let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0MlaKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column_1 = _t, #"Column NonAgg Sales" = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column 1", type text}, {"Column NonAgg Sales", Int64.Type}}),
        OriginalColumnNames = Table.ColumnNames(Source),
        ReplacementList = {"_", "NonAgg"},
        RenameHeaders = List.Transform(OriginalColumnNames, (columnName) => { columnName, List.Accumulate( ReplacementList, columnName, (state, current) => Text.Replace(state, current," "))}),
        Output = Table.RenameColumns(Source, RenameHeaders)
        in
        Output

         

        Option 2: You could do a sequence of List.Transform to apply the text.replacements one after the other. This is probably easier to understand, but tricker to add multiple replacement strings as you have to chain the steps together

         

        let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0MlaKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column_1 = _t, #"Column NonAgg Sales" = _t]),
        OriginalColumnNames = Table.ColumnNames(Source),
        RenameHeaders1 = List.Transform(OriginalColumnNames, each Text.Replace(_,"NonAgg"," ")),
        RenameHeaders2 = List.Transform(RenameHeaders1, each Text.Replace(_,"_"," ")),
        NewColumnNames = List.Zip({OriginalColumnNames,RenameHeaders2}),
        Output = Table.RenameColumns(Source, NewColumnNames)
        in
        Output