Dynamically renaming all columns' headers based on previous columns' headers if they match a condition RRS feed

  • Question

  • Dear Everyone,

    The Data Source I receive is a pivoted report in MS-Excel format. During my ETL experiment using Power Query, I come to a point where I'd need to rename columns' headers based on the value of the preceding column.


    E.g. below, the Pattern 303xxxxxx ColumnY repeats itself and this is what I'll need to "unpivot".

    • AGoodColumn1   AGoodColumn2   AGoodColumn3   AGoodColumn4    303123456   Column1   303123457   Column2 303123458   Column3 etc.

    I would need to obtain, i.e. appending the columns' headers of the previous column to each column that starts with "Column".

    • AGoodColumn1   AGoodColumn2   AGoodColumn3   AGoodColumn4    303123456   303123456-Column1   303123457   303123457-Column2 303123458   303123458-Column3 etc.

    Is there a way to do this in Power Query? After this I am sure I'd be able to perform the "unpivot" step and process my data perfectly.

    Thanks for your assistance,

    Saturday, October 7, 2017 1:27 AM


  • You can create a rename list as a list with pairs (old column name, new column name).
    So this is a list of list.
    This list you can use to actually rename your columns.

        Source = Table1,
        ColumnNames = Table.ColumnNames(Table1),
        ColumnNamesAndPrevious = List.Zip({ColumnNames,{null}&ColumnNames}),
        #"Removed Bottom Items" = List.RemoveLastN(ColumnNamesAndPrevious,1),
        RenameList = List.Transform(#"Removed Bottom Items", each if Text.StartsWith(_{0},"Column") then {_{0}, _{1} & "-" & _{0}} else {_{0}, _{0}}),
        #"Renamed Columns" = Table.RenameColumns(Source,RenameList)
        #"Renamed Columns"

    Saturday, October 7, 2017 8:48 AM