In this post I will walk you through all the steps necessary to expose your on premise enterprise data to your Power BI users of Power Query in Excel and also will demonstrate how to source that data into a Power Pivot workbook.

I am not going to give you too much theory, rather, I will just walk you through all the steps with explanations where necessary (one assumption is that whoever will be walking through these steps will have admin level permissions to the Power BI site)

1 – Set up Gateway

Gateway has two components, first it has to be configured in your Power BI portal, second, you have to download and run a program that will provide the synchronization between your on premise and cloud environments

So let’s go ahead and configure it, go to portal.onmicrosoft.com and login with your Power BI credentials, after you logged in, click on Admin-> Power BI

This should take you to the Power BI Admin center; click on Gateways next.

In my case I already had created a BIGateway; you will need to click on “+ new gateway” bottom and go through the wizard to have it installed and registered

2 – Set up data source

After the gateway is created, we need to set up a data source

First, in the data source usage, make sure that OData feed is enabled

Then click next to provide connection info

Click on Credentials to provide login/password information (this will download a run “Configure authentication” app)

Note that in my case Credentials Type says “Loading…”, my guess it’s because the Gateway app is running on a different machine, unfortunately I am not writing this post on the machine that is running the gateway app, on that machine, however, this screen give me an option to either provide windows or SQL credentials type and then specify login and password

After that you can click on data settings and select tables and views that you would like to expose through OData to your end users

IMPORTANT: something I learned the hard way, your tables or views have to have at least on not null-able column or they will be grayed out on the list of views or tables and you will not be able to select them.

Follow the screen directions to finisht this process up.

Now that your data sources have been defined, you can use Power Query to find and consume them.

3 – Power Query

In order to find your tables, go to Power Query ribbon and click Online Search (note, it takes a few seconds for all the sync activities to run their course so give it a minute)

Now make sure that you have Organization selected in your Search drop down

Now you can type in your search criteria and click search to get my available data sets back

And this is where the fun begins.

4 – Source to Power Pivot

It took me a while to figure out the best way to source that data in Excel (and when I say the best, I only mean it in my humble opinion). First, I went a standard Power Query route and right-clicked on one of the data sets and then clicked Edit. That opened up a Power Query window that subsequently allowed me to either load data to either Worksheet or Data model

If you pick Load to worksheet, Power Query will load the selected data into an Excel table, selecting the Load to Data Model option will load the data into the workbook’s Power Pivot.

I, however, recommend neither of these options. User Power Query to apply whatever transformations on the selected data set but make sure that both of these options are NOT checked before you click on “Apply and Close” button. Doing that will not load your data in Excel just yet, but rather will save that connection information in your Data->Existing connections->Connections in your Workbook

Now you can fire up the Power Pivot window, click on Existing connections, scroll to the bottom of the list and use our Power Query connections to load data in Power Pivot

Now we just need to create our relationships and add calculations

Enjoy!

PS.  apparently, the Power Query connections cannot be refreshed using Schedule Data Refresh option of Power BI, I provided the explanation and a workaround here

EDITED 5/2/2014: Good news, Power Query is not supported for Data Refresh in Power BI, follow this link for more information.