none
PowerQuery - Remove substring and character from header

    Question

  • Hi!

    I frequently receive tables with a lot of columns and I am tired of renaming them one by one with the Table.RenameColumns formula.

    The tables I get from the IT team all have headers that I need to change. 

    Here is an easy example :

    Sales[Date]

    Sales[Number]

    Sales[Client]

    Sales[Amount]

    I need to remove the source table name, in this case Sales, and the [] surrounding the real column name.

    Date

    Number

    Client

    Amount

    The source table name can also be, Inventory, Invoice, Employee... Not always the same number of characters.



    • Edited by PLRD Tuesday, October 13, 2015 7:08 PM
    Tuesday, October 13, 2015 1:30 PM

Answers

  • Let's have fun splitting hairs... :-)
    To possibly improve readability, based on Bill's elegant solution:

    let
        Source = Table.FromRows({{"Poznan/Dublin",22,"Max Minimus",100,"something"}},{"Sales[City]", "Sales[Number]","Sales[Client]", "Amount", "Invoice[Description]"}),
        NewHeaders = List.Transform(Table.ColumnNames(Source), (ColumnName) => {ColumnName,try Splitter.SplitTextByAnyDelimiter({"[","]"})(ColumnName){1} otherwise ColumnName}),
        #"Renamed Columns" = Table.RenameColumns(Source, NewHeaders )
    in
        #"Renamed Columns" 





    • Edited by Bertrand d'Arbonneau Wednesday, October 14, 2015 4:50 PM
    • Marked as answer by PLRD Thursday, October 15, 2015 11:53 AM
    Wednesday, October 14, 2015 4:38 PM

All replies

  • In these situations, I usually create a table in a worksheet to handle the renaming of the columns. Then this table is used in the query to dynamically rename the columns from the source file.

    In the example below, the table that contains the old and new column names is created in the step 'Columns' but you could replace that with a reference to a worksheet table.

    let
        Source = Table.FromRows({{"New York", 23, 51, 732}, {"Chicago", 25, 421, 23}, {"Los Angeles", 632, 22, 423}}, {"City", "Col 1", "Col 2", "Col 3"}),
        Columns = Table.FromRows({{"Col 1", "Column 1"}, {"Col 2", "Column 2"}, {"Col 3", "Column 3"}}, {"Name", "Value"}),
        ColumnsAsRenames = Table.TransformRows(Columns, Record.FieldValues),
        RenamedColumns = Table.RenameColumns(Source, ColumnsAsRenames)
    in
        RenamedColumns
    

    Tuesday, October 13, 2015 5:45 PM
  • Wow! Really nice code. Made me realize that there is actually a function to create a list of lists :-)

    With this we could actually create a dynamic renaming of the example above:

    let
        Source = Table.FromRows({{"New York", 23, 51, 732}, {"Chicago", 25, 421, 23}, {"Los Angeles", 632, 22, 423}}, {"Sales[City]", "Sales[Col 1]", "Sales[Col 2]", "Sales[Col 3]"}),
        GetColumnNames = Table.ColumnNames(Source),
        ConvertToTable = Table.FromList(GetColumnNames, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        SplitCol = Table.SplitColumn(ConvertToTable,"Column1",Splitter.SplitTextByDelimiter("[", QuoteStyle.None),{"Column1", "Name"}),
        SkipSqrBracket = Table.ReplaceValue(SplitCol,"]","",Replacer.ReplaceText,{"Name"}),
        Columns = Table.FromColumns({Table.ColumnNames(Source), SkipSqrBracket[Name]}),
        ColumnsAsRenames = Table.TransformRows(Columns, Record.FieldValues),
        RenamedColumns = Table.RenameColumns(Source, ColumnsAsRenames)
    in
        RenamedColumns

    @ Bda75: How do you insert your code that it looks so nice?


    Imke Feldmann TheBIccountant.com

    Tuesday, October 13, 2015 7:56 PM
    Moderator
  • Imke,

    To insert code, I use the "html" button which allows to insert code blocks into posts. I leave the language option empty.
     

    Wednesday, October 14, 2015 7:08 AM
  • That' s my two cents ;-)

    let
        Source = Table.FromRows({{"Poznan/Dublin",22,"Max Minimus",100,"something"}},{"Sales[City]", "Sales[Number]","Sales[Client]", "Amount", "Invoice[Description]"}),
        NewHeaders = List.Transform(Table.ColumnNames(Source), each {_,try Text.Remove(Text.Split(_,"["){1},"]") otherwise _}),
        Result = Table.RenameColumns(Source, NewHeaders)
    in
        Result

    Regards


    Wednesday, October 14, 2015 4:02 PM
  • Let's have fun splitting hairs... :-)
    To possibly improve readability, based on Bill's elegant solution:

    let
        Source = Table.FromRows({{"Poznan/Dublin",22,"Max Minimus",100,"something"}},{"Sales[City]", "Sales[Number]","Sales[Client]", "Amount", "Invoice[Description]"}),
        NewHeaders = List.Transform(Table.ColumnNames(Source), (ColumnName) => {ColumnName,try Splitter.SplitTextByAnyDelimiter({"[","]"})(ColumnName){1} otherwise ColumnName}),
        #"Renamed Columns" = Table.RenameColumns(Source, NewHeaders )
    in
        #"Renamed Columns" 





    • Edited by Bertrand d'Arbonneau Wednesday, October 14, 2015 4:50 PM
    • Marked as answer by PLRD Thursday, October 15, 2015 11:53 AM
    Wednesday, October 14, 2015 4:38 PM
  • Thanks to Bda75 and Bill Szysz! Exactly what I needed.
    Thursday, October 15, 2015 11:54 AM