Forum Discussion

CPaceFOTL's avatar
CPaceFOTL
Frequent Visitor
9 years ago
Solved

Renaming Columns by Column Number

I have been working with the Power BI Desktop to create reports based on a CSV file.  Things are going well; however, the headers in the CSV files have some "dynamic" headers which change from month ...
Need Help
  • MarcelBeug's avatar
    MarcelBeug
    9 years ago

    This function renames columns based on their position.

    The second and third argument are lists, each with the same number of elements.

    ColumnNumbers is 0-based, so the first column is 0.

     

    let
    RenameColumns = (InputTable as table, ColumnNumbers as list, NewColumnNames as list) =>
let
    OldColumnNames = Table.ColumnNames(InputTable),
    Indexed = List.Zip({OldColumnNames, {0..-1+List.Count(OldColumnNames)}}),
    Filtered = List.Select(Indexed, each List.Contains(ColumnNumbers,_{1})),
    IndexRemoved = List.Transform(Filtered, each _{0}),
    RenameList = List.Zip({IndexRemoved,NewColumnNames}),
    RenamedColumns = Table.RenameColumns(InputTable, RenameList)
in
    RenamedColumns
in
    RenameColumns
Anonymous's avatar
Anonymous
Not applicable
9 years ago

You are going to want to "UNPIVOT" those columns in "Get & Transform", and likely have a separate date table (that relates to the dates that are now in a single column).

CPaceFOTL's avatar
CPaceFOTL
Frequent Visitor
9 years ago

Scottsen,

 

Thank you for that infomation; however, these columns do not actually contain dates.  This is both forecasted and actual shipment totals for the current, previous and next month.  The problem is that the columns are coming from the system with the true month name instead of a static column name.

 

I could just manually rename each of the columns, but I was hoping there is a way to rename column no n so that I would only have to do this to five columns instead of all the columns in the data.  Please note that when the data is being retrieve, I am promoting the first row as the headers, so that is what the last reference is for.

 

Thanks,

 

Craig