Forum Discussion
Table Column Sort/Reorder/Rearrange
Hi Anonymous
See below and see if I am misunderstanding.
You have data that looks like this. It is some form of a date, and this is uselessly sorted alphabetically, because it is text, and Power BI only sorts text alphabetically.
I have another column in my table that I called Sort By.
You'll notice that it is at the same granularity of what I want to sort.
I am going to select my Month name text column, select Sort by, select my Sort By column, and now the columns in my matrix are sorted the way I want.
This is what was in the article I posted about at the top of this thread.
If I am still misunderstanding, please be very explicit in how it should look. Mock it up in Excel as to how it should look and post a screen cap.
Thanks for responding. And I apologize, it looks like I should have stated I am using a Matrix, not a Table.
I do have a date column that would hypothetically do the trick. But I think you're solution works if I were trying to sort down the column, whereas if you look at the image in the original question, what I need is to sort across the columns.
If I drag my date field to the columns box, and place it above the report_period, which is a text field, the columns are sorted in datasource order (ascending date L-R). However, I cannot then see the report_period field in the column header, which is what I want to see. If, as pictured, the date field is below the report_period field, then only the report_period field shows and the columns are all out of order.
Hi Anonymous ,
Please refer to the case.
The key to solving this problem is to use the "Sort by column" feature to sort the fields in the matrix column label.
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for the feedback.
edhans Sadly, I am either too ignorant to see the option, or it is not available. My drop down menu only provides me with an option to sort by either the field dragged into Rows or the field dragged into Values. I am unable to find any sort option for the Columns. It is perhaps worth noting that in my query of the data, I have sorted it according to date. But this sort is lost when in the matrix. See below for relevant snippet.
let Source = Csv.Document(File.Contents("C:=....data_out\sample.csv"),[Delimiter=",", Columns=9, Encoding=1252, QuoteStyle=QuoteStyle.None]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}}), #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]), #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", Int64.Type}, {"report_period", type text}, {"product_cat", type text}, {"report_dates", type text}, {"period_start_date", type date}, {"product_subcat", type text}, {"value", Int64.Type}, {"sort", Int64.Type}, {"sort_cat", Int64.Type}}), #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"period_start_date", Order.Ascending}}) in #"Sorted Rows"v-lionel-msft Unfortunately, that option is greyed out nor does my field appear in the drop down. I believe this is because the date is not unique since multiple categories occur on any given date.
Below is some sample data. In my case, I have used the matrix visualisation. Then put "report_subcat" into rows, "report_period" into columns, and "value" into values.
report_period product_cat report_dates period_start_date product_subcat value sort sort_cat 24 Feb-01 Mar CATA 2020-02-24/2020-03-01 3/1/2020 CATA: $0 5 1 2 24 Feb-01 Mar CATC 2020-02-24/2020-03-01 3/1/2020 CATC: $0 14 1 1 24 Feb-01 Mar CATB 2020-02-24/2020-03-01 3/1/2020 CATB: $0 13 1 3 02 Mar-08 Mar CATA 2020-03-02/2020-03-08 3/8/2020 CATA: $0 7 1 2 02 Mar-08 Mar CATB 2020-03-02/2020-03-08 3/8/2020 CATB: $0 9 1 3 02 Mar-08 Mar CATC 2020-03-02/2020-03-08 3/8/2020 CATC: $0 12 1 1 09 Mar-15 Mar CATA 2020-03-09/2020-03-15 ######## CATA: $0 9 1 2 09 Mar-15 Mar CATC 2020-03-09/2020-03-15 ######## CATC: $0 23 1 1 09 Mar-15 Mar CATB 2020-03-09/2020-03-15 ######## CATB: $0 12 1 3 16 Mar-22 Mar CATA 2020-03-16/2020-03-22 ######## CATA: $0 9 1 2 16 Mar-22 Mar CATC 2020-03-16/2020-03-22 ######## CATC: $0 16 1 1 16 Mar-22 Mar CATB 2020-03-16/2020-03-22 ######## CATB: $0 10 1 3 23 Mar-29 Mar CATC 2020-03-23/2020-03-29 ######## CATC: $0 13 1 1 23 Mar-29 Mar CATA 2020-03-23/2020-03-29 ######## CATA: $0 4 1 2 - Select the field you want sorted (your field you are using as column headers) and select the Sort By Column, and pick the column it should be sorted by. Follow the directions for this article.
- Select the ellipses in the matrix visual, and sort by the column field you are using, like the image below.
edhans I appreciate the time you've spent reviewing this issue from for me. However, I do not want to sort by the column header, rather I want to sort the column headers. The column header is a text field representation of a date interval. Sorting it sorts it alphabetically. It needs to be sorted chronologically or in the default data set order sort (which is set to sort by a date column).