Data Analytics with Office 365: Part Two



In the previous post in this mini-series on Power BI, I introduced the product and briefly described how to input data for manipulation and presentation.

There’s much more to say on that topic, which I’ll dive into next time.

However, I neglected to mention a problem you might experience if you license accounts within your tenant for Power BI.


Let’s say you’ve assigned a license:




You’d expect that a user who selects the Power BI app tile from the Office 365 portal


Power BI tile


And who clicks on the Power BI for Office 365 option (“The NEW Power BI is here“)…

Power BI Landing Page


And who then sees the “Getting Started” page…

Power BI Landing Getting Started Page


Would be ready to proceed into the bold new world of data analytics.



But alas, what your user might see is this:




What’s going on?

The problem is that, by default, your tenant may be configured to prohibit users from signing up for the service on an ad-hoc basis.  For now, we’ll overlook the apparent workflow issue (after all, if you, as the tenant admin, are assigning the license it should follow that you want the assignee to use the service).

The solution is pretty simple and requires a remote PowerShell session.

To enable ad-hoc sign-ups and allow your users to proceed, issue the following Powershelgl cmdlet syntax:

Set-MsolCompanySettings -AllowAdHocSubscriptions $true

Note that to issue this specific cmdlet, you must load the Azure Active Directory Module for Windows PowerShell which gives a global admin the necessary configuration access to key elements of the tenant.

Next time, we’ll take a closer look at using Power BI to generate useful reports.


Data Analytics with Office 365

Things to come-space-scope

As the saying goes, ‘I’m not a doctor, but I play one on TV’.

Only in this case, we can replace ‘doctor’ with “data scientist” or “business intelligence analyst” and ‘TV’ with “this blog post”.

Because this time, we’ll be taking an introductory look at Power BI for Office 365, the data analytics and business intelligence component of Microsoft’s service platform.

Let’s start with a definition, borrowed from the Power BI product page:

[Power BI is] an online service where you can quickly create dashboards, share reports, and directly connect to (and incorporate) all the data that’s important to you. [It] also introduces the Power BI Desktop, a dedicated report authoring tool that enables you to transform data, create powerful reports and visualizations, and easily publish to Power BI. [This] extends to all your mobile devices, too.

Full at the Power BI page.

For the un – or recently initiated, what this means is that using Power BI, you can import data from a variety of sources and manipulate that data to gain insights which may be hidden in plain sight within a forest of information.

Which is exciting, in a data geek sort of way.

As an example of what’s possible and how you can start to use this very potent tool, we’re going to walk through the process of importing and creating a report using Power BI from my test tenant.

When you log into your Office 365 tenant at you’ll notice the Power BI app tile:


By selecting the Power BI tile, you’ll be presented with the app login page:


Of course, you’ve noticed  “Already have an account?”  which, if you have an Office 365 Power BI license assigned from your tenant you do, although it may need to be provisioned (licensing example shown below from Office 365 portal admin view).

PowerBI licenses


By scrolling down the login page, you’ll see sign-up options:

Power BI Sign-up

For the sake of our overview, let’s assume you already have a licensed account and are ready to go, by clicking “sign-in“, you’ll be taken to a logon splash page:


And then into the Power BI dashboard.


Notice the “Get Data” button in the lower left-hand portion of the screen, when you click this, you’re presented with options for acquiring data:


Data sources include:

  • Content Packs created within your organization
  • External services that have created content packs for Power BI
  • Files (either stored on your computer or within your Office 365 tenant within SharePoint Online or OneDrive for Business)
  • Databases hosting ‘live’ data found within Azure SQL database, Azure data warehouse, SQL Server Analysis Services and Spark on Azure HDInsight

Here are samples of the individual interfaces:

Content Packs:


External Services (just a sample):






For the purposes of our example, we’ll be getting sample data from a file stored within OneDrive for Business:


Browsing to my OneDrive for Business site:

Earlier, I downloaded sample files from Microsoft’s Power BI support page and uploaded it into the “PowerBI Data Files” folder you see pictured above.

Next I selected the file and chose to connect to it:


I’m presented with the Excel import page:


One thing I neglected to mention is that data imports are linked to dashboards, as shown here:


In our example, I’ve imported the “Sales and Marketing Sample” to ‘Dashboard Two’.  Now let’s get back to what happens to this imported file. As you can see, the default view isn’t very interesting:


But by clicking on the file name displayed on the dashboard, a much richer visual is revealed:


Very nice!

But just a moment, before you pop a cork on that bottle of Clique Veuve to celebrate the end of your business intelligence problems (instant insights!) I should mention that this apparently painless creation of a beautiful dashboard is possible because the sample spreadsheet has been massaged using Power View for Excel.

More typically, after your spreadsheet has been properly formatted for Power BI you’ll interact with it more like this:

importing data –


Followed by –


In this more realistic example (which hasn’t had the benefit of being prepped by Power View for Excel) you must choose the fields that will be included and be used to form the elements of your interactive report.

That’s all the time I have for today.  Next time, we’ll take a closer look at how to manipulate your data using Power BI.