How to edit/rename multiple column names at the same time in Power Query
For those who may not know, Power Query is an engine attached to Excel, Power BI, Power Apps, Power Automate, Azure data factory etc. and it is used for data transformation and data preparation, with a very lovely graphical interface for getting data from different sources, including an editor that could be applied to transform the data. This allows you to easily perform ETL (Extract, Transform and Load) process on your data.
Apart from the GUI, Power Query also allow users to utilize the M formula language, which is an alternative when trying to perform some operations that cannot be done using the graphical user interface. One of this is what we are addressing in this article, which is how to edit or rename multiple column at the same time using the M formula language within power query.
The Steps
1. We start by removing a text we do not want from a column name manually by double clicking or right clicking and selecting Rename.
2. A new applied step would be generated. From the image example below, a new applied step was generated with the name “Renamed Columns”
3. Ensure the new step is selected to enter the following M script to replace the function auto generated on that step using the following syntax
= Table.TransformColumnNames(#”Previous Step”, (columnName as text) as text => Text.Replace(columnName, “text you want to replace”, “”))
In our case, “Expanded Column1” as seen in step 2 happens to be the previous step and we are editing the M script on Renamed Columns by changing texts with “Column1.” with nothing. Therefore, our script should look like this:
= Table.TransformColumnNames(#”Expanded Column1", (columnName as text) as text => Text.Replace(columnName, “Column1.”, “”))
You will have to continue this step for every other set of column name you want to make changes to, as long as they are different from the previous one changed.
For example, now that we have all our column with the text “Column1.” Replaced with blank, we want to remove column that contains the word “group_facilitydetails/”.
4. We start by removing this name manually from one of the column by double clicking or right clicking and selecting Rename
5. Once this is done, a new step is generated in the applied steps, automatically named as Renamed Columns1, which follows the one we previously edited above.
6. Once we are on it, looking at the function field, we then have something like what we have in the image below
7. Click inside this field and edit this step, so it can rename every other column with the text “group_facilitydetails/”. Like what we did above, we use the following function
= Table.TransformColumnNames(#”Previous Step”, (columnName as text) as text => Text.Replace(columnName, “text you want to replace”, “”))
Since our previous step as shown in the step above was “Renamed Column” and we want to change the columns with the text “group_facilitydetails/” with blank, we would type in the following script in the M function field as shown below
= Table.TransformColumnNames(#”Renamed Columns”, (columnName as text) as text => Text.Replace(columnName, “group_facilitydetails/”, “”))
