none
Ordering columns RRS feed

  • Question

  • I have a query which carried out, inter alia, the following steps:
    1.  Pivot tables some data to extract columns with dates, the row column header
    2.  Merges in two new columns from a separate table with [Min] and [Max] figures for each row.

    I now want to sort the columns so that the order has the [Min] and [Max] columns on the left.  This ensures that as the number of columns in the pivot table changes, those two columns are always in the same place.  The merge, of course, puts them on the extreme right.

    The standard Table.Reorder will not do it because it requires the column names:
        #"Reordered Columns" = Table.ReorderColumns(#"Merged Table",{ "Reference","Min", "Max","31/12/2017", "31/01/2018", "28/02/2018", etc... etc... })

    Those date column names will change, of course, as the pivot table grows (or reduces)  in size depending on the number of dates extracted, so this line will fail.

    Using a List.Sort on the headers didn't help me either as this sorts the dates as if they were text and I need the dates organised from earliest on the left to highest on the right after the Min, Max and Reference columns.

    How do I get the [Min], [Max] columns to be on the left, regardless of the headings of the other columns and regardless of how many columns there are?

    Thursday, August 2, 2018 1:20 PM

Answers

  • Hi

    Input table in blue (Table1) with a MIN and MAX column in the middle of other "date" columns desordered

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        MinMaxList = {"MIN","MAX"},
        TableColNamesAsList = Table.ColumnNames(Source),
        DatesListOnly = List.RemoveItems(TableColNamesAsList, MinMaxList),
        DatesListAsTable = Table.TransformColumnTypes(Table.FromList(DatesListOnly, null, {"Dates"}), {{"Dates", type date}}),
        DatesSortedAsText = Table.TransformColumnTypes(Table.Sort(DatesListAsTable,{{"Dates", Order.Ascending}}), {{"Dates", type text}}),
        DatesTableAsList = DatesSortedAsText[Dates],
        ReorderList = List.Combine({MinMaxList, DatesTableAsList}),
        SourceReordered = Table.ReorderColumns(Source, ReorderList)
    in
        SourceReordered

    • Marked as answer by MaBacc Thursday, August 2, 2018 5:32 PM
    Thursday, August 2, 2018 5:18 PM

All replies

  • Hi

    Input table in blue (Table1) with a MIN and MAX column in the middle of other "date" columns desordered

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        MinMaxList = {"MIN","MAX"},
        TableColNamesAsList = Table.ColumnNames(Source),
        DatesListOnly = List.RemoveItems(TableColNamesAsList, MinMaxList),
        DatesListAsTable = Table.TransformColumnTypes(Table.FromList(DatesListOnly, null, {"Dates"}), {{"Dates", type date}}),
        DatesSortedAsText = Table.TransformColumnTypes(Table.Sort(DatesListAsTable,{{"Dates", Order.Ascending}}), {{"Dates", type text}}),
        DatesTableAsList = DatesSortedAsText[Dates],
        ReorderList = List.Combine({MinMaxList, DatesTableAsList}),
        SourceReordered = Table.ReorderColumns(Source, ReorderList)
    in
        SourceReordered

    • Marked as answer by MaBacc Thursday, August 2, 2018 5:32 PM
    Thursday, August 2, 2018 5:18 PM
  • Thanks for the fast reply.  Got it.

    Malcolm

    Thursday, August 2, 2018 5:32 PM
  • Your welcome. Forgot to mention dates are in dd/mm/yyyy format
    + there might be a more straightforward/efficient way to do it…

    Thursday, August 2, 2018 5:38 PM