Rename Columns Based on Position

Share The Knowledge

In Power Query I select columns based on their name, unpivot other columns and load the data into the data model. Later, if the source columns get renamed, I get an error when I refresh the query.

Set-up

Here is the Excel source data. To use this data in Power BI data model it needs to be re-arranged (unpivoted). We should have 4 columns:

  • Country
  • City
  • Vehicle
  • Amount
An Excel table called “Table1”.

I load it into Power Query, select columns Country & City and unpivot other columns. The UnpivotOtherColumns step is seen below:

Power Query unpivots the data. Each column is now a distinct entity

I rename columns Attribute & Value. My steps generate this M code:

let
    Source = Excel.Workbook(File.Contents("C:\PBI local files\0001 source file (dynamically rename columns)3.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],

    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Country", type text}, {"City", type text}, {"Car", Int64.Type}, {"Truck", Int64.Type}, {"Van", Int64.Type}}),

    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Country", "City"}, "Attribute", "Value"),

    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Vehicle"}, {"Value", "Amount"}})
in
    #"Renamed Columns"

Select ‘Close & Apply’ to push the query into the data model as a table.

A table in Power BI data model called Table1

Why Do We Get An Error?

Someone renames Columns Country & City in the Excel source file.

Columns Country & City renamed to Pais & Ciudad.

The Power Query steps that selected columns Country & City based on their names (not column positions) can’t find them.

I see an error like this:

Power Query can’t find column Country.

Rename Columns Based on Position!

Fortunately we can rename columns based on horizontal position and refer to them using our new names in subsequent transformation steps!

Text following “//” are comments. Here’s my modified M code:

let
    Source = Excel.Workbook(File.Contents("C:\PBI local files\0001 source file (dynamically rename columns)3.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],

    //RENAME THE COLUMN IN POSITION 1 (LEFT MOST COLUMN)
    #"RenameColumn1" = Table.RenameColumns(Table1_Table,{{Table.ColumnNames(Table1_Table){0},"CountryName"}}),

    //RENAME THE COLUMN IN POSITION 2 (LEFT TO RIGHT)
    #"RenameColumn2" = Table.RenameColumns(#"RenameColumn1",{{Table.ColumnNames(#"RenameColumn1"){1},"CityName"}}),

    //MODIFY "Changed Type" TO REFERENCE THE NEW COLUMN NAMES
    #"Changed Type" = Table.TransformColumnTypes(#"RenameColumn2",{{"CountryName", type text}, {"CityName", type text}, {"Car", Int64.Type}, {"Truck", Int64.Type}, {"Van", Int64.Type}}),

    //MODIFY "Unpivoted Other Columns" TO REF NEW COLUMN NAMES
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"CountryName", "CityName"}, "Attribute", "Value"),

    //RENAME COLUMNS Attribute to Vehicle, Value to Amount
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Vehicle"}, {"Value", "Amount"}})
in
    #"Renamed Columns"

Now I can refresh my query that connects to the Excel source.

Regardless of what the names of the first two columns are I have renamed them in Power Query and will always refer to them using their new names (“CountryName” and “CityName”).

In the code above {0} refers to the 1st column (renamed to “CountryName”) and {1} refers to the 2nd column (renamed to “CityName”).

Now let’s explore the M steps further!

Exploring M Code

Below, bird’s eye view of the original steps (M code details removed)

let
    Source = ,

    Table1_Table = ,

    #"Changed Type" = ,

    #"Unpivoted Other Columns" = ,

    #"Renamed Columns" = 

in
    #"Renamed Columns"

Below, modified M code including column rename steps!

let
    Source = ,

    Table1_Table = ,

    //RENAME THE COLUMN IN POSITION 1 (LEFT MOST COLUMN)
    #"RenameColumn1" = ,

    //RENAME THE COLUMN IN POSITION 2 (LEFT TO RIGHT)
    #"RenameColumn2" = ,

    //MODIFY "Changed Type" TO REF NEW COLUMN NAMES
    #"Changed Type" = ,

    //MODIFY "Unpivoted Other Columns" TO REF NEW COLUMN NAMES
    #"Unpivoted Other Columns" = ,

    //RENAME COLUMNS Attribute to Vehicle, Value to Amount
    #"Renamed Columns" = 

in
    #"Renamed Columns"

Key M functions used:

Below we see the first column rename step:

 //RENAME THE COLUMN IN POSITION 1 (LEFT MOST COLUMN)
    #"RenameColumn1" = Table.RenameColumns(Table1_Table,{{Table.ColumnNames(Table1_Table){0},"CountryName"}}),
  

Table.RenameColumns needs a table to refer to. In my case I refer to Table1_Table (the step above) that eventually refers back to Excel and references the 1st column by using {0} and renaming it to “CountryName”.

Microsoft shows us that we could include a table inside Table.RenameColumns function. Below, Table.FromRecords creates the fields and data. Then column “CustomerNum” is changed to “CustomerID”.

Table.RenameColumns(
    Table.FromRecords({[CustomerNum = 1, Name = "Bob", Phone = "123-4567"]}),
    {"CustomerNum", "CustomerID"}
)

Step to create the full M code in Power Query:

  • Home
  • New Source
  • Blank Query
  • Paste M code above into the formula bar, press enter

Below we see the step and outcome:

And this is the full M code found in ‘Advanced Editor’:

let
    Source = Table.RenameColumns(
    Table.FromRecords({[CustomerNum = 1, Name = "Bob", Phone = "123-4567"]}),
    {"CustomerNum", "CustomerID"}
    )
in
    Source

The source for the data is created inside the source step! Click ‘Close & Apply’ to see the query become a table in the Power BI data model.

About Me

This is my personal blog to practice learning Power BI (Power Query, DAX, etc) and share my knowledge with you.


Share The Knowledge

Leave a Reply

Your email address will not be published. Required fields are marked *