Reorder columns in Query Editor without listing the column names

[foyiq]

--------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,

[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 · Accepted solution]

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

[foyiq]

Thank you! This is exactly what I wanted!

[vanessafvg]

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.