Forum Discussion
Table Column Sort/Reorder/Rearrange
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
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.
11 Replies
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.
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 ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
