cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
v4anand18
Regular Visitor

Sorting a table using multiple columns

Hi All,

 

 

Is it possible to sort a table using multiple columns?

 

 Revenue tableRevenue table

 

My table looks like this and i would like to sort by "Year" first and then by "Month". Is it possible?

1 ACCEPTED SOLUTION
Thejeswar
Solution Sage
Solution Sage

@v4anand18,

Yes it is possible, but not from the visual side

 

You will have to do all the sorting based on multiple columns in the Power Query and then build you visual using the table.

 

If you have already built a visual, changing the sorting now might not show the proper sorting. In such case you will have to re-create the visual from scratch

 

Here's how the M code will look once you sort in Power Query

 

= Table.Sort(#"Changed Type",{{"Team", Order.Ascending}, {"Plan", Order.Descending}})

You can see from the below image that inspite of not applying sorting in the visual, due to the sorting at power Query, my data looks sorted as expected

 

i.e. In the Visual Team is sorted in Ascending Order and then Plan is getting sorted in descending order with in each Team.

 

sort.PNG

 

 

 

View solution in original post

19 REPLIES 19
Doyin
New Member

Hi all! Is it possible to sort a table by multiple columns using "contain" in Power Query?

Hi @Doyin , use expression like this

= Table.Sort(#"Previous Step",{{"Column1", Order.Ascending}, {"Column2", Order.Ascending}})

 

roselilly23
Advocate III
Advocate III

As of March 2020 it is now possible from the visual side by using shift+click on the column headers. For more info: https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-march-2020-feature-summary/#_Multi-column_...

@roselilly23 , Finally I get your post in the thread. That is great!
@v4anand18 , I think you should mark as solution the @roselilly23 's post

Well noted @roselilly23 , and OMG thank god - finally!!!!

micwebnet
Microsoft
Microsoft

Provided accepted solution is incorrect. It only sorts PQ table and doesn't affect the visual. You'll need to add an Index column after the multi-column PQ sort and then sort your table on that column in PBI Desktop. Here's a video on how to do it: https://www.youtube.com/watch?v=RHhITmZjuDA.

 

Best,  Michael

cmihale
Advocate II
Advocate II

Coming from a Tableau environment, I don't understand why this functionality doesnt exist in this tool. 

 

Sorting by mulitple columns is very basic stuff in Excel. 

 

Sorting within Power Query is not a good solution; a different VIZ, using the same data, might require a different sort.  

 

 

Arklur
Resolver II
Resolver II

The "Accepted Solution" is definitely wrong. With that sample data, even if you don't specify an order in Power Query, the result is the same:

0FGIOOl

And no matter what sort order you specify in it, it will stay the same. In a table visual data will be sorted from left to right. In this case Team > Country > Plan > Actual. If you sort by a specific column, then - I think - the sort order will be "Selected Column" then from left to right. If you want to sort a table by multiple columns, the only option you have is to have a concatenated column with the necessary columns and specify the sort order on that column, which is obviously not a nice workaround, but might work in some cases.

 

Likely you can verify this using DAX Studio and check the executed query behind the curtains.

nick_dalfonso
New Member

Hello,

 

I found this thread and am doing somethign similar. I am using this method with the M code, and it works perfectly. I am tryign to now get the table to display as the data in the query does, but cannot figure out why it is sorting columns alphabeticlly and not following the source sorting?

 

New to Power Bi, so would need simple or easy to follow help.

 

Thank you, 

v-jiascu-msft
Microsoft
Microsoft

Hi @v4anand18,

 


Can you mark the proper answer as a solution please?

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi 

 

 

 

 

Hi,

I am trying to apply this step in my query editor as a step, but it throws an error. Could you please suggest how to resolve it.

Expression.Error: The name 'Changed Type' wasn't recognized.  Make sure it's spelled correctly.

 

Thank you,

Roopali

Hi @roopsnegi,

There is a break in your steps present in the Query Editor.

 

Check if all the steps are properly linked to each other. In simple words, there is a problem (mostly name related) with the steps before, in or after the Changed Type Step. 

 

If you post the M-Query of your query, it will be easy to solve this error....

The m code you’ve mentioned solve the problem to sort on the matrix visual? Would you post an example with repeated names? Cheers!
Thejeswar
Solution Sage
Solution Sage

@v4anand18,

Yes it is possible, but not from the visual side

 

You will have to do all the sorting based on multiple columns in the Power Query and then build you visual using the table.

 

If you have already built a visual, changing the sorting now might not show the proper sorting. In such case you will have to re-create the visual from scratch

 

Here's how the M code will look once you sort in Power Query

 

= Table.Sort(#"Changed Type",{{"Team", Order.Ascending}, {"Plan", Order.Descending}})

You can see from the below image that inspite of not applying sorting in the visual, due to the sorting at power Query, my data looks sorted as expected

 

i.e. In the Visual Team is sorted in Ascending Order and then Plan is getting sorted in descending order with in each Team.

 

sort.PNG

 

 

 

View solution in original post

Hello,

I successfully edited M code in the query to sort how I would like, but after closing and applying the sort is not how I intended. I tried sorting the column by itself (default), but no success.

 

Suggestions?

 

Thank you,

Hi there,

 

This was helpful; however, when I exit out of power query and refresh the original data does not reflect the sorted values. Any thoughts on how to change that?

saf
Regular Visitor

Same problem... sorting ok in power query, but not in visual side...

You might need to sort by column but not into power query

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors