Power BI for Office 365 Released

Today Microsoft has officially announced the release of Power BI for Office 365.  You can read the Power BI teams post on the release here:


Power BI has some pretty amazing features that allow users take control of their data.  I’ve also written several posts in the past about the Power BI features you can browse here:

Power Pivot

Power Query

Power View

Power Map

Power Q&A

Sign up for Power BI now here!

Final Day for Power BI Voting, Can I Have Your?

Today is the final day for the Power BI Contest voting and I could use your help to move on in the competition!  Please take a moment and vote for my video here  http://tinyurl.com/PowerBIDevin

Thanks for voting!

Power BI Contest Entry Voting

You’ve watched my video (hopefully) now vote for it!  In a previous post I wrote about my Power BI contest entry (Read more about it here).  I really enjoyed making this video and hopefully you enjoyed watching it.  Please take a few moments and vote for my entry here.



Power BI Contest Entry

Today I submitted my Power BI contest entry and I’d love for you to take a look!  Here’s a short description of entry and the video itself:

In this submission I have used Power BI to help me plan a vacation with my wife. We both love thrill rides so I wanted to make the best decision on planning our next trip. Using Power Query I imported data from the web to find all possible roller coasters and amusement parks. I then took that raw data and made relationships and calculations using Power Pivot. Next, I visualized the results through Power View to help guide me through the data easier. Finally, I uploaded my workbook to a Power BI site and enabled Q&A to allow my wife to ask the workbook questions about our trip.


Find out more about this contest here and put in your own contest entry.

Power BI Q&A Preview Now Live On Your Data

With the recent updates to the Power BI preview and Q&A features added I thought I would do a quick tutorial of how it all works.  This post will walk you through uploading a workbook to Power BI, Using Q&A against that workbook, enhancing the workbook with synonyms and the creating features questions.

First, sign up for a trial at www.PowerBI.com.  Once your trial Power BI site has been issued to you login and add your own Power Pivot workbook by selecting Add as shown below.


After you add your own workbook click the context menu and select Add to Q&A.


Next select Search with Power BI Q&A in the top right corner of the site.


This will present you with an empty textbox that is waiting for you to ask a question of your data.  Earlier when I added my own workbook I added a workbook that has presidential election data in it.  So I ask a question about the “Total popular votes by state”. 


With this small amount of information I provide Q&A realizes I’m asking for the Total Popular Vote metric from my Power Pivot model and that I want to see this information by state, which is also in my Power Pivot model. 


A couple other interesting things are presented here.  In the top right there are some hints on getting better search results for this workbook.  If you follow the help link there is some guidance on adding synonyms to your Power Pivot model:


I wrote briefly about synonyms being added to Power Pivot as well here.  Adding synonyms will allow my users to have more flexibility when interacting with Q&A.  Currently without synonyms in my model I must ask very specific question that reference column names exactly how they appear in my Power Pivot model. 

However, with synonyms added to my model I could add other variations of how users may ask my question of “Total popular votes by state”. 

Below I’ve added a synonym to my model on to Total Popular Vote metric.  Synonyms are entered with a comma separated list so I’ve added “votes” as a synonym for Total Popular Vote.


After publishing this workbook back to the Power BI site I can now ask the question more like an end user would.  This time I ask for “votes by state” and get the same results as expected.  This shows how you will start designing your Power Pivot models that you plan on publishing within Q&A.


The last nice new feature that is available in the Power BI Q&A preview is featured questions.  Featured questions are inquires that you would like to highlight on the Power BI site as common questions users ask.  These were in the preview before but you could not create your own. 

To create a featured question you simply click add featured question on the side pane or before you even ask a question click the plus icon (both shown below).

image image

Once you select one of these two options you enter the question and create an icon.  The icon can be small or large, a variety of colors, and with a symbol or even a background image.


Here’s an example of a featured question I created for this election data model.


I really love these new features and I think you will too so go try them out now at www.PowerBI.com.

Using Power Query to Parse your Inbox

One of the nice new features that was added to Power Query with the latest update in December is the ability to connect to Microsoft Exchange as a data source.  You can download this update now here:


Here’s a quick demonstration of how you may use the Microsoft Exchange data source in a Power BI solution:

After downloading and installing Power Query launch Excel and navigate to the Power Query tab.  If you don’t see the Power Query tab you may need to turn on the feature, which I describe here. From the Power Query Tab select From Other Sources and pick From Microsoft Exchange.


