Working with Excel Files in Power BI V2

In the beginning, Excel was at the center of Power BI. In the “V1” version of Power BI, the entire product was all about enabling analytics in the cloud, and Excel/Excel services was the delivery vehicle for those analytics. The authoring tools were all Excel add-ins, and the service revolved around updating data models in Excel, and allowing you to work with data models greater than 10 MB. Power BI “V2” changed that focus completely, completely removing that dependency on Office 365 and Excel. However, all of the Excel goodness that was within the “V1” product is still there, it just may have moved around a bit, and it works a little differently now.

In the early days of Power BI V1, I wrote up a post about the limitations of the product, specifically the file size limitations. Reviewing this article recently, I was actually struck by how little has changed. The maximum data model size remains 250 MB. In fact, 250 MB is the maximum size of any data model in Power BI, whether or not it originated in Excel. What has changed, rather drastically is the means of enabling this 250 MB limit for an Excel file, which we’ll get to below.

Let’s have a look at some of the major differences between Power BI “V2” and Power BI “V1” as it pertains to Excel.

More than just pretty face

In V1, Excel was almost always used as a means of presenting data. It was possible to use Excel as a data source, but the presentation of that data would inevitably be through another Excel file.

In V2, Excel is far more commonly used as a data source. The Power BI Designer, or the web interface can connect to Excel files, and then import the data into data models stored in the service. Native Power BI visualizations are then used for data presentation. These Excel files can be local, in OneDrive, or in OneDrive for Business. If the files are in OneDrive, or OneDrive for Business, the data models can be automatically refreshed when the source workbooks change. However, we can continue to work with Excel as a presentation mechanism.

If the Excel file is stored in OneDrive for Business (as opposed to OneDrive), you can connect the Power BI interface to the workbook in place. Once connected, the workbook can be viewed and interacted with through Excel Services right within the Power BI interface. To do this, from the dashboard, select “Get Data”, choose Files, select the OneDrive for Business option, select the desired workbook, and finally, select the “Connect” button”. You will then be presented with two options.

Importing data from Excel vs connecting to a workbook in place

Note that these two options will ONLY appear if the repository for the workbook is OneDrive for Business. Selecting “Import” will import the data contained in the file into a service based data model. This is the operation that will occur for all other repositories, and it uses Excel as a data source. Selecting “Connect” however connects to the workbook in place, and it adds the workbook as a report to the Power BI user interface.

Excel Workbook in the Power BI section

The workbook appears in the Reports section with a small Excel icon beside it. To view the workbook in Excel Services, click on the ellipsis to the right of the name, and select View. The workbook should load in a new window in full fidelity, and allow interaction.

No artifacts are created in the Datasets or Dashboards sections, the workbook is a self-contained unit. This is important because personal sharing can only be done through Dashboards. Therefore, Excel Services based reports can only be shared through Office 365 Groups.

Office 365 Groups

One of the biggest changes that Power BI “V1” users will notice (and need to deal with) is the fact that in order to work with Power BI in the same manner, Excel workbooks MUST be stored in a OneDrive for Business repository. With “V1”, a workbook could be stored within any SharePoint Online repository, and enabled for use with Power BI. Therefore, all “V1” users will need to move these workbooks into OneDrive repositories before the “V1” service is deprecate (Dec 31, 2015).

Every Office 365 user gets a OneDrive repository by default, and these repositories work just fine for personal use, but most current users that are using SharePoint Online will have workbooks in shared libraries in a collaboration environment. Office 365 Groups also each have their own OneDrive for Business repository, and all group members have access to that repository. Power BI V2 fully supports Groups, so this is the logical place to store all of the “V1” workbooks for collaboration purposes.

Connecting to a workbook in a Groups OneDrive is identical to the process above, but first, you need to navigate to the Group’s context in the Power BI UI. This is done by clicking on the My Workspace button (and not entirely obvious).

Groups Selector

Simply select the group and you will be working in that Group’s context.

Increased File Size

One of the biggest benefits of the original Power BI “V1” was the ability to work with workbooks that contained data models larger than 10 MB. The way that this was done was by “enabling” the workbook for Power BI as I outlined in this article. The enablement mechanism no longer exists, but the benefits are still there. The process of connecting a workbook outlined above intrinsically enables the workbook for Power BI and increases the maximum model size from 10 MB to 250 MB. In addition, the workbook can also be opened and interacted with directly from OneDrive. It is also possible to share that workbook with others in your organization, but in order to open those large workbooks in a browser, those users will need a Power BI licence.

Workbook Refresh

The ability to refresh workbooks stored in the cloud from data stored on-premises was, at the time of its introduction, the most important feature of Power BI. Each workbook would be enabled for refresh through an administrative interface, and if the data source matched a registered data source, the service would call an on-premises Data Management Gateway, which would facilitate the refresh of the workbook. This worked, but was somewhat difficult to get set up. Power BI “V2” has simplified the process tremendously. This does however mean that the process has changed.

Most refreshes are performed on a Dataset, but as mentioned above, an Excel report is self-contained, so the refresh options are available from the report itself. Simply click on the ellipsis to the right of the report and select “Schedule Refresh”.

Report refresh options

The first time this is done, you will need to enter the Data Source credentials. Once entered these will be used for subsequent refreshes. This will also need to be completed before the report can be refreshed on demand. You can also schedule the refresh time here.

If the data source is a supported cloud source, no further steps will be necessary. If it is on-premises, then it is necessary to install the Power BI Personal Gateway. This does not require administrative permissions to run (although it’s best if you do), nor does it require any special permission on the service side. The Personal Gateway is meant to be just that – personal and easy to use.

Hopefully this covers most of the major differences of working with Excel workbooks in V2 of the service. It looks like a big change, but most things are still possible, and some things significantly enhanced.