CODEWITHSUNDEEP
sql-serverpowerbipowerquerypowerbi-desktop
A846h
A846h

Reputation: 65

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

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.

Upvotes: 2

Views: 3489

Answers (1)

Wedge
Wedge

Reputation: 1826

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.

Upvotes: 4

Related Questions