Using Power BI to combine Financial and Social Data for More Powerful Analytics

In my last blog post we talked about Power BI Designer Preview and why I was lovin’ it. In this post I’m going to use Microsoft’s most recently Power BI tool to connect to my SQL server instance to analysis my Google Analytics data, Marketing data and CRM opportunities.

Connect to your SQL Data

Let’s get started by selecting the Get Data button in the top left corner and then select SQL Server


Supply your SQL sever name and database that you would like to connect to


Select with tables you would like to use for your dashboard/report. I’m going to use my CRM, Google Analytics and KTLMarketing.


Transforming your Data

At this point we can edit the query before loading the table, by selecting Edit from the bottom of the window, or we can load the table(s) and then select each table to transform the data.

Now that we’ve connected to a data sources, we need to adjust the data to meet our needs.

The Query view in Power BI Designer makes ample use of right-click menus, in addition to having tasks available on the ribbon. Most of what you can select in the Transform ribbon is also available by right-clicking an item (such as a column) and choosing from the menu that appears.

When you transform data in the Query view, you’re providing step-by-step instructions (that Query carries out for you) to adjust the data as Query loads and presents it. Clicking on the View tab allows you to select the option for the formula bar so you can enter in or modify the DAX formulas with your transformation. The original data source is not affected; only this particular view of the data is adjusted.

The steps you specify are recorded by Query in the Applied Steps navigation pane, and each time this query connects to the data source those steps are carried out so that the data is always shaped the way you specify. This process occurs whenever you use the query in the Power BI Designer, or for anyone who uses your shared query, such as on the Power BI Service. Those steps are captured, sequentially, in the Query Settings pane under Applied Steps. Reversing your applied transformation is as simple as clicking on the ‘x’ to the left of the step.


For starters, I’m going to replace the null values with zero. I just right click the column header, select Replace Values.


Type null in the Value To Find field and 0 in the Replace With field and then click the OK button.


Additionally, I’m going to remove several columns that were imported within the KTLMarketing table by highlighting them and then right clicking and Remove Columns.

I’ve completed the transformations to my data that I want to do for now. I can always do additional transformations later by selecting Query and then the table that I want to work with.

Building Your Visual Dashboard/Report

I’m going to start my first report by looking at Google Analytics data. I drag sessions by date, page views by date and page views by channel grouping. I like what I have started but want to compare sessions to page views. To do that I simply select page views by date and drag and drop it on top of sessions by date.


Next I want to add some of our KTL Marketing data to the Google Analytics report. I select the KTLMarketing data set and drag the Blog post views by author, LinkedIn views by author and Twitter by authors statistics. What I am left with below is a clear prospective of where my traffic to my website is coming from and which employee is providing the most relevant posts with each social media channel that I’m analyzing.


Next I’m going to start a new report by right clicking a blank space in the Reports navigation pane to the left.


Next I’m going to select my actual value by customer, estimated and actual value by opportunity owner and then count of opportunities by name. I’m going to use this data to analysis who are best customers are, which opportunity owners do the best at estimating the opportunity and who is the best preforming employee and then I can use the opportunity name to see if there is any correlation between blog posts and the opportunities.


This is just a start into analytics using Power BI Designer Preview and is specific to our business needs. There is so much more that you can do with it and the best thing of all is its FREE!!!!!


Lovin’ it….. Power BI Designer Preview

Microsoft has a great new offering, Power BI Designer Preview, as part of the Power BI platform. It is a standalone Windows Desktop application that can be downloaded from the Power BI site. It is an optional Power BI content authoring option for users that are unable to upgrade to the latest version of Excel. The application combines Power Query, Power Pivot Data Model and Power View into a seamless experience that will allow users to build their Power BI dashboards and reports.

Getting Started Tutorial

Microsoft provides a great getting started with Power BI Designer tutorial here. It provides basic software installation instructions and how to start creating your first report. Additionally, when you first start Power BI Designer you’re presented a Getting Started screen with video tutorials on building reports.


Building Your First Report

Get Data

Within Excel 2013, users could get confused on what the right approach is to getting data into Excel. There really isn’t a right or wrong answer but users could do this with the Excel Data Tab, Power Query or Power Pivot. It just depended on what you wanted to do with the data.

Within the Power BI Designer, there is one spot to get data from. This is from the Get Data Ribbon item. The first click reviles the Most Common data sources and clicking on the More… selection brings up the additional sources on the right. These include the standard flat files, Databases, Azure and Other.


Preparing Your Data Model

Once data is loaded, you can combine, transform and build calculated columns in Power BI Designer’s Query view. As you explore Power BI Designer, you will notice the deep functionality of Power Query has already been made available.  Power Query functionality is laid out in the following three tabs:

· Home


· Transform


· Add Column


Power BI Designer has Power Pivot automatic relationship detection and data blending cooked in with one-to-one, one-to-many and many-to-many relationship support across the wide array of available data sources.  More detail on creating relationships between data sources is available on the Power BI support site.

Create Reports

After your data is loaded and prepared, you can begin building fully interactive dashboards and reports by dragging-and-dropping fields onto the canvas and filters. If you have ever worked with Power View, you will see a lot of similarities in the Power BI Designer Report user interface.


Limitations and Next Steps

This is a great start for Power BI Designer and I can’t wait to see what is next but realize that this is still a preview and not all features are in place with this application yet. One thing to note is that you don’t have full control over the Data Model. You cannot cross between Excel 2013 and the Power BI Designer and loading your designs up to your Power BI website is a manual data refresh process, except for Power BI new connector for SQL Server Analysis Services that allows customers to benefit from a cloud-based Power BI dashboard sharing site without having to move their data to the cloud.

Microsoft is focused on the monthly development and updates for Power BI Designer, so I look forward for the great things to come our way. The Add-ins for Excel 2013 and later are still available and customers can continue to use them to model their data and build reports but the updates will be less frequent.