Question

How do I dynamically rename multiple columns in Power Query based on a mapping table?
I am working on a Power BI project where I need to dynamically rename multiple columns in Power Query based on a mapping table. The mapping table contains the original column names and their corresponding new names. What is the best approach to automate this renaming process using M code?

Answer 1

To dynamically rename multiple columns in Power Query based on a mapping table, you can use the following approach with M code. The mapping table will contain the original column names and their new names, and you will loop through the columns to rename them based on the mapping.

Here’s a step-by-step method:

Steps:

1. Load the Mapping Table
   Ensure that your mapping table contains two columns: OriginalName and NewName. This table will map the old column names to the new ones.

2. Transform the Data Table
   Use the Table.RenameColumns function along with a List.Transform to apply the renaming dynamically.

M Code Example:

let
    // Step 1: Load your data and the mapping table
    Source = YourDataSource,  // Replace with your actual data source
    MappingTable = YourMappingTable,  // Replace with your mapping table

    // Step 2: Create a list of column renaming rules based on the mapping table
    RenameRules = List.Transform(
        Table.ToRecords(MappingTable), 
        each { [OriginalName = [OriginalName], NewName = [NewName]] }
    ),

    // Step 3: Rename columns based on the mapping table
    RenamedColumns = Table.RenameColumns(Source, RenameRules)
in
    RenamedColumns

Explanation:

• Source: This is the table you want to apply the column renaming to (your data table).

• MappingTable: This is the table with the original column names (OriginalName) and the corresponding new names (NewName).

• RenameRules: This step generates a list of rules for renaming by converting the mapping table into records and applying them to the Table.RenameColumns function.

• RenamedColumns: This step applies the renaming process to the data table using the dynamically created rules.