Forum Discussion

Till__'s avatar
Till__
Helper I
3 years ago
Solved

Renaming multiple Headers

Dear all, 

 

I have multiple Headers which contain an ID before some text occurs. For example like in the table below.

EF-1.1.1) Text1EF-1.1.2) Text 2EF-2.1.1) Text 3  

 

Is there a way to rename all headers by deleting the text? My idea is to work with a list contains EF- code and then to split at the first bracket and deleting the rest of the text so that my end reslut looks like that: 

EF-1.1.1) EF-1.1.2) EF-2.1.1)

 

 

Thank you in advance! 

Need Help
  • BA_Pete's avatar
    BA_Pete
    3 years ago

     

    Okay, try this as a custom step instead:

     

    Table.RenameColumns(
    previousStepName,
    List.Transform(
        Table.ColumnNames(previousStepName),
        each {_, if Text.StartsWith(_, "EF") then Text.BeforeDelimiter(_, ")") else _}
    )
)

     

    Working example query:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUUoC4mQgTgHiVKXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"EF-1.1.1) Some text" = _t, #"EF-1.1.2) Some Words" = _t, #"EF-2.1.1) Some Writing" = _t, #"AB-1.1.1) Some Stuff" = _t, #"TT-1.1.1) Blah Blah" = _t]),
    
    Custom2 =
    Table.RenameColumns(
        Source,
        List.Transform(
            Table.ColumnNames(Source),
            each {_, if Text.StartsWith(_, "EF") then Text.BeforeDelimiter(_, ")") else _}
        )
    )
    
in
    Custom2

     

    Example output:

     

    Pete

4 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    3 years ago

    Hi Till__ ,

     

    You can use the following as a custom step:

    = Table.TransformColumnNames(previousStepName, each Text.BeforeDelimiter(_, ")"))

     

    Working example query:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUUoC4mSl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"EF-1.1.1) Some text" = _t, #"EF-1.1.2) Some Words" = _t, #"EF-2.1.1) Some Writing" = _t]),
    Custom1 = Table.TransformColumnNames(Source, each Text.BeforeDelimiter(_, ")"))
in
    Custom1

     

    Example output:

     

    Pete

    • Till__'s avatar
      Till__
      Helper I
      3 years ago

      Dear BA_Pete, I want to do this only for columns which include the name "EF-"

       

      I was trying to do this with the following code but this did not work out... Do you have an idea? 


      = Table.TransformColumnNames(#"Erweiterte Tabellenspalte1", each Text.BeforeDelimiter(_, ")",List.Select(Table.ColumnNames(#"Erweiterte Tabellenspalte1"), each Text.Contains(_, "EF-"))))

      • BA_Pete's avatar
        BA_Pete
        Super User
        3 years ago

         

        Okay, try this as a custom step instead:

         

        Table.RenameColumns(
    previousStepName,
    List.Transform(
        Table.ColumnNames(previousStepName),
        each {_, if Text.StartsWith(_, "EF") then Text.BeforeDelimiter(_, ")") else _}
    )
)

         

        Working example query:

         

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUUoC4mQgTgHiVKXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"EF-1.1.1) Some text" = _t, #"EF-1.1.2) Some Words" = _t, #"EF-2.1.1) Some Writing" = _t, #"AB-1.1.1) Some Stuff" = _t, #"TT-1.1.1) Blah Blah" = _t]),
    
    Custom2 =
    Table.RenameColumns(
        Source,
        List.Transform(
            Table.ColumnNames(Source),
            each {_, if Text.StartsWith(_, "EF") then Text.BeforeDelimiter(_, ")") else _}
        )
    )
    
in
    Custom2

         

        Example output:

         

        Pete