How to reorder columns in Power Query editor by referring to column position instead of column name

1
votes

I have a SQL query as a data source which pulls in a table into Power Query. The columns in this table have month headings for the past 12 months. Therefore, every month when the data refreshes, the column names change by one month. There are still the same number of columns in total.

I'm pretty new to the Power Query language so I wasn't sure how I could go about reordering the columns by referring to their position instead of the name as it is doing at the moment

This is what my 'reordered columns' code currently looks like:

#"Reordered Columns" = Table.ReorderColumns(Source,{"Cat", "Type", "Function", "Organisation", "Locality", "SubOrganisation", "2018-08", "2018-09", "2018-10", "2018-11", "2018-12", "2019-01", "2019-02", "2019-03", "2019-04", "2019-05", "2019-06", "2019-07"})

I don't want to specify the column names when reordering as every month, the last month will fall off and a new month will be added.

1
sql-serverpowerbipowerquerypowerbi-desktop
How exactly do you want the columns to be ordered in the end? – Nick Krasnov
I just need to bring the 'Function' and 'Organisation' columns to the front but because the date columns are specified, I have to manually change this each month. – A846h

1 Answers

3
votes
Table.ReorderColumns
    (Source,
        {"Cat", "Type", "Function", "Organisation", "Locality", "SubOrganisation"} //List of static columns in order we want
        & //Lists can be appended with this
        List.RemoveItems( //Function that removes a list of items from another list
            Table.ColumnNames(Source) //List of all column names,
            {"Cat", "Type", "Function", "Organisation", "Locality", "SubOrganisation"} //List of static columns (to be removed)
            )
    )

What's going on here is first we're taking the list of columns you always have and setting them in whatever order you want. Then we're taking the list of all the column names and removing the column names you always have from that list. The result is then appended to the static list of columns you always have. This way it doesn't matter what the date columns are, they will always be tacked onto the end of the order.