Table Column Sort/Reorder/Rearrange

[born_naked]

I am a complete newbie to Power BI so this might be a stupid question. 

The gist is this: I have date period columns using the Matrix viz that are out of order. Data is not aggregated in pivot as data was prepared originally for ingestion in another app. Is there a way to set the order of columns in a data viz?

Or barring that, if I were to ingest unprocessed data and pivot and clean it in PowerBI is there a way to then set the order of the columns?

Longer question/more context:

I had for another application/use case used python to create a table that was designed to be pivoted without aggregation--possible with Tableau. Since each pairing is unique, I can pivot with aggregation just using sum or avg and don't have an issue with that. But the columns are quite out of order: 

The columns come from a custom date formatted field. I also have a column with the start date of each period and a column with a numeric value which I had used to sort the columns in other applications. I do not see an obvious option to either manually rearrange the columns or to do so automatically in Power BI. 

Thanks in advance

[edhans]

You need to create another column that would be a 1:1 match. For example, 20200302 for the March 2 column. Then, you tell Power BI to sort your real column by the sort column.

[born_naked]

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. 

 

[edhans]

@born_naked - it doesn't matter what the visual is or how the data is used. As I indicated in my first post, the Sort by Column is what you need. You can sort that date field however you want, and it doesn't matter if you put it in a row, column, values, or what the visual is. It can be a bar chart, table, matrix, or pie chart. It will sort if you follow the directions in the article I linked to.

[v-lionel-msft]

Hi @born_naked ,

Please refer to the case.

Matrix Custom Sort 

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.

[born_naked]

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

[edhans]

1. 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.
2. Select the ellipses in the matrix visual, and sort by the column field you are using, like the image below.

[born_naked]

@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). 

[edhans · Accepted solution]

Hi @born_naked 

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.

[edhans]

@born_naked - did this help? if not, can you share your file with me via PM so I can assist?

[born_naked]

thanks for the help Ed. I'm sure you're answer is correct so I accepted it. Our versions of Power BI might be different as it appears you have a different ribbon. I followed the tutorials various ways of sorting the columns to no avail--the options shown are simply not present for me. I'm not going to continue working on this problem. 

[edhans]

Yeah @born_naked I have the "new ribbon" but the feature is years old, so just in a different place.  It looks like this in the original ribbon. 

I understand the frustration sometimes with a feature. If you never want to revisit it, just let me know. Sorry we didn't get this working for your specific scenario.