Power BI template with built in Date dimension

If you are serious about building Power BI solutions, you definitely need a Date dimension in your data model. Reza Rad had written a fantastic blog about why need this, you can find it here. Isn’t it good that you can have a template file that already has a built in Date table and you can use this as a starting point to build your data model. As you can see below, this table has all the dates from a starting date (defined by you) and up until yesterday. Plus it is dynamic so it will update as time goes by when you refresh your data model. I have built a Power BI template file so when you open it or import it you will have a built in Date table. You will find the download link at the end of this post.

Date.JPG

If you open the query editor and go to Advanced Editor, you will see the underlining M code as below

//Create Date Dimension
(StartDate as date)=>
let
//Capture the date range from the parameters
StartDate = #date(Date.Year(StartDate), Date.Month(StartDate),
Date.Day(StartDate)),
EndDate = Date.From(DateTime.LocalNow()),
//Get the number of dates that will be required for the table
GetDateCount = Duration.Days(EndDate – StartDate),
//Take the count of dates and turn it into a list of dates
GetDateList = List.Dates(StartDate, GetDateCount,
#duration(1,0,0,0)),
//Convert the list into a table
DateListToTable = Table.FromList(GetDateList,
Splitter.SplitByNothing(), {“Date”}, null, ExtraValues.Error),
//Create various date attributes from the date column
//Add Year Column
YearNumber = Table.AddColumn(DateListToTable, “Year”,
each Date.Year([Date])),
//Add Quarter Column
QuarterNumber = Table.AddColumn(YearNumber , “Quarter”,
each “Q” & Number.ToText(Date.QuarterOfYear([Date]))),
//Add Week Number Column
WeekNumber= Table.AddColumn(QuarterNumber , “Week Number”,
each Date.WeekOfYear([Date])),
//Add Month Number Column
MonthNumber = Table.AddColumn(WeekNumber, “Month Number”,
each Date.Month([Date])),
//Add Month Name Column
MonthName = Table.AddColumn(MonthNumber , “Month”,
each Date.ToText([Date],”MMMM”)),
//Add Day of Week Column
DayOfWeek = Table.AddColumn(MonthName , “Day of Week”,
each Date.ToText([Date],”dddd”))
in
DayOfWeek

The above M code creates a function and by invoking it after you specify a start date, you will get a nice date table for your data model. If you need additional columns such as fiscal year you can either amend the M code to add it or you can do so after loading this table into your model first. Entirely up to you.

Date2.JPG

You can download the Power BI template file here

 

 

Power BI May Updates

Power BI has released may updates

Here’s the complete list of May updates:

Report view

Analytics

Data connectivity

Query editing

You can also watch this video for demo of these great new features.

On top of this, there are 2 other things I think you should know if you and your organization has already started or are planning to use Power BI.

1 Power BI free license will no longer enable you to share reports, you have to have either pro license or Power BI premium (see point 2).

2 MS is introducing Power BI Premium, a new way of sharing your report either on premise (Power BI report server) or cloud. read here for more info.

a whitepaper is also available for you to know more about this brand new offering.

Power BI Premium whitepaper

There is also a post from Matt for detailed reasoning why MS is doing this

Matt’s post

 

 

Excel UNICHAR function is also in Power BI

Back in the old days I remember I had used Excel REPT function to hold a bar in a cell like the graph below (I can’t recall why I did this), but I am sure there is some great use of this trick out there. You can see the formula in the formula bar. You also need to set the font to Britannic Bold for this to work.

excel rept and unichar

Good news is now you can use the same trick in Power BI as well . Chris Webb has recently blogged about this. Go check it out.

The DAX Unichar() Function And How To Use It In Measures For Data Visualisation

 

2 great new features in Power BI April update (quick measures and add column by example)

Microsoft released Power BI April update a few days ago. I am very excited about 2 new features -quick measures and add column by example. These 2 new features are great for Power BI beginners because now you can add measures and custom columns without needing to know DAX and Power Query M language. Power BI will automatically create them for you. It’s also a great way of learning DAX and M by looking at the code generated by Power BI.

You can create a quick measure by selecting Quick measures from the field menu, either in the field of a chart or from the field list.

quickmeasures

In the dialog, you can pick any of the 19 different calculations to perform on the selected measure. Depending on the calculation, you’ll have different required parameters you’ll need to fill in. You can also change the fields being used in the calculation using the field list on the right half of the dialog. Click below link for more detailed info on this feature.

https://powerbi.microsoft.com/en-us/blog/quick-measures-preview/preview/

The add column by example feature is really like Flash Fill introduced in Excel 2013. It works like by manually typing some sample values you want then Power BI will apply the same transformation rule to other rows of the same column. The more sample values you type the more accurate it will get. Watch below video for a complete run down of how it’s doing the magic.

P.S. Quick measure is a preview feature so you need to go to option menu to enable it

Click File menu in your ribbon and choose Options and Settings, click on Options to open Options dialog, then click Preview features to enable it

On prem hosting of Power BI reports

If you have been using Power BI and think about how you can host your report locally, good news is you will be able to do so very soon. Microsoft released technical preview for on premise hosting of power BI reports inside SSRS. Below link is the official guide from Microsoft on how you can actually do this. I won’t repeat here on the how’s, but I would like to call out a few things that might still confuse you.

https://blogs.msdn.microsoft.com/sqlrsteamblog/2017/01/18/getting-started-with-the-technical-preview-of-power-bi-reports-in-sql-server-reporting-services/

1 It’s still beta version and the only supported data source for your Power BI reports at the moment is SQL server analysis service and you have to use direct query. That’s right, live connection to your SSAS cube (either multi dimensional or tabular) instead of loading data from cube to your Power BI data model. Microsoft is planning to add more data source in the future.

2 You may wonder why live connection? Because to make SSRS integrate with Power Query seamlessly is not an easy thing and Microsoft needs time to deliver this. So at the moment, only live connection to your cube.

3 Microsoft is targeting mid 2017 to release the GA and the licensing details won’t be available until close to that time. so sit tight and it’s coming.

Now without further ado, go try out the preview feature on your local server or VM.

 

Microsoft Custom Visual – Bullet Chart Can’t do exactly what I want

I have recently came across a problem like this. I would like to visually present some data shown in below table in a graph like the one below my bonus table where I can easily see what my bonus% value will be base on my sales number. In below bullet chart I can easily see that I will get 2.50% as my sales 499K falls in the fourth tier.

Data tier
Bonus Table

bullet

I have had a look at all available Power BI custom visuals at that time and thought that the bullet chart from Microsoft might be just what I need, so I gave it a short. After nearly an hour of playing around and trying different formatting options, I can’t seem to get what I want exactly.

bullet chart

Microsoft Bullet Chart

The problems lies in the chart itself. With this chart I can only have 5 color for my bonus% tier and I need 7. Note the last one -black color is for the bullet. So you have 5 color not 6. Also you don’t have control over your X-axis label, nor do you have control over the size of it. You cannot even make the bar wider. Anyway it can’t do exactly what I just needed.

Capture2

Do I just give up and stop here? That’s a big NO for a data geek like me. So I tried below.I created a picture like below and you can do the same in any tools of your choice. I created mine in Excel. As you can see it already has the labels and color tier I need.

Capture

Insert this as a image onto you power bi canvas and then visualise your sales number in a normal bar chart. Finally move your bar chart on top of your inserted image, then you will see the bullet chart I showed your in the beginning of this post. If you don’t get what I showed you before, use below button to send your image to the back.

send to back

Finally, the normal bar chart let you fix your X-Axis label, that’s exactly what I need. Set end value at 2M, my bar chart will always be in proportion to the image underneath.

Capture3

There you go. Hope this post can give you some idea of how thinking out of box can sometimes saves your day. If you know other ways or maybe better ways to do what I wanted, please share with me as I would like to learn from you as well.

Last but not least, I hope Microsoft can enhance it’s bullet chart custom visual, so it can better serve our needs.

Alteryx hand in hand with Tableau

I have covered similar problem using Power BI before, you can find it here. Now I would like to show you how you can tackle this different granularity problem with Alteryx. Alteryx is an powerful ETL tool but it also does other amazing things such as spatial analysis and predictive analysis. I will cover these in future post. so what I am showing you today is how you can use Alteryx to prepare your data and spit out a tde file for tableau to visualise your data. (note that Alteryx has basic reporting capability as well but it’s horrible compared to that of Tableau). Some sample data as below: daily level transaction data and quarterly sales quota data.

1
Daily sale data
2
Quarterly sales quota data

If you load these data into tableau it’s not going to be able to consume it easily. You can ask your IT to break your quarterly sales quota data down to day level and then use tableau to visualise it. Since we are in the era of self service BI, let’s do it all by ourselves with Alteryx. I have massaged the above data set to be in below format so that both data set are now at quarterly level. Now I can easily join them and find out which sales person hits target for each quarter.

I designed below workflow in Alteryx to do the job. (I could have used SQL script to get desired data for my analysis, but I want to show you how you can do it with Alteryx)

3

Step 1: drag an input tool to get my sales data from my local instance of SQL server. You can see the incoming data and data type as below. Note that SalesAmount is string type.

Step 2: drag an auto field tool to change the data type. You can see SalesAmount is now Double type.

Step 3: drag a datatime tool to parse the OrderDate to remove the time component.

10Step 4: Drag a text to column tool to split the DateTime_Out field.

12.PNG

Step 5: drag a formula tool to get my Year-Quarter column.

1314

Step 6: Drag a select tool to remove unwanted column.

15.PNG

Step 7: Drag a summarise tool to aggregate my sales data to quarter level for each sales person. I have put step 1 to 7 in a container so it’s easier for other people to follow my logic flow.

Step 8 to 11: Manipulated the sales quota data set coming from SQL server to be in the same structure as my sales data. You can see the end result as below.18

Step 12: drag a join tool to combine the 2 data sets.19.PNG

Step 13: I have added 3 browse tools to check my output data set from step 12. This tool is often used to see your data set after transformation performed to ensure you get intended result. I don’t expect to see any data in the upper and bottom browse tool as all my employees should have sales quota defined in my data set. If I had employees without associated sales quota or vice versa, then I would have seen data coming out of those 2 browse tools.

Step 14: drag a output tool to generate a tableau tde file, so you can visualise your final output in Tableau. My mock-up data set for sales quota is too high as no sales person had reached it. Anyway, you get the idea how you can use Alteryx and Tableau together for your analysis.

20.PNG