Power BI pro tip: using Access Online for data entry

With powerful self-service BI tools such as Power BI comes the need for business user data entry; data does not exist in source systems or does need to be enhanced / enriched before going into the report, or the business user just wants to change the way the data is organized. In those cases (which are present more often than not) we need to find a way to give the business user an easy to use way to do data entry while keeping it robust: i.e. not use a tool the user could easily make mistakes in and hurt the reporting process. You could use Excel but you would have to secure it so no mistakes can be made. Also, SharePoint lists are a good option if you have less than 5000 data rows (that’s the hard limit in SharePoint Online). If you need to store a lot of data and need a robust solution, Access Services or Access Online is a great tool for the job and the best part is it works perfectly with Power BI.

Perhaps the biggest change in Access 2013 is that it now stores that in SQL Server Databases rather than Access files. In this post I will show you how to build a sample application concerning reports on KPIs for production plants around the world. The data is entered by the business user using a web form generated by Access and the dashboard is created using Power BI. So here we go.

First step is to get the data. For that I created a simple Access 2013 application that I published on my SharePoint Online site. The Access application consists of three tables: KPIs, Periods, Plants and of course the actual facts: the KPI Values. On top of this sits a very basic data entry screen that enables the user to enter new actuals and targets for a KPI for a period for a given plant:

I entered some test data and saved the app. Imagine your business user just entering their data in here.

The next step is to get the data out of the SQL database Access Services will store it in and build a report / dashboard on top of it. For this, you will need to go to the Info pane of the File menu in Access. Look for the ‘Manage’ button next to Connections:

If you click it you get a big flyout presenting you with a lot of options. You will need to select the following:

-From My location or From Any location. I chose from Any.

-Enable Read Only connections.

See this screenshot:

Now, click on ‘View Read-Only Connection Information’ and leave it open for now. You will need to later.

Next step is to start Excel, go to Power Query, select From Database à SQL Server (and not Access since data is stored in SQL Server by default in Access 2013).

Copy paste the server and database name from the Connection information screen in Access and choose Ok. In next screen enter your credentials and passwords (again copy/paste from the connection information screen in Access). After a while you can select the table you are interested in and you can load the data into PowerPivot. I loaded my Plants, Periods and Values (I skipped KPIs since it was only the KPI label):

Next step is to create relationships between tables in PowerPivot, hide some columns as well as add a KPI definition. I ended up with this model:

Now, with Power View I created the following basic report (I did not give myself time to work on the layout, this is just quick and dirty):


This concludes this Power BI Pro Tip!

Hybrid BI environments with Power BI whitepaper

A new whitepaper highlighting best practices for building hybrid business intelligence environments with Power BI has been released. I highly recommend reading it: http://blogs.msdn.com/b/powerbi/archive/2014/08/26/best-practices-for-building-hybrid-business-intelligence-environments-with-power-bi.aspx


Combining text (csv) files using Power Query – follow-up

I have been getting quite a few responses on my original post on how to combine text (csv) files using Power Query. One of the FAQs is how to keep the filename where the data came from in your result set. Said differently: what you want is the contents of all files in a folder plus the filename of the originating files all in one table. I thought it was simply a matter of adding a column, but Nicolas pointed out in a comment that adding a column would create a cross product of all data and all filenames. So, I needed to come up with another solution. The solution I present here might not be the best one, but it works. The magic trick here is knowing that the Csv.Document() function exists. Allow me to explain. First of all, I followed the “normal” approach to list files in the folder using Power Query. What you get is: Now, it might be tempting to expand the ‘Content’ column (as I did in the original post). However, as in this scenario the goal is to get the contents as well as keep the originating filename, we need a different approach. What we need to do is add a custom column that equals the following:

What this is doing is opening the contents in a single column and it expects CSV format. The custom column shows up like this: Next step is to expand the table. The contents will be displayed in your custom column: Then, I did a split on the CSV separator (semi-column in this case), so I ended up with three columns: The only thing left to do is clean up (remove columns) and filter rows (since my CSVs had headers the header from the second CSV is still in my data). The end result is: For your reference, here is my code:

With this I hope Sunflowers and Nicolas are happy J

Macro malware on the rise (again)

