Skip to content

Category: Power BI

How to Hide Objects From the Power BI Mobile Apps

If you’ve worked at all with the Power BI mobile app, you’ll find that it’s quite straightforward to use. What it does in essence is to render out objects from Excel workbooks in a manner that is easy to consume from a mobile device. It uses Excel Services to render the content, but it doesn’t do so in a manner that may be familiar to Excel Services users, which is to essentially replicate the spreadsheet editing environment in a browser. Instead, each named object is presented as a distinct object, and in favourites, the workbook is represented as a section.


Clicking on any of the objects opens it in full screen mode. Swiping down from the top, or right clicking on the report opens a bar at the top that allows you to navigate to any of the other objects.

By default, any named object will appear, as well as Power View reports. (It should be noted that as of right now at least, Power Map objects are not rendered by the Mobile app). However, what happens if we don’t want an object to be rendered in the application? If you use Power Query, and you follow my recommendations of loading the data directly into the data model, you’ll likely bump into the need to do this fairly quickly. Queries are named objects in the workbook, and are rendered by the mobile app.


In the screen above, there are actually 7 objects in the workbook, but the favourites view can only display 6. Three query stubs are blocking a Power View report. Not an ideal situation. What we want to do is to hide these queries, but how to do so is not immediately obvious. The BI app uses Excel Services to render the objects, and since the early days of Excel Services, Excel has had a mechanism to control what gets rendered by Excel Services.

If we open the source workbook in Excel we will see that all of the named objects are contained in worksheets named “Pivots” and “Pie” another worksheet, “Power View 1” contains the Power View report. The queries are all stored in different worksheets. In order to control what gets rendered, we first click on the “File” tab in the ribbon, and click the “Browser view options” button.


You are then presented with a dialog that allows you to determine what is rendered by Excel Services. The default is “Entire Workbook”, but you can select specific “Sheets”, or for very fine grained control, “Items in the workbook”.


In our case, we just want to turn off items in the unnamed sheets, so we deselect them. Once this is done, all that is necessary is to save the workbook back into its library, and the results become immediately apparent on a refresh of the app.


This is a bit of a brute force approach. It affects not only the mobile app, but also anyone using the workbook from a browser – those sheets will be invisible to any users using the Excel Web App. I can foresee a need to hide things from the mobile app but not from the browser. I can also see a need to control the layout of the objects, but these are early days. For now, I’ll take this approach.

1 Comment

Query Limit with Power BI On Premises Data Management Gateway

UPDATE – 2013-11-17 – After installing the November 2013 refresh of the Data Management Gateway Preview, this restriction appears to have been removed. The workaround is not longer necessary, but is still valid.

After successfully setting up the Power BI On Premises Data Gateway, and setting up a data source, you can use Power Query to query that source through the oData data source. However, if you have a significant amount of data, you may encounter an error:

[DataSource.Error]   Cannot parse OData response result. Error: The maximum number of bytes allowed to be read from the stream   has been exceeded. After the last read operation, a total of 104858624 bytes   has been read from the stream; however a maximum of 104857600 bytes is   allowed.

As has been recently confirmed by the product team, the maximum amount of data that can be returned from the gateway is 100 MB. I have no confirmation of this, but it may be removed or increased in the future. However, for now, if you hit this limit, you are stuck. One way around it is to create segmented queries that come in under the limit, and merge them together through Power Query’s Append function.

Leave a Comment

Power Query Preview vs Power Query Release – How Can I Share a Query?

You learn something new every day. Hopefully this helps a few confused souls, it would have helped me.

Microsoft first announced the release version of Power Query in July 2013, and last week (August 2013) it put out a stability update for it. Not wanting to be left behind, I gleefully installed the update, and when I got my invitation to try the Power BI preview, I went ahead and started working with it. One of the first things that I wanted to try was to work with shared queries. I couldn’t figure out how to create one. After a little research, i knew that I was missing something. There is a section (The Organization Section) in the Power Query ribbon where you can sign in and access shared queries.


The problem was, I didn’t have it. Initially, I thought that it was left out of the preview, but then quickly realized that others had it. Then perhaps that it was a license issue, or an installation issue. However after installing on three different systems with 2 different bit levels, I gave up and posted my dilemma in the community forums.  It didn’t take long for someone from Microsoft to chime in with the answer.

There are two different versions of Power BI. The released version (the one from July and the stability update) is meant for standalone use, and does not have the ability to connect to a Power BI site. The preview version is for corporate use, and it does have that capability. The release version is 1.5.3296.2082, and the preview version is 2.6.3387.121.

Power Query (for use with Power BI sites) version 2.6.3387.121

Power Query (standalone) version 1.5.3296.2082

Once I installed 2.x, everything was good. Luckily, the main Power BI download site now points to the version 2.x, so not too many people should be caught by this. If you have been, hopefully this helps.