none
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

Answers

  • 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.

    let
        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)
    in
        #"Renamed Columns"

    Saturday, October 7, 2017 8:48 AM