cancel
Showing results for 
Search instead for 
Did you mean: 
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

7 REPLIES 7
jamesleslie
Frequent Visitor

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

 

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)

View solution in original post

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?

scottsen
Memorable Member
Memorable Member

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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors