Tag Archives: microsoft excel

Summary: Microsoft’s Power BI Team Does an AMA

One June 10, the Power BI team at Microsoft conducted an Ask Me Anything (AMA) on Reddit, and there were some standard non-responses, but there were also some interesting insights into what the team is thinking about the product and the direction it is planned to be taking.

Here is a quick summary of the items discussed:

Power BI in Office 365 Home

As you may know, Power BI is only supported with Office 365, and only with certain higher-end editions of the service. Many customers and potential customers who have interest in analytics and business intelligence but simply cannot afford a higher-end Office 365 edition. So the question about being able to access Power BI in a Home edition is legit, and I am glad the team responded to it and the good news is that it is a highly-requested feature, and we may see something on that soon. From the answer here:

Customers have asked for some subset of Power BI functionality be available in other versions of Office and it is something we actively discuss. We are hoping to have great news in the coming months. (Carl Perry, PM)

Power BI On-Premises

Power BI currently only works with Office 365 and SharePoint Online. One of the reasons is that with cloud-based services, Microsoft is able to release quickly and release often. Hence, the response here is that it won’t happen at least until the next version of SharePoint is released. From the answer here:

For Power BI on-prem, the earliest you’ll see this is in the next major release of SharePoint. (As of today we don’t have dates to share/discuss for this release.) We’re taking advantage of the capabilities of Office 365 cloud services to release early and often in Power BI, but on-prem SharePoint is releasing on a more traditional on-prem schedule. [Matthew – Data Catalog PM]

Duplication of languages/interfaces

Microsoft now has several ways of doing the same thing, across client and server. For example, in Power Query they are using the “M” language to transform and shape data, whereas their SQL Server Integration Services, their Extract/Transform/Load tool has its own expression language. They are aware of this, which is a good thing, but there will always be overlap in functionality across their tools. Similarly, Power Query and Power Pivot offering similar ways to manipulate data. From one response here:

We are working hard on making sure we remove as much overlap as possible in the UI, for example we want to have a single place to import data in future updates of the product. The overlap between M and DAX is a very interesting as in nature they serve very different purposes. DAX is very analytical in nature, meaning optimized for business problems / aggregations. M is optimized is a functional language optimized for data transformations. I agree there is some overlap between DAX calc. columns and M and we are thinking about optimizing the overlap here. (K de Jonge, AS team)

Another response here:

We are definitely working on our relationship, as with any marriage it’s a lot of hard work. No, seriously, you will some of the more annoying issues fixed in the current version of Office and we are looking into how to optimize our two products for future versions. (K de Jonge, SSAS team)

We’re actively working on improvements in Power Query that will address the most common causes of this type of “destructive change” problem. We release a new version of Power Query each month, so be sure to stay up to date. [Matthew – Data Catalog PM]

And another one here:

Although both “M” and SSIS expressions are tools for manipulating data, the contexts in which they operate are different enough that it’s pretty unlikely that M will replace SSIS expressions.

But with this said, we are actively looking at ways to make Power Query and SSIS play better together. Prior to Power BI GA we separated the Power Query engine (the part that executes queries) from the Power Query add-in UX, and are looking at a variety of scenarios where that engine can be hosted in different processes. SSIS is an obvious one. [Matthew – Data Catalog PM]

Power BI for Android

This was bound to come up, and per Microsoft the fact at the moment is that Android has tremendous reach in consumer space, but not much in the enterprise. As a result, the focus has been on Windows and iOS. From their response here:

We decided to initially focus on Windows and iOS first. Android does have a big market share in the consumer space but its growth in the enterprise was less initially. We are actively monitoring the trends and Android support is definitely on our roadmap. (Cindy S. – Mobile BI PM)

Power BI with Dynamics? Maybe a Salesforce connector hint?

While Power Query can pull from a variety of data sources, there is no connector to Dynamics CRM. Power BI officials said that they have friends in Dynamics team and something should happen soon. At the same time they seem to hint that something else in the CRM space may happen soon too. Could that be a hint that a salesforce.com connector is coming soon, given the recently announced partnership between the two companies? See here:

We have some really good friends in the Dynamics team and are pushing to make getting that data in to Power BI as seamless as possible – And, as we generally like to make new friends, we’re working on some new CRM capability provider friendships as well.

Feedback

Feedback for Power BI is being collected via Connect, and it goes straight to the team’s Visual Studio Team Server. They use the votes up/down as a factor to prioritize the backlog of features. See here:

Challenge accepted: http://connect.microsoft.com And as proof yo: http://imgur.com/BHmZTh7

Connect items go straight into our VSTS instance so they really do get looked at! We track the up/down votes and that goes into our planning cycles. You can also give feedback through the product now! There are links and smiley faces in the products that you can use to give us feedback. Also: http://social.technet.microsoft.com/Forums/en-US/home?forum=powerview%2Cpowerbimobileapps%2Cpowerqanda&filter=alltypes&sort=lastpostdesc

