Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
CPaceFOTL
Frequent Visitor

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

1 ACCEPTED SOLUTION
MarcelBeug
Community Champion
Community Champion

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
Specializing in Power Query Formula Language (M)

View solution in original post

9 REPLIES 9
DenisSipchenko
Advocate III
Advocate III

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
Advocate I
Advocate I

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"}})

 

Great ! Thanks !

VP
MVP

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
Community Champion
Community Champion

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
Specializing in Power Query Formula Language (M)

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

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
Not applicable

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.