locked
Losing PQ sort when column is removed RRS feed

  • Question

  • I have several PQs to build a spreadsheet from a SQL database and since the data is variable using Ken's fnGetParameter and that works great.  Because there are six sections and the values can be user selected the columns were moving around if I used one query.  I broke it up to multiples like below.  A total of four.  When the code runs and the last line, removed time, the data is then unsorted.  I need to be able to use this to insert the data without the time column.  Any thoughts?

    let
        DB = #"fnGetParameter"("DB"),
        Run = #"fnGetParameter"("Run"),
        P1 = #"fnGetParameter"("P1"),
       Source = Sql.Database("server", DB),
        dbo_datatable = Source{[Schema="dbo",Item="datatable"]}[Data],
        #"Filtered Rows" = Table.SelectRows(dbo_datatable, each ([RUN] = Run)),
        #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{P1, "TIME"}),
        #"Sorted Rows" = Table.Sort(#"Removed Other Columns",{{"TIME", Order.Ascending}}),
        #"Removed Time" = Table.SelectColumns(#"Filtered Rows",{P1})
    in
        #"Removed Time"

    Thanks,

    Bruce


    • Edited by Bruce Stirl Tuesday, October 4, 2016 10:18 PM
    Tuesday, October 4, 2016 10:17 PM

Answers

  • You can try to buffer the step like this:

        #"Sorted Rows" = Table.Buffer(Table.Sort(#"Removed Other Columns",{{"TIME", Order.Ascending}})),

    But I heard that there is an issue with sorting when you load to the data model - so you'd better check that thoroughly.


    Imke Feldmann TheBIccountant.com

    • Marked as answer by Bruce Stirl Wednesday, October 5, 2016 1:53 PM
    Wednesday, October 5, 2016 6:59 AM

All replies

  • You can try to buffer the step like this:

        #"Sorted Rows" = Table.Buffer(Table.Sort(#"Removed Other Columns",{{"TIME", Order.Ascending}})),

    But I heard that there is an issue with sorting when you load to the data model - so you'd better check that thoroughly.


    Imke Feldmann TheBIccountant.com

    • Marked as answer by Bruce Stirl Wednesday, October 5, 2016 1:53 PM
    Wednesday, October 5, 2016 6:59 AM
  • Thanks Imke,

    This seems to work well.  Fortunately I am not loading/using the data model at this time.

    Best,

    Bruce

    Wednesday, October 5, 2016 1:54 PM