Renaming Columns by Column Number

[CPaceFOTL]

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 to month.  Example, JAN-17 Shipments, FEB-17 Shipments, etc.

This causes issues when refreshing the data and the column has now changed names as well as in the visual that I am performing some filtering on.

I have been searching, but have not found a way to rename a column by its column number verse the column name.  Is this possible?  Any help would be greatly appreciated.

Thanks,

Craig

[Anonymous]

Rename first N columns (first 2 columns in this example):

= Table.RenameColumns(#"Promoted Headers",List.Zip({List.FirstN(Table.ColumnNames(#"Promoted Headers"),2), {"NewColumn1","NewColumn2"}}))

[jamesleslie]

Here's how to rename the first column in Power Query, without needing to know its name:

= Table.RenameColumns(#"Previous step",{{Table.ColumnNames(#"Previous step"){0}, "New name"}})

[amirshiloh]

Great ! Thanks !

[VP]

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 · Accepted solution]

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]

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]

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?

[Anonymous]

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]

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