BUG - Sort by Date in Query Editor

[rusgesig]

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

[edhans]

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]

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.

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.

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

Best Regards,

Community Support Team _ Caiyun

[rusgesig]

@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:

This is the result I get

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

[edhans]

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]

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

[v-cazheng-msft]

User edhans explained it clearly.