cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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: 

 

Capture.PNG

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User II
Super User II

Re: Table Column Sort/Reorder/Rearrange

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.

2020-05-21 13_02_48-Untitled - Power BI Desktop.png

I have another column in my table that I called Sort By.

2020-05-21 13_07_46-Untitled - Power BI Desktop.png

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.

2020-05-21 13_08_34-Untitled - Power BI Desktop.png

 

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

11 REPLIES 11
Highlighted
Super User II
Super User II

Re: Table Column Sort/Reorder/Rearrange

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Highlighted
Frequent Visitor

Re: Table Column Sort/Reorder/Rearrange

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. 

 

 Capture.PNG

Highlighted
Community Support
Community Support

Re: Table Column Sort/Reorder/Rearrange

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.

 

Highlighted
Super User II
Super User II

Re: Table Column Sort/Reorder/Rearrange

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



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Highlighted
Frequent Visitor

Re: Table Column Sort/Reorder/Rearrange

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_periodproduct_catreport_datesperiod_start_dateproduct_subcatvaluesortsort_cat  
24 Feb-01 MarCATA2020-02-24/2020-03-013/1/2020CATA: $0512  
24 Feb-01 MarCATC2020-02-24/2020-03-013/1/2020CATC: $01411  
24 Feb-01 MarCATB2020-02-24/2020-03-013/1/2020CATB: $01313  
02 Mar-08 MarCATA2020-03-02/2020-03-083/8/2020CATA: $0712  
02 Mar-08 MarCATB2020-03-02/2020-03-083/8/2020CATB: $0913  
02 Mar-08 MarCATC2020-03-02/2020-03-083/8/2020CATC: $01211  
09 Mar-15 MarCATA2020-03-09/2020-03-15########CATA: $0912  
09 Mar-15 MarCATC2020-03-09/2020-03-15########CATC: $02311  
09 Mar-15 MarCATB2020-03-09/2020-03-15########CATB: $01213  
16 Mar-22 MarCATA2020-03-16/2020-03-22########CATA: $0912  
16 Mar-22 MarCATC2020-03-16/2020-03-22########CATC: $01611  
16 Mar-22 MarCATB2020-03-16/2020-03-22########CATB: $01013  
23 Mar-29 MarCATC2020-03-23/2020-03-29########CATC: $01311  
23 Mar-29 MarCATA2020-03-23/2020-03-29########CATA: $0412  
Highlighted
Super User II
Super User II

Re: Table Column Sort/Reorder/Rearrange

  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.

 

2020-05-21 10_22_12-Issue - Power BI Desktop.png



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Highlighted
Frequent Visitor

Re: Table Column Sort/Reorder/Rearrange

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

Highlighted
Super User II
Super User II

Re: Table Column Sort/Reorder/Rearrange

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.

2020-05-21 13_02_48-Untitled - Power BI Desktop.png

I have another column in my table that I called Sort By.

2020-05-21 13_07_46-Untitled - Power BI Desktop.png

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.

2020-05-21 13_08_34-Untitled - Power BI Desktop.png

 

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

Highlighted
Super User II
Super User II

Re: Table Column Sort/Reorder/Rearrange

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



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors