Forum Discussion
Advocate IReorder columns in Query Editor without listing the column names
--------TO SUM UP--------
I want query to see, how many columns my file has, and rename last two rows based on that count.
If there are 20 columns, I want the 19th and 20th column to be renamed.
If there are 17 columns, I want the 16th and 17th, correspondingly.
So it might be something like this:
= Table.RenameColumns(#"Promoted Headers",{{Table.ColumnNames(#"Promoted Headers",{{Table.ColumnCount(#"Promoted Headers")}}, "Status"}})
Here I tried to count the columns, pass the number to Table.ColumnNames, and rename the column with that order number.
But I am wrong in syntax, and, maybe, in understanding.
-------------------------------------------------------------------------------------
Hi, dears,
I am combining multiple Excel workbooks using a function as decribed in this article:
http://www.excelguru.ca/blog/2015/02/25/combine-multiple-excel-workbooks-in-power-query/
There are some garbage rows in the beginning, which I remove, however, they contain two rows of necessary data (date and status - like Q2 2017 and Budget). I extract those inserting custom columns and filling them down in order to keep this info. Then I promote all headers. Thus, the last two columns have different names for different files.
Now, as my column structure may be a bit different in different files, I have to rename those last two columns in one manner so that they can be matched across the files. I know I can use
= Table.RenameColumns(#"Promoted Headers",{{Table.ColumnNames(#"Promoted Headers"){34}, "Date"}})
to rename those columns but due to different structure of columns "34" may need to change.
I tried reordering the last two columns to be able to use the "0" and "1" in renaming, but to do that, M uses the names of columns, which leads to an error in other different files.
I feel there is a way to accomplish this, but I can't quite figure out how at the moment.
Would be very grateful for any tips.
Regards,
It's difficult to understand everything, but it looks like you want to rename the last 2 columns in a table, which can be done e.g. as follows:
let Source = Table1, NewNames = {"LastBut1Col", "LastCol"}, CurrentNames = List.LastN(Table.ColumnNames(Source),2), RenameList = List.Zip({CurrentNames,NewNames}), RenamedColumns = Table.RenameColumns(Source,RenameList) in RenamedColumns
6 Replies
- vanessafvg
Community Champion
are you wanting to move the placement of the column?
if so it can be done via this
- foyiq
Hi, vanessafvg,
Well, to put it simple - yes. But there is a tricky point: those column names differ in each file (as I promote the headers to match other identical columns in different files.
You see, the M language requires the name of at least the column being moved to be listed in the query code. I could use the rename the column by using this type of a query step:
= Table.RenameColumns(#"Promoted Headers",{{Table.ColumnNames(#"Promoted Headers"){0}, "FILE"}})
but the problem is that the order changes, since these are the custom columns, and the source files may have a different number of columns.
- foyiq
So it might be something like this:
= Table.RenameColumns(#"Promoted Headers",{{Table.ColumnNames(#"Promoted Headers",{{Table.ColumnCount(#"Promoted Headers")}}, "Status"}})
Here I tried to count the columns, pass the number to Table.ColumnNames, and rename the column with that order number.
But I am wrong in syntax, and, maybe, in understanding.
- vanessafvg
i think someone like MarcelBeug is probably a good person to ask, cos i haven't got a clue :)
- MarcelBeug
It's difficult to understand everything, but it looks like you want to rename the last 2 columns in a table, which can be done e.g. as follows:
let Source = Table1, NewNames = {"LastBut1Col", "LastCol"}, CurrentNames = List.LastN(Table.ColumnNames(Source),2), RenameList = List.Zip({CurrentNames,NewNames}), RenamedColumns = Table.RenameColumns(Source,RenameList) in RenamedColumns
