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
Most 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
    Community 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
    • JeevanSandhu's avatar
      JeevanSandhu
      Regular Visitor
      6 years ago

      Thank you so much MarcelBeug !!!

      We struggle a bit to apply the function so we thought of adding an example.

       

      1. Create a blank query and copy paste the whole function in the advanced editor (-> the new query will have the name of Query1)

      2. In your query (the one you're working in), you can add a line similar to the below :

       

       

      #"Rename Colum" = Query1(#"Promoted Headers", {2}, {"Date"}),

       

       

      In this example:

      - "Rename Column" is the new code line

      - "Query1" is the new function created

      - "Promoted Headers" is our previous line code

      - "2" is the third column of our table

      - "Date" is the new name of column

    • Recoba88's avatar
      Recoba88
      Helper III
      9 years ago

      This is my code:

       

      let
          Source = Excel.Workbook(File.Contents("file:///\\bllshare\DavWWWRoot\sites\testdocs\TEST_BRM2018\EST18\ב.דיגיטאלית\עסקים%20תמ17.xlsx"), null, true),
          hadash_DefinedName = Source{[Item="hadash",Kind="DefinedName"]}[Data]
      in
          hadash_DefinedName

       

       

       

       

      Where I should enter your code? and can you make example (print screen) before and after how I will see the table?