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
VP's avatar
VP
Icon for Most Valuable Professional rankMost Valuable Professional
9 years ago

If column heading is changing from file to file and not column order, Following is my work around

 

  1. Demoted Headers:-From Transformation > select Use Header as first row
  2. Change Column name to what you want
  3. Delete first row:- Home > Remove top row (input = 1)

 

 

MarcelBeug's avatar
MarcelBeug
Icon for Community Champion rankCommunity Champion
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