![excel query table column order excel query table column order](https://www.seerinteractive.com/wp-content/uploads/2018/11/PowerBI_Legend_Order_Step3.png)
The Column Names Mapping is the mapping tables shown in the preceding image that holds the original column names and their mapping.
![excel query table column order excel query table column order](https://wmfexcel.files.wordpress.com/2018/06/excel-tip-no-more-copy-and-paste-with-pq_case1.png)
Excel query table column order download#
You can download the preceding expressions from here. If Text.Trim(Table.SelectRows(Source, each ( = ColumnName)) If List.Contains(Source, ColumnName) = true Here you go, this is the function I came up with. If it doesn’t find the matching, it leaves the column name is. The function reads through the Column Names Mapping table and renames the columns of the query that we invoked the function in when it finds the matching. Creating fnRenameColumnsFromRefQuery Custom Function I also disable load on the Column Names Mapping query as I don’t need to have it in my data model. This means if we created a data model on top of the new column names, then the whole model breaks, which is no good. Hence, it brings back the original column names. The main reason to use the Enter Data functionality instead of getting data directly from the file (in my case an Excel file) is that if for any reason in the future we miss the mapping file, the function below cannot find the columns to map. I name the new table Column Names Mapping. Then I copy the data from my mapping table and paste it into the new table. So I create a new table using the Enter Data functionality. Easy! The Solutionīefore we start, I need to have my mapping table in Power BI. Then I use the custom function in each table to rename the columns. 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. I definitely recommend looking at his blog post. That is literally a pain in the neck, and it doesn’t sound quite right to burn the project time to rename 800 columns.īut 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. Ouch! I have almost 800 columns to rename. Now, the next step is to rename all columns is based on the mapping table. I was quite happy with the mapping table. Here is an example to show the Column Names Mapping table: He replied me back with a mapping table in Excel. I emailed him all the current column names and asked if he can provide more user-friendly names. Luckily they have a very nice data expert who also understands their ERP system as well as the underlying entity model. I emailed back to my customer, asking for their help. So I have to rename those columns to something more readable, more on this side of the story later. Looking at the column names, they cannot be more difficult to read than they are, and I have multiple tables like that. But some of them are really wide like having between 150 to 208 columns. Some tables are quite small, so I didn’t bother. I received about 10 files, including 15 tables.
![excel query table column order excel query table column order](https://i0.wp.com/www.thebiccountant.com/WP/wp-content/uploads/2019/02/Target.jpg)
So it is quite critical to have a good understanding of the underlying entity model. The tables may have many columns that are not necessarily useful for analytical purposes. Some of the columns are custom built to cover specific needs. So if we can get our hands to the underlying data model, we see configuration tables keeping column names. The ERP systems are being used in various environments for many different customers with different requirements. It is challenging, as in the ERP systems, the table names and column names are not user friendly at all, which makes sense. 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.
![excel query table column order excel query table column order](https://cdn.ablebits.com/_img-blog/swap-columns/mouse-pointer-drag-column.png)
The CSV files are data export dumps from an ERP system. I got some data exported from various systems in different formats, including Excel, CSV and OData. I am involved with a Power BI development in the past few days.