Sorry, but you can accomplish the same with the GUI by sorting on first column, then sort again on the second column. It combines the two sorts into one step. :). Just tried on a test sheet.
Also, if you look closely you'll see a little number appear by the sort arrow which indicates the order in which the column was sorted: "1" for first, "2" for second, etc.
There is a problem with this approach if your goal with the sorting is to eliminate a duplicate register. Once I wanted to keep the top row after a few sorting and eliminate the duplicates and power query couldn't make it. You could see it correct on the editor but not on the resulting table. I read that you have to index the database before the sorting to make the trick but it didn't work for me. Maybe Ruth you have a different approach to this issue? Thanks for all your videos Ruth. You have helped me a lot and I wish you the best. Keep this amazing work you do for us!
But the problem is my sorting is on 2 columns which is in two different tables. Date is in Calendar Table & Amount is in Sales Table. If they both would have been in single table this could have been achieved.
Ruth, It is not necessary to edit the M code. If you sort by the first column and then during the same step you sort by an another column, the same M code is created showing the two columns on the same step such as: = Table.Sort(#"Changed Type",{{"Company State", Order.Ascending}, {"Company City", Order.Ascending}})
Hi Curbal, thanks for this video very helpful! To add to this, e.g. for the folks that also want to have the table sorted in the 'Table Visual': 1) First sort the table in the query editor as explained in the video 2) Hereafter go to 'Add column' and add an 'Index Column' 3) Click apply 4) Create your visual (e.g. Table) add the Index Column and sort on Index. In addition, I learned that the newest version of Power BI now has a built in feature to sort on multiple columns (shift+click), I will update and check it out soon.
Hi Curbal , I think when we are sorting multiple columns we need to add Table.Buffer(Table sorting M Code) in the M code otherwise it won't do sorting properly. Do a video on Lazy Evaluation concept in power Query
What is the result of the sort? It seems to only be useful in the Power Query editor. There does not seem to be any effect in Power BI Data view or when you add a field to a table.
Hi Ruth, I’m made the sort change in power query, clicked close/apply, but when I create a new Table Visualization, it still only sorts one column at a time. Am I missing a step?
I have the same problem. if I click close and apply, in the visual I am seeing only one column is sorted in descending and no change in another column. I appreciate your help.
Rather than writing or copying and pasting and then changing the column name, why don't you just sort the columns one by one to save time? Is there any particular reason not to do this? Curious to know about it. TIA.
Hi Ruth, I have sorted my data as per your video.but it doesn't appear on the table view.it always shows ascending order by date.. there is no any sort other than power query sort. please help me to sort this out.
I have a unique requirement. The requirement is when no filter is selected the text graph should be sorted by Date column. Or when Country filter is selected the text graph should be filtered by sales. Could you please help me how to achieve it.
Actually Ruth, I'd be pleased if you could go through auto-indexing. I'm used to Access where if I append a query, auto indexing will give new rows a an ID. But how does this work in Power BI? I don't want to give old data new indexes, I just want new data to have an index-ID. So what do I need to do? Hopefully, I'm not the only one who doesn't know what best practice is!!
@@CurbalEN Well I just feel I should have one, shouldn't I? Although, for cities, for example, I have a unique ID and that would be appended, but I thought that all my tables should have them.. maybe not?!
If your table contains a column with unique values, then you don’t need an index, it will only hurt your performance. Should definitely make a video on this! /Ruth
Sorry, but you can accomplish the same with the GUI by sorting on first column, then sort again on the second column. It combines the two sorts into one step. :). Just tried on a test sheet.
Great, thanks for sharing! Will pin the post for more visibility:)
/Ruth
Also, if you look closely you'll see a little number appear by the sort arrow which indicates the order in which the column was sorted: "1" for first, "2" for second, etc.
There is a problem with this approach if your goal with the sorting is to eliminate a duplicate register. Once I wanted to keep the top row after a few sorting and eliminate the duplicates and power query couldn't make it. You could see it correct on the editor but not on the resulting table. I read that you have to index the database before the sorting to make the trick but it didn't work for me. Maybe Ruth you have a different approach to this issue? Thanks for all your videos Ruth. You have helped me a lot and I wish you the best. Keep this amazing work you do for us!
But the problem is my sorting is on 2 columns which is in two different tables. Date is in Calendar Table & Amount is in Sales Table. If they both would have been in single table this could have been achieved.
what do you mean with GUI?
Ruth, It is not necessary to edit the M code. If you sort by the first column and then during the same step you sort by an another column, the same M code is created showing the two columns on the same step such as:
= Table.Sort(#"Changed Type",{{"Company State", Order.Ascending}, {"Company City", Order.Ascending}})
Thanks, much easier!
/Ruth
oh I see
Hi Curbal, thanks for this video very helpful!
To add to this, e.g. for the folks that also want to have the table sorted in the 'Table Visual':
1) First sort the table in the query editor as explained in the video
2) Hereafter go to 'Add column' and add an 'Index Column'
3) Click apply
4) Create your visual (e.g. Table) add the Index Column and sort on Index.
In addition, I learned that the newest version of Power BI now has a built in feature to sort on multiple columns (shift+click), I will update and check it out soon.
I speak of Brazil, I looked for this help a lot I am very grateful, God bless!
Excellent explanation 💯👍
Thanks a lot, a bog hug from colombia 🇨🇴🇨🇴🇨🇴🇨🇴
it did work for me, even I deleted columns after and it kep the sort , Thank you!!!
🥳🥳
You are the G.O.A.T! Thank you so much!
Easy, quick and superuseful. My favorites! Thank you.
Thanks Curbal, I saw that the version Mar 2020 can do Shift+left click but I am using Sep 2019 in my work laptop so your video helped!
Great!!
Thanks! That is what I was looking for!
🎊🎊
Muy bueno Ruth! Muchas Gracias!, te saludo desde Houston
-Jose
Hola José, muchos saludos !!
/Ruth
Awesome mam keep posting M language tricks 😀
Miss posting those, should get back at it :)
Hi Ruth, we can select multiple columns with holding "Ctrl" button to sort multiple column.
That doesn’t work for me. Does it for you?
/Ruth
@@CurbalENIn my version I need to hold both Shift and Ctrl, then it works. In previous version I only needed Ctrl.
That is weird, but thanks for letting us know!
@@steinarbreivik1895 Doesnt work. Holding shift and ctrl will select the columns and the columns in between the two columns.
Hi Curbal, How to remove the sort option? I want the table to be displayed as same as how it is in source file. Please answer..
Gracias era lo que necesitaba.
Perfecto!
this was useful, thanks
Hi Curbal ,
I think when we are sorting multiple columns we need to add Table.Buffer(Table sorting M Code) in the M code otherwise it won't do sorting properly. Do a video on Lazy Evaluation concept in power Query
You can also use List.Transform, turning list of columns
Thanks!
/Rutj
Thanks for sharing this tip Ruth.
My pleasure 😇
/Ruth
what a fantastic tip! thank you so much Ruth !
Thanks Giovani! Make sure you check the pinned comment to see an easier way :)
/Ruth
I need to create separate sorting custome column by country like US =1, England =2, like that in power query editor. Give me the links
Super .. thanks
great video, tks :)
What is the result of the sort? It seems to only be useful in the Power Query editor. There does not seem to be any effect in Power BI Data view or when you add a field to a table.
Hi Ruth, I’m made the sort change in power query, clicked close/apply, but when I create a new Table Visualization, it still only sorts one column at a time. Am I missing a step?
I have the same problem. if I click close and apply, in the visual I am seeing only one column is sorted in descending and no change in another column. I appreciate your help.
Same here! When I close & apply the table in Power Pivot doesn't sort according to Power Query
Rather than writing or copying and pasting and then changing the column name, why don't you just sort the columns one by one to save time? Is there any particular reason not to do this?
Curious to know about it. TIA.
Thanks!!
Another useful tip! Thanks so much!
Check the pinned comment for an easier way :)
/Ruth
Hi Ruth,
I have sorted my data as per your video.but it doesn't appear on the table view.it always shows ascending order by date.. there is no any sort other than power query sort.
please help me to sort this out.
Nice Trick! i’ll record in portuguese in my channel for brasilians learning it! i’ll give your credits!
Thanks 👊, but before you do that, check the pinned comment, there is a easier way to do this :)
/Ruth
great trick Ruth!
Hi Pedro,
Make sure you check out the pinned comment for an easier way :)
/Ruth
Useful tips as usual, thank you.
Naji K.
Hi Naji!
Make sure you check out the pinned comment for an easier way :)
/Ruth
I have a unique requirement. The requirement is when no filter is selected the text graph should be sorted by Date column. Or when Country filter is selected the text graph should be filtered by sales. Could you please help me how to achieve it.
Actually Ruth, I'd be pleased if you could go through auto-indexing. I'm used to Access where if I append a query, auto indexing will give new rows a an ID. But how does this work in Power BI? I don't want to give old data new indexes, I just want new data to have an index-ID. So what do I need to do? Hopefully, I'm not the only one who doesn't know what best practice is!!
Hi Erica,
What are you using the index columns for ?
/Ruth
@@CurbalEN Well I just feel I should have one, shouldn't I? Although, for cities, for example, I have a unique ID and that would be appended, but I thought that all my tables should have them.. maybe not?!
If your table contains a column with unique values, then you don’t need an index, it will only hurt your performance. Should definitely make a video on this!
/Ruth
@@CurbalEN Yep.. I know that, but what about the tables that don't have unique values?
Then you need an Id ;)
/Ruth