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

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

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.

Why Do We Get An Error?

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

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:

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:

• Table.RenameColumns (Microsoft documentation)
• Table.ColumnNames (Microsoft documentation)

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.