Sessions at SQLBits

I am very happy to share that I’ll be doing several sessions at the SQLBits conference this year.

I will be doing the following sessions:

  • We now have an exciting developer story for Power BI, the session Developing for Power BI I will go into how to write applications for Power BI and show you several examples. Lot’s of code here, from PHP to .Net, JSON and REST and OAuth.. not your average BI session Smile.
  • The BI Power Hour will be done for the first time outside of a pure Microsoft event, ill be doing this together with Power Hour superstars Matt and Matthew:  BI Power Hour
  • Lastly I will do a Pre-con together with Matt and Matthew where we will take an entire day to take you through the entire Microsoft data stack from Azure Data Factory and Azure Machine learning to Power BI. This is going to be a great way to see how all of these services work together and get a in depth overview of what all these services contain.

Looking forward to seeing you there!


Power BI news

You probably have heard the news already but we have some exciting information that has been released on the new Power BI service as you, for all the details read the blog post on Microsoft.

Some great new functionality that is being released are:

  • - Dashboards that allow you to gain insights in your data right away. A single pane of glass for all your data. You can mix and match data from several data sources onto a single dashboard.
  • - Many new datavisualizations like treemap, fill map and many more.
  • - An amazing Ipad  app to connect to your data
  • - Out of the box connectivity to many data sources like SalesForce and GitHub.
  • - Hyrbrid connectivity, meaning connect to your on prem tabular models and visualize using the latest and greatest data visualizations in the cloud.
  • - Developer API’s that allow you to create datasets and push data into Power BI. (see for more details)
  • - Last but not least is the new Power BI designer that will merge Power Pivot, Power View and Power Query into a single experience that will focus on making analytics visual and easy to do.

Of course this is a preview release so there is much more to come soon for all this functionality.

And of course the last very important item is the price:

Today, anyone with a US business email account can try the preview of the new Power BI for free. We’ll expand to international users in the future. When we reach general availability we will introduce a free offer, available as Power BI. We’ll also introduce Power BI Pro, available at a low monthly price, with additional data capacity and features for the enterprise. In anticipation of this move, on February 1 we are lowering the price of our in-market Power BI service to $9.99 per user a month, a 75 percent reduction in price. More information on these offers is available here.

I have a few blog post I am working on to cover some new functionality for the designer and the developer API’s that I’ll finish soon!.

Now go to to go and sign up yourself.

Update: I got some questions about what happens with existing Office 365, the straight answer is that existing Power BI for Office 365 customers will be able to transition to the Power BI Pro experience when it is made available using their existing subscription license. Additional details will be made available as we approach general availability. For more details see this blog post:


Loading an image into the Model using Power Query

Last week I had an interesting questions, someone wanted to show images in Power View but didn’t want the report to have to fetch the images every time. So the images needed to be loaded into the model.

I have this simple Device table that I loaded from Azure into Power Query:



Read more »


New year and several updates

As most of you have probably noticed I have not been very active recently, this has everything to do with the recently released preview for Power BI that took all my time. Luckily now that we shipped the first preview I hope to be able to spend some time on blogging again, mostly on the amazing new designer when more features come online for that.

Right now I am playing with the Power BI API’s in the hope that I can write a Power BI for WordPress plugin that allows anyone to track their usage using a wordpress plugin by directly  psuhing visitor statics into Power BI, more on this in a later blog post :).

I also updated the commenting system to use disqus, the out of the box wordpress commenting system is not great. This plugin looks great imho :) Let me know if you have any issues or comments on the new commenting system.

Happy new year!



Automatically ranged date table using DAX and M

I am prepping some demo’s for my SQLPass summit session “End-to-End Demos with Power BI” where I’ll show many of the tricks I use in my Power BI workbooks.

One of them is this trick that I recently figured out, I am so excited about it that I wanted to share it with all of you. I figured out a way to automatically generate a data table with a dynamic range based on the data in the fact tables.

Read more »


Export a table or DAX query from Power Pivot to CSV using VBA

Had an interesting question today. Someone did some great transformations combining multiple files into a single table and adding some calculations using Power Query and Power Pivot. Now he wanted to load that data into CloudML to do some machine learning on top of this data. Currently cloudML takes csv files so the question was how can I get data from the Power Pivot model into a CSV even when there are more than 1.000.000 rows.

It turned out to be pretty easy by combining two other blog posts: one of my blogs on how to access the data model combined with this blog I found on how to write to CSV from vba.
Read more »


Getting data into Power Query with the twitter search API (how to hack PQ to use OAuth)

Last week I decided to try an interesting experiment. Lets see if I can get data from the Twitter REST API into Power Pivot using Power Query. Power Query already supports two out of three things that are needed for me to import this data:

  1. Import from a web url is already supported
  2. Parsing JSON data is already supported
  3. Logging into the service with OAuth is not supported for a datasource that is not developed by the Power Query team. Unfortunately even though most products use OAuth, no OAuth implementation is the same. Thanks to some hacking with Power Query I managed to work around this.

So lets go take a look at how we can solve this.

Read more »


Import data from TFS online into the data model through Power Query

I was playing around today with some TFS data (that’s were we store all our product data Smile) and of course I was looking for a way to get this data into Power Pivot and Excel. I found a way Smile

It turns out TFS online supports OData for most of their services. See this blog post on how to enable it and where to connect to: 

The important thing is this:

Team Foundation Service authentication:

(Optional) In order to authenticate with Team Foundation Service, you will need to enable and configure basic auth credentials on

  • Navigate to the account that you want to use on For example, you may have
  • In the top-right corner, click on your account name and then select My Profile
  • Select the Credentials tab
  • Click the ‘Enable alternate credentials and set password‘ link
  • Enter a password. It is suggested that you choose a unique password here (not associated with any other accounts)
  • Click Save Changes

To authenticate against the OData service, you need to send your basic auth credentials in the following domain\username and password format:

  • account\username
  • password
  • Note: account is from, username is from the Credentials tab under My Profile, and password is the password that you just created.

So I did that for my TFS online instance and opened up PQ and selected import from OData:


Next I pasted in my workitems OData url (that I got from the site above):


This prompts me for authentication, and I choose basic auth and enter my credentials:


This opens up the query editor and I am good to go from that point:


It’s pretty cool that this worked and I though I share it Smile