In today’s tutorial, we’ll talk about one of the external tools in Power BI developed by Greg Deckler exclusively for our Enterprise DNA members – the Power Sort Pro. The Power Sort Pro is specifically designed to work with the Enterprise DNA Extended Date table and can also find Sort By columns via basic pattern matching. You can watch the full video of this tutorial at the bottom of this blog.
Let’s dive into how this tool is such a fantastic time-saver!
I’ve got a couple of tables and a bar chart below from the External Tools in Power BI for Practice Dataset that we’ve developed and put on the forum. This is just a simple star schema that uses the Enterprise DNA Extended Date table. What I’ve done is just I’ve pulled out a couple of tables here, DayOfWeekName and Total Sales. I’ve got one for Channel and Importance in Total Sales, and then Month & Year in Total Sales.
You can see that these are all sorted improperly. The DayOfWeekName sorts in alphabetical order. And then, we want to sort our Channel by importance, and we’ve got the Month & Year sorted alphabetically as well.
Typically, you’ll go into your Date table and look for Month & Year, and then you’ve got to figure out the proper sort of the column that you need to go through and then attach and sort by that numeric field. It turns out to be MonthnYear.
In the past, we’ve had a cheat sheet that we’ve developed that would show you for each of the date fields what the proper sort field is. You had to go through and manually sort each one, but at least it told you which was the proper field. But now, with Power Sort Pro, you don’t need to do any of that.
Let me show you how this works.
Introducing Power Sort Pro – External Tools In Power BI By Enterprise DNA
Let me show you how this works. If you go into External Tools in Power BI, and if you’ve installed your External Tools from the installer, you’ll get these five tools: Quick Measures, Power Sort Pro, Metadata Mechanic Pro, Conductor Pro, and DAX Editor Pro.
So let’s click on Power Sort Pro. There are three different ways to set your sort.
One is by Sort Text, which looks for a field that has the name “sort” in it so that it identifies as a sort column. If you use the Enterprise DNA Extended Date table, Greg programmed in all the sort fields, and each recognizes that Date table and automatically sorts by all the proper fields.
The other way to do it is to do it manually. You can select any one of your tables, or you can just select All. I typically just keep it on All. Next, click Find Sort By Column, set sort by column, and you are done.
And now, you can see that everything is sorted correctly.
Lastly, I’ll show you a couple of more things that you might find helpful over time.
If for some reason you wanted to clear out all of your sorts, you can just hit Clear, then Find Sort By Column, and then set Sort Columns. So basically, run the process in reverse, and it’ll clear out all sorts.
Another thing is the Channel Sort in the Channels table. Because of that Sort designation, it automatically recognized that as the Sort column. But let’s say we had this ChannelOrder column, and we wanted to make that the Sort column instead.
There are two ways we could do that with Power Sort Pro. One of which is we could initially set this to Order rather than Sort for the keyword. But let’s say in other tables we had Sort. We could go Find Sort By Columns, and let’s just go to our Channels table, and then Update Sort by Columns. From here, we can Unset from Channel Sort, and we can just manually select ChannelOrder, and then Set By Sort Columns.
So, we always have the ability to unset and set manually, which is still faster than picking your measure or your column going up to the column designation, finding the proper numeric field, and then hitting set and doing that manually.
***** Related Links *****
Level Up Your External Tools Menu In Power BI
Power BI External Tools – Installing Practice Dataset
Extended Date Table Power Query M Function
Conclusion
So that is the Power Sort Pro. It’s a fantastic time saver, and it’s a great addition to the External Tools in Power BI. I hope that you find this useful and apply this tool in your own work.
You can watch the full video of this tutorial below. Check out the links below for more related content and stay tuned for more Enterprise DNA External Tools.
Cheers!
Brian
