none
Sort Order of worksheet table loaded by power query RRS feed

  • Question

  • Hi,

    I have a PQ query that loads to a tale in the spreadsheet.  The query is also loaded into the data model and I add some measures in PP to make use of the various relationships and because DAX is much more performant than PQ.  I have an index column which I sort by as the final step in PQ.  But, this sort order is not honoured when I update.  I tried sorting in PQ as well but this didn't help.  Any help on how to fix this behavior?


    I did notice that the DateTime field that I have as one column is sorted in order.  It seems like PQ is being opinionated about which column it should sort by maybe?
    • Edited by Cool.Blue Monday, April 30, 2018 1:23 PM
    Monday, April 30, 2018 1:20 PM

Answers

  • Thanks for the inspiration, I didn't see what you suggested in Excel 2016 but, I found it under

    Table Tools/Design/Properties and then check the Preserve column sort/filter/layout box.

    • Marked as answer by Cool.Blue Tuesday, May 1, 2018 8:25 AM
    Tuesday, May 1, 2018 8:25 AM

All replies

  • do you need the order to be proper only in PowerPivot or in both PowerPivot and table loaded to worksheet?
    If PowerPivot is crucial then you can set custom order in:
    In Excel it's under Design>Sort by Column
    In PowerBI: Modeling>Sort by Column
    Monday, April 30, 2018 3:46 PM
  • When a query is loaded to the worksheet and the Data Model, the ordering of the data in the worksheet is determined by PowerPivot, not by Power Query. The steps suggested by the previous poster should get you the correct sorting in the sheet.
    Tuesday, May 1, 2018 6:50 AM
  • Thanks for the inspiration, I didn't see what you suggested in Excel 2016 but, I found it under

    Table Tools/Design/Properties and then check the Preserve column sort/filter/layout box.

    • Marked as answer by Cool.Blue Tuesday, May 1, 2018 8:25 AM
    Tuesday, May 1, 2018 8:25 AM