Ordering attributes in Power BI

Earlier this summer I got to fish for Salmon on Lake Michigan for the first time.  A little while ago I got to do another first: deep ocean fishing off the coast of California.  My wife and I took a vacation out near Los Angeles since one of my college buddies was getting married.  We stayed an extra couple days after the wedding and spent one of them on a fishing charter out of Santa Barbara.

It was a cool experience, definitely not the type of fishing I’m used to.  The boat had about 30 people standing around the outside with fishing rods ready.  The captain stopped us over a pod of fish and everyone dropped their lines at the same time.  Since we fished between 125 and 300 foot depths, it took 2 to 3 minutes for the bait (which was sliced squid and chopped up anchovies) to reach the fish.  After catching a fish it took another 2 – 3 minutes to reel it up.  Since most people had monofilament and we had so much line out, it was pretty difficult to feel the bites.  If you weren’t sure, you basically wasted the 5 – 6 minutes dropping down and reeling back up.

My wife and I finished with 21 rockfish (the limit) and one ocean whitefish.  The whitefish tasted incredibly good.  It was a fun trip and definitely worth trying at least once.  I also want to commend the fishing crew that took us out.  They were patient and efficient, and did a great job making sure everyone had fun and caught fish.

rockfish

All the fish my wife and I caught. The ocean whitefish is in the upper right, partially obscured by a rockfish’s tail.

I’ve been delving heavily into Business Intelligence at work the last few months.  I had no previous Business Intelligence training or experience, and am now being asked to create a data warehouse and build various reports off it.  It is quite a daunting task, and I hope to share what I’ve learned in the next several blog posts.  I have chosen to follow strategy proposed by Ralph Kimball for creating the data warehouse, and I’ve found it is serving me well.  This blog post will however focus on a single problem and the solution that I found.

Power BI is Microsoft’s venture into the self service business intelligence world.  The two main players in this market are Qlikview and Tableau, but Power BI is a rapidly growing solution with a low price point, so we have decided to pursue that for now.  I attended a SQL Saturday pre-con dedicated to Power BI back in April and I came away very impressed with the tool.  However, there are still a lot of holes that need to be filled.  One such hole is the ability to sort columns.  In the method where you load all your data into the model, you can choose to sort on another column in the table.  This would work well, for example, when you want to sort by month number but show the month name on the visual’s axis.  However, there is currently no way to do this in the method where your data is stored locally or in Azure and the model queries that data each time it loads the data.  A simple way to get around this is to prepend the month number to the month name, showing a visual such as this:

20160921-sort-by-month-with-number

This looks ok, though not great.  However, I came upon another version of this problem that wouldn’t look quite so good.  I need to display a bunch of apparel sizes.  These sizes would have names like Small, Medium, and Large, but would also include shoe sizes, which are simply numbers like 10, 10.5, 11, etc.

Having numbers to order the size name would result in name such as 14 10, 15 10.5, and 16 11, which would be all kinds of confusing.  After thinking about it for a little while, I came up with a solution that I felt was pretty clever.  I decided to put a number of zero length strings in front of each name. The more zero length-ed strings there are, that later the name would show in the order.

In order to do this, I had to convert the name column from varchar to nvarchar, since the zero length string is a unicode character.  Then I adjusted the names by using a size order table which had the sort order stored as an integer column:

20160921-size-sort-table

I used the ROW_NUMBER window function and the REPLICATE function to put the correct number of blanks in front of each size name and code.

--Add blank spaces to the sizes for sorting purposes
WITH    cte
          AS ( SELECT   Size ,
                        ROW_NUMBER() OVER ( ORDER BY [Order] ) rn
               FROM     dbo.SizeOrder
             )
    UPDATE  dprod
    SET     dprod.SizeCode = REPLICATE(NCHAR(8203), cte.rn) + cte.Size ,
            dprod.SizeName = REPLICATE(NCHAR(8203), cte.rn) + sizedesc.[SEW Desc]
    FROM    cte
            INNER JOIN dbo.dimProduct dprod ON cte.Size = dprod.SizeCode
            INNER JOIN dbo.[SKU Element 2] sizedesc ON cte.Size = sizedesc.[SEW Code];

For the final result, here is my visual with sizes that are sorted smallest to largest rather than alphabetically, as would normally be the case:

20160921 Price total by size.PNG

I hope this helps if you ever need to sort on a text column but not alphabetically.