I have reported on the subject of macros and how I consider them a security risk multiple times on this blog (see http://dutchdatadude.com/macros-are-dead/ and http://dutchdatadude.com/keep-macros-under-control/). Also, in my talks macros are a frequent topic.

Now, Virus Bulletin (https://www.virusbtn.com/virusbulletin/archive/2014/07/vb201407-VBA) has observed a rise in macro related malware, specifically trojan horses. I encourage you to read the report and see for yourself why macros need to be treated carefully. Maybe, sometime in the future, macros will be really extinct. I certainly hope so. However, before that happens we will need to find a way to bring comparable functionality to end-users without the security problems.

Power BI Pro Tip: Name your linked tables

Just a quick Power BI Pro Tip this time: if you use linked tables to add data to your data model in Excel, before you press the ‘Add To Data Model’ button be sure to go to the table properties in Excel and give your table a better name (better than Table X). This makes figuring out which data you are looking at so much easier. You will thank me later J

Power BI Pro Tip: Show Top x results with RANKX() function

One of the more frequent scenarios is listing the top X results, such as most profitable products, biggest customers, top 10 best selling stores, etc. Also doing a top X selection helps reduce clutter in charts: a lot of data points can work as noise and obscure the data points that really matter and make the biggest impact.

In this post I describe an approach to implementing these scenarios using Power Pivot’s RANKX() function.

Let’s start with a simple dataset consisting of products (P1…P20 in my sample), Cities, Sales Amount and Number of products sold:


After adding this table to the Power Pivot data model, we can use the RANKX() function to get the best selling products / cities etc. I added the following measures to my table:

Sum of Sales Amount:=SUM([Sales Amount])

Sum of Number Sold:=SUM([Number Sold])

Rank of products by sales amount:=RANKX(ALL(Sales[Product]);[Sum of Sales Amount])

Rank of city by number sold:=RANKX(ALL(Sales[City]);[Sum of Number Sold])


These measures allow me to determine the top selling products by sales amount and best cities by number of products sold.

Only thing left to do is to use a Pivot Table / Pivot Graph or Power View / Power Map visualization and display the results.


If you create a new Pivot Table and add the Product column and the ‘Rank of product by sales amount’ measure you get the following:


So how do we get the top 10 selling products by sales amount is a nice ordered fashion? Very easy, just a matter of the right sorting and filtering. Click on the little downwards pointing triangle button at Row Labels and choose ‘More Sort Options’. There I chose Ascending and then selected the rank measure:


Now the Pivot Table is sorted by rank with the highest ranking product at the top. Now, to filter out only the top ten, we press the same button again and choose Value Filters and then Top 10. Here I made the following selections:


This seems maybe a bit counter intuitive, but what this does is return the lowest ten ranks (which would be 1 to 10 or the highest ranking products). Alternatively I could have used a Lower Than or Equal To Value Filter with these settings to produce the same result:

And here it is: a top 10 of products by sales amount.


Of course, you can also use Power View or Power Map to visualize these results. Here is a Power View based on the same information:

The trick here is to create the visualization just as normal (as above). Above displays the sales amount by product and the number sold by city. However, the catch here is that both the graph as well as the map have a filter on them that utilizes the rank measures I created. Here is the filter for the chart. The ‘Rank of products by sales amount’ measure is filtered to showing only values less than or equal to 10, i.e. the top 10.

What’s best about this is that it is very easy to change from top 10 to top 15 to top 5 or anything you desire. Also, the Power View is fully interactive. For example, clicking on one of the cities on the right shows which products are sold in that city. Note that it does not show the top 10 products in that city however.

Hope you liked this Power BI Pro Tip!

Version compatibility between Power Pivot Data Models in Excel 2010 / 2013 and SharePoint 2010 / 2013

I have been getting a lot of questions on compatibility around Excel 2010 / Excel 2013 / SharePoint 2010 / SharePoint 2013. To be honest, I have been confused myself.

I encourage you to check out our Excel help page, which makes it crystal clear:

Note to SharePoint Server 2010 customers: Client applications used to create a Data Model need to align with the server applications that host a Data Model. For SharePoint Server 2010, this means you’ll need to continue to use Excel 2010 and a Power Pivot for Excel add-in to create and maintain a Data Model. Excel 2013 cannot be used to create Data Models that run on SharePoint Server 2010.

This means that you can upload an Excel 2013 file with a Power Pivot data model in it to SharePoint. However, if you interact with it (click refresh, click a slicer, etc) you will get an error message.

It does not get any more specific than that, right?



You will not believe these sites have been built with SharePoint

I get myself in many discussions about SharePoint as application platform for public / customer facing sites. The reason I end up in the discussion is because people are looking to use our great BI tooling to provide insight to their customers.

Most people do not believe that SharePoint is used to create internet sites (most people just know SharePoint as a collaboration platform).

Here is a great site that gives you an idea of what websites were created with SharePoint and it even features a great BI visualization of the data (PivotViewer). I encourage you to go check it out!



My personal favorite is http://www.ferrari.com. Can you believe it has been built using SharePoint?


Using Power BI to analyze the BI market

Disclaimer: Yes, I know the Gartner Magic Quadrants normally should be bought to be able to use. The information in this post however is based on the following web search: http://www.bing.com/images/search?q=gartner+magic+quadrant+bi&FORM=AWIR which happens to return the Gartner Magic Quadrants…

Recently I thought about the Gartner Magic Quadrants and how they offer a great view on the BI market and the BI vendors. However, they provide static views and it is hard to spot developments over time. With that, I thought to myself that an animated scatter plot would be just the thing to make these MQs much more interesting.

Power View (part of Power BI) provides this kind of visualization and here is what I did.

First, I followed a very advanced procedure to get coordinates for each BI vendor for each year in each MQ. This advanced procedure involved complex machine learning resizing the MQ images to 100×100 pixels and me manually writing down all the coordinates. This resulted in the following table:

Vendor Date Xcoord Ycoord Completeness Of Vision Ability To Execute






Panoroma Software

























The vendor is or course the name of the BI vendor in the MQ. Date is the year the MQ was released (I did not take into account months). Xcoord and Ycoord are the X and Y coordinates respectively and the next two columns (Completeness Of Vision and Ability To Execute) takes Xcoord and Ycoord and substract 50 from it so the midpoint for the scatter plot is 0.

Although by now I would have been ready to plot the data I decided to make it a bit more interesting by also including market capitalization for each vendor. In order to do this I added another table which looked like this:

Vendor Stock
arcplan PRIVATE 1
Panoroma Software PRIVATE 2
Spotfire PRIVATE 3
Applix PRIVATE 4
Actuate BIRT
QlikTech QLIK
MicroStrategy MSTR


Using the Stock Indicator I used Power Query to get the stock information for the “date” the MQ was published (which I fixed to 1st of January of that year). The data I got from http://www.quandl.com which provides CSV downloads per stock (for example the historical market capitalization for MSFT can be retrieved from http://www.quandl.com/api/v1/datasets/DMDRN/MSFT_MKT_CAP.csv). What I needed next was a function that would retrieve the market capitalization data for a given stock label. I create a function called MarketCap that takes a stock label as input and then reaches out to Quandl to download the CSV. Next it does some formatting and returns the data. Here is the code:

All I needed to do was get Power Query to iterate over all stock labels and call this function for every stock label. I used my Excel table shown above as source and inserted a customer column that called the MarketCap function with the stock label. Next, I expanded the returned column and removed some columns. The code is shown below:

Next, I loaded all of this into PowerPivot and created a relationship between the MQ scores table and the Vendor table.

However, I could not create a relationship between the MarketCapData and the Vendor table, since I would need to draw two relationships: one on Vendor and one on date (or actually year). However, I still wanted to get the Market Capitalization per vendor for the moment the MQ was released. Luckily, we have LOOKUPVALUE (which I discussed earlier). With LOOKUPVALUE I added a calculated column to the Scores table to retrieve the Market Capitalization:

Next I went ahead and did some cleanup (hide columns / tables).

Finally, it was time to build the visualization using Power View:

What you see here is a line graph showing the market cap data and a scatter plot with play axis on Date that shows the scores per vendor for Completeness Of Vision (horizontal axis) and Ability To Execute (vertical axis). I could have used market cap data for the size of each vendor’s indicator but that makes the chart unreadable. So now we can go back in time and see all the MQs for BI one after the other in a nice animation:

Notice how in the video I show the development of Microsoft over time. Also see that the top chart responds to the selection I made in the scatter plot.

This wraps up this post. Hope you liked this demonstration of meta-BI using Power BI!