Forum Discussion
Sort by multiple columns in Get Data on SqlServer table
I have a SqlServer table that I want to show as a Table object on my PowerBI report. When in Get Data, I'm sorting the data by
ColA DateTime
ColB varchar
ColC varchar
ColD int
The sort should be:
order by ColA ASC, ColB Desc, ColC Desc, ColD Desc
It sorts fine when I am in the Get Data modelling, but when I close and apply the Table object is not sorted in this fashion.
Here is my Get Data. See the Select Group is in Asc, the DDATE is in Desc as needed
In my report, notice that the Select Group is OK (null), but the dates are Ascending, not Descending).
Shouldnt it be the same between the two?
Please advise
Hi,bdherh .I am glad to help you.
The problem you're experiencing involves a different process of running Power BI.powerbi - Trying to Reorder Columns in Data View in Power BI - Stack Overflow
Sorting rules customized in power query are not applied to table visual in power bi desktop.
suggestion1
A better approach is to create an index column in power query that is sorted by a combination of fields (i.e., the index column is created after you have ensured that the data has been sorted properly). Then in power BI Desktop, select the tables to be sorted consistently by that index column. (Sorting the real table in the model, the visual presentation of the data using this table will be sorted according to this column by default)
The limitation of this is that for each table in the desktop semantic model, it must be sorted by only one column (and the sorted data must not be duplicated)Solved: Setting Default Sorting by Date/Time in All Charts... - Microsoft Fabric Community
I notice that you have successfully sorted the data, you can sort by the index column by inserting an index sorting sequence and then selecting the table in table view.
suggestion2On the report view for a single visual setting to sort by multiple columns, you need to manually configure the sorting rules, the problem is that this can be easily modified as it is not the default sorting rule.
Change how a chart is sorted in a report - Power BI | Microsoft Learn
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
I made a mistake on the column types. Should be
ColA varchar
ColB DateTime
ColC varchar
ColD int
Hi,bdherh .I am glad to help you.
The problem you're experiencing involves a different process of running Power BI.powerbi - Trying to Reorder Columns in Data View in Power BI - Stack Overflow
Sorting rules customized in power query are not applied to table visual in power bi desktop.
suggestion1
A better approach is to create an index column in power query that is sorted by a combination of fields (i.e., the index column is created after you have ensured that the data has been sorted properly). Then in power BI Desktop, select the tables to be sorted consistently by that index column. (Sorting the real table in the model, the visual presentation of the data using this table will be sorted according to this column by default)
The limitation of this is that for each table in the desktop semantic model, it must be sorted by only one column (and the sorted data must not be duplicated)Solved: Setting Default Sorting by Date/Time in All Charts... - Microsoft Fabric Community
I notice that you have successfully sorted the data, you can sort by the index column by inserting an index sorting sequence and then selecting the table in table view.
suggestion2On the report view for a single visual setting to sort by multiple columns, you need to manually configure the sorting rules, the problem is that this can be easily modified as it is not the default sorting rule.
Change how a chart is sorted in a report - Power BI | Microsoft Learn
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
