Tutorial On Creating A Frequency Distribution Chart With Microsoft Excel, R and Python

I got to join this amazing community of Data Scientists in Nigeria. We are a mix of experts and beginners. Today, I created a tutorial for the beginners to see how to do a common task like frequency distribution plot in both Python and R, also decided to include my dearest Microsoft Excel as a control.

The sample data is a fictionalized data for Dominos Pizza Nigeria. One day sales data for their Lekki branch. You can download the practice along raw data file here: https://dl.dropboxusercontent.com/u/28140414/Dominos%20Pizza.csv 

So the business question we want to tackle is: Is there a pattern in the quantities each customer buys? To be more specific, we want to examine the frequency distribution of the quantities purchased per sales transaction.

In Excel, it is extremely straightforward. Just plot a histogram on the quantity field.

Now let's head to doing same with R

I use R 3.3.2 and RStudio.

First, I import the csv file into RStudio.

Though not necessary for what we want to do, but I like doing it for any data I bring into R, I run the summary command on the dataframe/table. > summary(Dominos_Pizza)

Again, not a required step. I check out the standard plot graph on the Quantity field. > plot(Dominos_Pizza$Quantity)

Finally, I do the histogram chart on the Quantity field. > hist(Dominos_Pizza$Quantity)

For now I don't bother customizing the graph elements (labels, color, title, etc.)

It is Python time.

I use Rodeo IDE and Anaconda. 

I import Pandas and use it to read in the csv file. 

And here is the plot graph, like we did in R.

Finally, I create the histogram.

I will try to follow up with more tutorials of complex tasks, and some that are best suited to R and others that are best suited to Python. As per Excel, it is in a completely different class. It is a spreadsheet application. 

Got any particular task you will like me to create a tutorial around? Ask away!

Setting Up Power BI Embedded on Microsoft Dynamics NAV 2017

We got a request from a big company to show them how to integrate Power BI with Microsoft Dynamics NAV 2017. 

In other to prepare demo content for the training, I had to install Microsoft Dynamics NAV 2017 and set it up with Power BI. NAV 2017 can integrate with Power BI via four doors -- Power BI Embedded, Power BI service NAV content pack, Power BI Desktop OData and SQL connection to the NAV database. The Power BI Embedded one is more of you bringing in your Power BI dashboard (any one, not necessarily one built from NAV datasets) into NAV reports.

I made sure to take a lot of screenshots along the way. I know this won't be interesting to a lot of us, but for the few who some day will stumble on this when they are stuck getting to make all the frustratingly many moving parts work in sync, this can be a time/life saver.

First, what is Power BI Embedded?

It is when you make your Power BI reports show in another application. Like I did to make my stock analysis dashboard show up on my Nigerian Elite web app. So I embedded it there. And the new NAV 2017 also comes with that ability for you to embed your Power BI dashboards in its reports page.

So below are the visual steps to setting Power BI embedded up in NAV 2017.

In the beginning, there was no Power BI Reports, just a link to setting it up.

Then I followed the setup instructions.

At this stage, I had to go to Microsoft Azure on the account that is the O365 administrator and has Power BI pro enabled. Under Azure Active Directory, I created a new App Registration.

Copied the sign-on URL from the NAV reply URL (in the setup dialog box). Ignore the 8080 showing up in mine, it was the result of following a wrong instruction online, I had to verbatim copy the Reply URL value here before I could get it to work.

Got the App Id and Key.

Set the Permissions and add Power BI service (enabled all the delegate permissions for Power BI service).

Continued the setup on NAV 2017.

Now it was half time.  I was half done.

Then continued.

I got a new dialogbox asking me to authorize azure services to connect to Power BI services.

On clicking on the link, I had to login again.

All set! I can now embed any of my many Power BI reports.

:)

The Seven New Charts Exclusive To Excel 2016

In August 2016 we wrote about the new chart types in Excel 2016. 

Since then, the list has grown slightly. Now we have seven new chart types in Excel 2016. 

The latest entry is Filled Maps. It allows you do a map of your location/state/branch/region/city/country data with a graduated fill to show the values (of any metric) across those locations.

The other ones are:
 Treemap

Sunburst

Histogram

Box and Whisker

Waterfall

Funnel

For extra commentary on the charts other than the filled map, you should read: http://blog.urbizedge.com/2016/08/new-chart-types-in-excel-treemap.html

The Recorded Version of Our December Webinar On PowerPivot

On Tuesday we did have the webinar on PowerPivot. For one hour I showed how to combine data from difference sources and easier transform them into ones you can use to create meaningful single page dashboards.

I covered using the Query Editor in Get & Transform panel in Excel. We pulled in over 15 report tables from Excel and one database table from SQL, and combined them to create a dashboard that ties everything neatly together.

You should watch the webinar here: Turbo Charging Your Reports with PowerPivot

For the demo, I worked on real data of the companies listed on the Nigerian Stock Exchange. And we created a dashboard that looked like the one below.

Don't forget to watch the webinar recording here: Turbo Charging Your Reports with PowerPivot

Interested In Learning The New Office.js Excel Add-in?

For some months now I have been digging deep into the exciting new world of Excel (Office.js based) add-in. What does that mean? In straightforward terms, it means the new powerful and exciting way of creating Excel add-ins.

However it has not been easy finding learning resources. In fact, most of the early push and learning I got where based on insider NDA content shared in the Microsoft MVP community. But not everyone will get that silver spoon feeding privilege.

So how would you, without direct content from Microsoft, access resources that will get you started on learning and building your own Excel web Add-ins?

That is the question I have set to provide answer to in today's post.

1. I will recommend you start with Michael Zlatkovsky's Building Office Add-ins using Office.js site

2. I will recommend you watch Michael Zlatkovsky's End-to-End Walkthrough of Excel JavaScript Add-in Development video on Microsoft's channel 9

Also make sure you download the solution file so you can practice along.

3. Get familiar with the code snippets for doing the common tasks you'll want to carry out at office-js-snippet-explorer/excel-snippets and Office 2016 JavaScript API Snippet Explorer 

4. Build some sample add-ins from scratch with guidance. Try out Build your first Excel add-in and samples with the codes from Office Dev Center.

5. Read more helpful documentation (Excel JavaScript API programming overview and Excel JavaScript API reference) and maybe buy the Building Office Add-ins using Office.js book.

So those are my goto resources. But ultimately, how far you go will depend on how much practice and non-guided add-ins you build.

Best of luck to both of us on this new exciting journey!