Next you must provide your Microsoft Exchange credentials.


It will then prompt you to authorize Power Query to access your account.


Once the authentication completes the Navigator pane will launch with a view of your Exchange account.


In the Navigator pane you can select you mailbox, calendar, contacts, or tasks.  You could hover over each of these options to get a preview of what the data looks like.  For this demonstration select Mail then pick Edit Query.  This will launch the Query Editor with an entry for each email in your Inbox.  Of course if you have thousands of emails this will show just a sampling of the email you have.


I’ve hidden the results in this screen shot to protect the innocent but it does indeed return back my mailbox results.  Here’s the kind of metadata it returns.

  • Inbox Path
  • Subject
  • Sender
  • To
  • CC
  • Time Sent
  • Time Received
  • Importance
  • Is Read?
  • Has Attachments?
  • Preview of the Body
  • The Full Email Body
  • Etc…

What I’d like to do is see who sends me the most emails. So I expand the sender column to return back the Name of the sender for each of these emails.  I only care about the Name column so I’ll filter out the address.


For this example I only care about counting the number of emails I get by sender so I’ll remove all the irrelevant columns and only keep the Sender.Name.  To remove all other columns select the one you want to keep then right-click on one of the columns and select Remove Other Columns (My screenshot show this on a different column). 


This will leave you with only one column but we need at least one other column to aggregate the number of Emails.  This can be done by creating an Index column.  Right-Click on the only remaining column and select Insert Index Column.  This will be used for returning a count of emails.  This could have also likely been done by doing a count of the ID column that was in the original dataset.  Click Apply & Close to take the results of this Power Query solution and build a report on it.  This may take a while depending on how many emails you have. 

I’ve decided to create a Power View Report to visualize who emails me most.  With the Power Query table selected in Excel go to the Insert menu and select Power View.  Once Power View launches it will automatically place the Sender and Index column into a table.  Change the Index column to Count (Not Blank).  This will produce an output that shows each sender and the number of emails from each.


Now, change this visualization to a Stacked Bar chart to easily visualize each sender and the number of emails they send.  After sorting the chart descending I see the final result shows that the Brian Knight, my brother and owner of the company, is the person that fills up my inbox the most.


Now this is a neat demonstration, but how can you make it work for a business solution?  Personally, I know severally organizations that could use this for parsing support inboxes to organize and see which users have taken up the most time (inbox time at least) from support.  So yes there is a business application and a pretty neat one at that!

Go download the latest Power Query update here:


Are you interested in learning more about Power BI?  Attend one of the classes I offer either in person or virtually

Virtually – http://pragmaticworks.com/LearningCenter/VirtualTraining/BusinessAnalytics.aspx

In Person Workshop – http://pragmaticworks.com/LearningCenter/Workshops/BusinessAnalytics.aspx

Thoughts on December 2013 Power Query Updates

Just in time for Christmas the Microsoft product teams responsible for Power BI have a slew of amazing updates.  The list of updates can be found here but for my notes specifically on Power Query updates continue reading!

Power Query

Power Query now has 3 new data sources that you can connect to.  Connectors have now been added for Sybase IQ, Exchange and Dynamics CRM Online.  Sybase IQ is the column-store database technology produced by SAP and is used for many organizations for BI solutions.  This is a great add for Microsoft to pull in competitors data sources. The addition of Exchange was a bit of a surprise to me at first but once I saw my first demo connecting to an Exchange email account I was pretty impressed what you could do with it.  Expect a blog post on this one soon.  Last, Dynamic CRM Online is likely to be very interesting to many organizations.  My company uses Dynamics and having the ability to easily pull data from it into a self service solution is a huge win.

Power Query can now automatically detects database relationships.  This functionality has existed in other Microsoft technologies including Power Pivot but now is available in Power Query too.

In the November release of Power Query the Query Editor ribbon was introduced.  Now the Query Editor ribbon has additional buttons for Fill Down, Sort Ascending/Descending and Number Transformations. The Fill Down function is a really cool M query function that I demoed in this whitepaper.

In addition to these, several new features have been added in regards to sharing organizational queries:

  • The Replace Shared query has been removed.
  • You can now Share your query from the Query fly-out
  • A new Certified button was added to the Search ribbon, enabling Search result to only show Certified results.

Get every new post delivered to your Inbox.

Join 29 other followers