(Will T – British Power View PM)

Third party visualizations

On the topic of allowing third party visualizations, Microsoft said that they are focused right now on getting the HTML5 version to be on parity with the Silverlight version. Once that happens, they plan to add many more visualizations and maybe even open it up to third parties. That’s promising! See here:

Currently the Power BI team is working on bringing the new HTML5 version to parity with Silverlight with respect to visuals and also adding some new important visuals. We will then be adding lots of new visuals and during that process, we will take a look and see how to open up the platform to third party custom visualizations.. Stay tuned! [Ajay, Power View PM]

It is very easy to duck questions in an AMA, especially when it is product roadmap type of questions. I think the Power BI team did a good job of taking the important questions, and responding fairly openly. I am most interested in the addition of Power BI in Home edition. What about you?

Here is a video talking about Power BI for Office 365:

Get Quick Answers Using Excel’s Status Bar

There is hardly any debate to the fact that Excel is an extremely powerful tool. It can do complex formulas and calculations at mind-numbing speed. All this being said, however, in all the years I have worked in IT, I have observed that most people use it for a basic set of functions. I believe that Microsoft recognized this as well. They made it very easy for us to get answers to our common questions. The problem is, they put this tool in an uncommonly used place. Today, I would like to introduce you to Excel’s “Status Bar” and to show you how it can make your life a little easier.

What is a status bar you ask? Well, it is way down at the bottom of the screen. You know, the place that no one hardly ever looks. In the picture below, you can see Excel’s main window with the status bar highlighted.

Main Window

Several things happen in the status bar that often go unnoticed. For instance, when you hit the “Caps Lock” key on your keyboard, an indicator shows up in the status bar. It is also home to some pretty helpful tools that can help you get quick answers from your spreadsheet.

Let’s begin by selecting a column of numbers like the ones pictured below. One of the most common tasks in a spreadsheet is to sum up a column of numbers. Notice that Excel makes this very easy. Below, you will see what the status bar shows when you have a column of numbers highlighted. By default, Excel shows you the average, sum, and count of the selected numbers.

Status Bar

If you right-click the status bar, you will get a context menu which will allow you to customize which fields you need displayed.  In the picture below, you can see the different options that you have. Notice the red circled area. Here, you can add other popular functions to the status bar.

Customize

Let’s add minimum and maximum to the status bar and see what we get. If you highlight your data as pictured below, you now will notice that the status bar gives some additional information. Highlighted below, you can see the “Min” and “Max” functions. Sometimes it is nice just to be able to glean out what the smallest and largest numbers in a spreadsheet are. The status bar makes this very simple to find.

Min Max

I hope this tutorial helps to simplify some of the common tasks you perform in Excel. Of course, if you enjoy typing “=Sum(H1:K8)” every time you want to get a Sum in a column, then this tip probably isn’t for you. ;)

Let me know what you think about this tip. I love questions and comments.

Jazz Up Excel Spreadsheets Using Tables

Format As TableTake a look at the spreadsheet in the picture below? What does it say to you? Do you get the feeling you’re looking back in time at an old black and white TV? What this spreadsheet needs is some spunk! Today I would like to show you how Excel can turn this bland spreadsheet into a work of art!

Spreadsheet

Microsoft Excel 2010 has a real handy feature called “Format as Table”. This feature can be found on the “Home” tab of the ribbon toolbar.  See the picture below.

Format As Table

To use this feature, highlight your spreadsheet, and click the  “Format as Table” button. This will drop down a menu with different samples of tables you can choose from. Click one of the choices on the menu to apply it to your spreadsheet. A dialog box will pop up asking you to confirm where the data for your table resides.  Since you highlighted your spreadsheet to begin with, this data should be correct as shown. Since our spreadsheet does have column headers you want to make sure that the “My table has headers” check box is checked. Click OK to apply the table style to your spreadsheet.

Confirm Data Window

In the picture below, you will notice that your table style has been applied. You will also notice that there are little downward pointing arrows beside each column heading. These are filter arrows. If you click the filter arrow beside the heading labeled “State”, for example, you will have the option to filter the list to only show the states you want to see.

Table Headers

When you click in your newly formatted table, you will notice a new tab on the ribbon toolbar called “Design”. In the picture below, you will see this “Design” tab gives you the option to change the table styles. If you hover your mouse over the different table styles, the table changes to give you a preview of how it will look if that style is applied; however, the style will not be applied until you click it.

Design Tab

On the same “Design” tab, you will see “Table Style Options” group. Here you can add different formatting to the first column. You can even add a “Total Row”, as shown in the picture below.

Total Row

As you can see, using the “Format as Table” option in excel, is an easy way to add color and functionality to your spreadsheet. How would you use this feature in your spreadsheet?  Comments are always welcome. Please see the comment field at the bottom of this post.