I am involved with a Power BI development in the past few days. I got some data exported from various systems in different formats, including Excel, CSV and OData. The CSV files are data export dumps from an ERP system. Working with ERP systems can be very time consuming, especially when you don’t have access to the data model, and you get the data in raw format in CSV files. It is challenging, as in the ERP systems, the table names and column names are not user friendly at all, which makes sense. The ERP systems are being used in various environments for many different customers with different requirements. So if we can get our hands to the underlying data model, we see configuration tables keeping column names. Some of the columns are custom built to cover specific needs. The tables may have many columns that are not necessarily useful for analytical purposes. So it is quite critical to have a good understanding of the underlying entity model. Anyhow, I don’t want to go off-topic.
The Problem
So, here is my scenario. I received about 10 files, including 15 tables. Some tables are quite small, so I didn’t bother. But some of them are really wide like having between 150 to 208 columns. Nice!
Looking at the column names, they cannot be more difficult to read than they are, and I have multiple tables like that. So I have to rename those columns to something more readable, more on this side of the story later.
Background
I emailed back to my customer, asking for their help. Luckily they have a very nice data expert who also understands their ERP system as well as the underlying entity model. I emailed him all the current column names and asked if he can provide more user-friendly names. He replied me back with a mapping table in Excel. Here is an example to show the Column Names Mapping table:
I was quite happy with the mapping table. Now, the next step is to rename all columns is based on the mapping table. Ouch! I have almost 800 columns to rename. That is literally a pain in the neck, and it doesn’t sound quite right to burn the project time to rename 800 columns.
But wait, what about writing automating the rename process? Like writing a custom function to rename all columns at once? I recall I read an excellent blog post about renaming multiple columns in Power Query that Gilbert Quevauvilliers wrote in 2018. I definitely recommend looking at his blog post. So I must do something similar to what Gilbert did; creating a custom function that gets the original columns names and brings back the new names. Then I use the custom function in each table to rename the columns. Easy!
Continue reading “A Power Query Custom Function to Rename all Columns at Once in a Table”