Yesterday I was trying to setup a new Power BI dashboard for our SQL Server Analysis Services logging data.
We are running traces on most of our servers, in order to keep up with what is going on – this mainly because all other access to the boxes is restricted.
Now, having the data in a SQL Server database, the connection to Power BI Desktop is of course supported out of the box, no issues here what-so-ever, one should think. I however, came across creating a particular view on top of a table, the just didn’t compute in the Power BI Desktop Designer. As depicted below, the view in question would just not be possible to load/edit. Microsoft is looking into this.
I managed to get around the view issue by persiting the data in another table, consolidating and filtering to only have relevant data there.
Loading the data, from the table into Power BI Desktop Designer is easy, you just point to the server, pick the table and you are presented with two options, either to load or to edit. In this case I opted to load the data. I started playing around with the data and quickly discovered that I was missing out on some attributes in the data. For one thing I needed to convert my date to day, month and year (would have loved some basic week functionality as well, but maybe later eh?).
This functionality is actually build into the tool (Power BI Desktop Designer), so you can generate those extra columns by selecting “Add column” in the ribbon, select the desired date (if you have more) and choose from one of the many options in the “date convert picker”
– apologies for the screenshot in Danish.
The above selection will yield the Column you see in same screenshot, named [Month No], and as you can see, it returns a number. This is not very end user friendly, so a little convertion is in order.
The normal Excel IF(test, success, fail) does not work in the Power BI desktop Designer, we need to write it a little bit differently – this is how I convert month number into text:
if Text.Range([Month No],0,1) = "1" then "Jan" else if Text.Range([Month No],0,1) = "2" then "Feb" else if Text.Range([Month No],0,1) = "3" then "Mar" else if Text.Range([Month No],0,1) = "4" then "Apr" else if Text.Range([Month No],0,1) = "5" then "May" else if Text.Range([Month No],0,1) = "6" then "Jun" else if Text.Range([Month No],0,1) = "7" then "Jul" else if Text.Range([Month No],0,1) = "8" then "Aug" else if Text.Range([Month No],0,1) = "9" then "Sep" else if Text.Range([Month No],0,2) = "10" then "Oct" else if Text.Range([Month No],0,2) = "11" then "Nov" else if Text.Range([Month No],0,2) = "12" then "Dec" else "Unkn"
as a custom column:
Please note the little green check mark indicating everything’s Dandy (b|l|t)… No not that Dandy….
But to my great surprise, the column does not show up in the data source view (or whatever they call it in Power BI Desktop Designer) – Look for [Month Name] as I renamed the column from [Month] in a later step.
WTH is my column?
