cancel
Showing results for 
Search instead for 
Did you mean: 
0

BUG - Sort by Date in Query Editor

Sorting by date in the query editor does not apply changes to the table in question. December 2021 version.

Status: Delivered

User edhans explained it clearly.

Comments
edhans
Super User

If you mean the data in the Power BI Data Model has a different sort order, you are correct. When the data loads, Power BI will resort the table a few times to find the best compression algorithm.

There is rarely a need to sort in Power Query for Power BI. In Excel you may want the results in a specific order when loaded to a table. In Power BI the order of the data in the model is irrelevant. DAX doesn't care, and there is no way in dax to move up and down a row using row position, only values in filters.

You get the desired sort in Power BI via the visual settings.

v-cazheng-msft
Community Support
Status changed to: Investigating

Hi @rusgesig 

 

I’ve tested it in recent several versions of Power BI Desktop including the latest version(January 2022). As you can see, If I sort a table by Date in Power Query, and the table in Data View will be sorted in the way that specified in Power Query.

vcazhengmsft_0-1643007649989.png

 

vcazhengmsft_1-1643007649990.png

 

But for visual such as Table visual, they will not be impacted by the setting when you drag a column into a visual. Power BI provides separate options to sort the values in visual.

vcazhengmsft_2-1643007649991.png

vcazhengmsft_3-1643007649994.png

If I misunderstand your problems, please feel free to let me know. Thanks a lot!

 

Best Regards,

Community Support Team _ Caiyun

rusgesig
Helper II

@edhans I have about 2m rows that I want sorted by date so that I can use lastnonblank to get the latest status. Are you telling me that query editor is not suited for this purpose?

@v-cazheng-msft 
I had logged a support ticket before - the technician suggested adding an index for sorting but it did not work either.
Step in Query Editor:

rusgesig_0-1643008222622.png

This is the result I get

rusgesig_0-1643008375114.png

I am still on December 2021  version from the MS app store - I can't see a way to force an update.


edhans
Super User

Yes @rusgesig that is what I am saying. LASTNONBLANK will automatically sort whatever your column is. See Alternative use of FIRSTNONBLANK and LASTNONBLANK - SQLBI for more information.

 

The data in the data model is never sorted like you want or would think. Even date tables will get resorted not based on date but whatever sorting algorithm allows for the highest level of compression.

 

Consider posting your issue in the DAX forum so we can help, and show you pre-sorting the data in Power Query is neither necessary nor effective.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

rusgesig
Helper II

@edhans lol I thought I was finally getting the hang of this BI thing.

v-cazheng-msft
Community Support
Status changed to: Delivered

User edhans explained it clearly.