Power BI – Losing Disk space when working with Power BI Desktop

I recently had an issue on a customer’s server, where I was developing Power BI Desktop reports. And the C: Drive was running out of disk space, which was attributed to my user profile.

Upon inspection I found that the TempSaves directory had a whole stack of temporary files saved in there. Even though I had previously selected to change the option in Power BI Desktop to delete files

I am pretty confident it has to do with the Auto recovery feature in Power BI Desktop.

If you are ever having any issues here is where you can go and delete the unwanted files that are no longer relevant.

Click on Start, then Run and put the following into the Run Command below.

%UserProfile%\AppData\Local\Microsoft\Power BI Desktop\TempSaves

This should then open the location of the Temp Saves for Power BI Desktop Files.

I then selected all the files that I knew I no longer needed.

And then deleted them.

NOTE: I did keep the most recent file, which had todays date, which I did currently have open.

I then could get back roughly 10GB of disk space.

Ask a Question (Q&A) Hidden Treasures in Power BI Desktop

The new Ask a Question (Q&A) in Power BI Desktop really does make it quick and easy to create new visuals.

Here is a link to the blog post from the Power BI Team: Q&A for report creation (preview)

I have been using Q&A in the Power BI Service for some time, and there are a few hidden treasures that you can now also use in Power BI Desktop. I will demonstrate a few of them that I know below.

I think the hidden gem is that the amazing people in the Power BI team have enabled Q&A to understand context so that you can not only ask questions as we would ask, but also it understands things like dates (Today, This Month), as well as for example highest, top, bottom etc.

So this can then be leveraged to easily create visuals that will always be in the context that they were created in.

Q&A for today

The first example, I created a card showing me the sessions for today 04 Jan 2018. The reason I wanted this, is because I am always interested in how many sessions I have for today. So as time moves, so will the card below, to always show me today, which is the context it was created in.

HOW COOL is that!

In order to confirm this, I validated it with the following screenshot below, showing what my sessions were for today 01/04/2018. As well as the DateTime where I have my PBIX file open.

And if I look on closer inspection at the Visual level filters, I can see where it has applied the filter automatically for me.

Q&A for current (this) month

A second example is where I want the total sessions for the current (this) month, so that as time moves on it will always show me the current month that I am in, again the context in which I created it. I did not script this at all, that it would add up to 1,000 exactly!

One thing to take note if, is that I chose “this month (Date > Date), which is from my Date table, and the Date column. This is because I have created a relationship from my Analytics table to the date table. This ensures that it is used within the right context.

Once again if I look at the Visual level filters it shows me how it created the measure in the card, as well as it shows that it will move with time.

Other hidden gems

As I found out from the documentation there is a whole host of hidden gems that you can use in context when asking a question.

Tips for asking questions in Power BI Q&A

At the end of this blog post are the current words and terminology that Q&A recognizes.

Differences between Q&A in the Power BI Service and Power BI Desktop

EDIT: 10 Jan 2018

Once again the Power BI team is incredibly quick at updating Power BI Desktop, and where I had previously said there were some differences between Q&A in the Power BI Service and Power BI Desktop, with the January 2018 release, it now appears to be identical.

So as shown below, if I was now searching for the date of my Max Sessions, this can easily be achieved with the following shown below.











Once again I am really impressed with Power BI, and I love working with the product!

It would be nice to have the ability to edit the Q&A as you can do on the Service. So I have created the following idea on the link below.

Vote here: Edit Q&A questions in Power BI Desktop

I hope that you have found this blog post useful and can start leveraging Q&A in Power BI Desktop, it really is a fantastic feature.

Power BI – Q&A Words and Terminology

If you are the owner of a dataset, add phrasings and synonyms to improve the Q&A results for your customers.

Aggregates: total, sum, amount, number, quantity, count, average, most, least, fewest, largest, smallest, highest, biggest, maximum, max, greatest, lowest, littlest, minimum, min

Articles: a, an, the

Blank and Boolean: blank, empty, null, prefixed with “non” or “non-“, empty string, empty text, true, t, false, f

Comparisons: vs, versus, compared to, compared with

Conjunctions: and, or, each of, with, versus, &, and, but, nor, along with, in addition to

Contractions: Q&A recognizes almost all contractions, try it out. Here are a few examples: didn’t, haven’t, he’d, he’s, isn’t, it’s, she’ll, they’d, weren’t, where’ll, who’s, won’t, wouldn’t.

Dates: Power BI recognizes most date terms (day, week, month, year, quarter, decade, etc…) and dates written in many different formats (see below). Power BI also recognizes the following keywords: MonthName, Days 1-31, decade.

Examples: January 3rd of 1995, January 3rd 1995, jan 03 1995, 3 Jan 1995, the 3rd of January, January 1995, 1995 January, 1995-01, 01/1995, names of months.

Relative dates: today, right now, current time, yesterday, tomorrow, the current, next, the coming, last, previous, ago, before now, sooner than, after, later than, from, at, on, from now, after now, in the future, past, last, previous, within, in, over, N days ago, N days from now, next, once, twice.

Example: count of orders in the past 6 days.

Equality (Range): in, equal to, =, after, is more than, in, between, before

Examples: Order year is before 2012? Price equals between 10 and 20? Is the age of John greater than 40? Total sales in 200-300?

Equality (Value): is, equal, equal to, in, of, for, within, is in, is on

Examples: Which products are green? Order date equals 2012. Is the age of John 40? Total sales that is not equal to 200? Order date of 1/1/2016. 10 in price? Green for color? 10 in price?

Names: If a column in the dataset contains the phrase “name” (e.g., EmployeeName), Q&A understands the values in that column are names and you can ask questions like “which employees are named robert.”

Pronouns: he, him, himself, his, she, herself, her, hers, it, itself, its, they, their, them, themselves, theirs, this, these, that, those

Query commands: sorted, sort by, direction, group, group by, by, show, list, display, give me, name, just, only, arrange, rank, compare, to, with, against, alphabetically, ascending, descending, order

Range: greater, more, larger, above, over, >, less, smaller, fewer, below, under, <, at least, no less than, >=, at most, no more than, <=, in, between, in the range of, from, later, earlier, sooner, after, on, at, later than, after, since, starting with, starting from, ending with

Times: am, pm, o’clock, noon, midnight, hour, minute, second, hh:mm:ss

Examples: 10 pm, 10:35 pm, 10:35:15 pm, 10 oclock, noon, midnight, hour, minute, second.

Top N (order, ranking): top, bottom, highest, lowest, first, last, next, earliest, newest, oldest, latest, most recent, next

Visual types: all visual types native to Power BI. If it’s an option in the Visualizations pane, then you can include it in your question. The exception to this is custom visuals that you’ve manually added to the Visualization pane.

Example: show districts by month and sales total as bar chart

Wh (relationship, qualified): when, where, which, who, whom, how many, how much, how many times, how often, how frequently, amount, number, quantity, how long, what


Power BI – Turning off (Disabling) Native Database Queries

In this quick blog post I am going to show how to turn off or disable having the dialog
box which pops
up when using Native Database Queries.

This can sometimes be an extra step on something that you have to action when refreshing your data either in the Query Editor or in Power BI Desktop.

NOTE: I was often prompted with the following below for EACH native database query.

As you can see below this is typically what would happen if I ran a native database query

  • I would first get the yellow car asking for permissions to run the native database query.
  • Then once I clicked Edit Permission I then got the following Window, showing me the query that is going to be run.
  • Once I click Run it would then run the query.
  • So this can be time consuming especially if I had multiple queries.

How to turn off or disable the Native Database Queries

In the steps below I will show how to easily turn (Disable) the native database queries

NOTE: I could complete the steps below from either being in the Query Editor or within Power BI Desktop.

  • Click on File and then Options and settings and then Options
  • Then under GLOBAL I clicked on Security
  • Then in the Security settings at the top it has got a section which says Native Database Queries you will see an option.
    • And there is a tick
      next to “Require user approval for new native database queries
    • I removed the tick.
  • Then click

Now I went back and ran my native database query and it simply
ran with no requests to Edit Permissions or to Run the query.


I have shown how to disable the native database queries which not only helps when I am developing but also assists me when my Power BI Desktop file is refreshing.

BI-RoundUp – Power BI (December Desktop Update – Larger Power BI Premium PBIX – Power BI Bookmarking Contest – Premium Capacity WhitePaper)

Even though it is almost the holiday season, the wonderful people from the Power BI team have given myself (and hopefully you also) some early birthday presents. So please find all the updates below.

Power BI – December Desktop Update

There was an incredible number of updates in the December edition of Power BI Desktop, so please find below all the details.


The first and biggest thing in the latest update was now the capability to be able to Ask a Question (Q&A) within Power BI Desktop, which relates to asking a question on a report page. Previously this could only be done in the Power BI Service on a dashboard.

I have used Ask a question (Q&A) in the Power BI Service previously and it works really well. But with this now being in Power BI Desktop, it enables business users to quickly create the visualizations that they are looking for. And then if need be they can customize them afterwards.

I also think that this will go a long way to facilitate self-service report creation, because it now means they can just ask a question and get a visual response.

Next is updates to Bookmarking:

  • The bookmark will now save your cross-highlighted state when you create your bookmark.
  • Along with this there is more flexibility with regards to what is bookmarked.
    • Data – This will apply properties relating to your data such as filters and slicers
    • Display – This lets you decide if you want to include Visual Properties of the display item
    • Current Page – This lets you apply the bookmark to the current page only, if deselected it will not apply to the current page you are in.
    • All Visuals – This means that the bookmark will bookmark all the visuals on the entire page.
    • Selected Visuals – This will only apply the bookmark to the selected visuals, which I would have selected before applying the bookmark.

Next is the Field Properties Pane and Field Properties Description, which now allows you to add in a description for your Field Names. This is great to document or make a note within your Power BI Desktop model. Especially if someone else is going to come along and edit the Power BI Desktop PBIX, they can now find additional details within the file.

There is now the capability to be able to create a scatter visual from the x & y-axis groupings. What this means is that you no longer need to add in a category to see the details. The only currently caveat is, is that you have to use a Numeric column it currently does not work on measures. But I am certain that this will change in the near future.

If you use maps, and have had a lot of data points in the past, the new algorithm has also been applied to maps. This is great because where they are dense, the human eye will not be able to see any difference. But more importantly it will show the outliers, which are often very important.

(I know it is really wide, but that is how far I had to drag it across in Power BI Desktop in order to get the entire name!)

Along with this, there are now responsive slicers, which is an update to the existing responsive visuals. This makes it easier to use especially when on touch screen’s or mobile phones.

There are a whole host of Custom Visuals, some I have seen before, but some really cool new ones.

  • Card Visual
  • Table Heatmap
  • Data Image
  • Power Matrix KPI
  • Text Filter
  • ChartAccent – Line Chart
  • ChartAccent – Bar Chart


In the analytics section there is now is the ability to drill filters to other visuals within your Power BI Desktop report.

What this means if you drill down in a visual it will apply the same filter to all the other visuals in your report. This is really fantastic, because I know personally that I often want to see the context of what I am drilling down to and how it relates with my other visuals.

Data Connectivity

Please find below new data connectors, as well as some updates to existing data connectors.

  • Adobe Analytics Connector
  • HDInsight Interactive Query connector
  • Data.World connector
  • SAP BW connector improvements – Ability to be able to connect to either the SAP Business Warehouse Application or Message Server.
    • As well as currently this improvement is only available in Power BI Desktop, and will be coming to the On-Premise Data Gateway soon.
  • IBM Netezza connector is now generally available

There is a lot of content to go through, so if you would like to watch the video or find more details here is the link to the blog post: Power BI Desktop December Feature Summary

Power BI – Larger PBIX sizes for Power BI Premium

As you can see above, available now in Power BI Premium, you can now upload PBIX files that are up to 10GB in size. I am sure that this is a great welcome for customers who have had larger PBIX files.

You can find the blog post details here: Announcing Power BI Premium support for larger datasets

Power BI – Bookmarking Contest

There is a Power BI Bookmarking contest where you could not only win some awesome Power BI Swag, but also be seen by the Power BI community as to how awesome your Bookmarking capabilities are.

More details can be found for the competition here: Bookmarking Contest: Dec. 12th – Dec. 27th

Power BI – Premium Capacity White Paper

Please find the link below to the newly released Power BI Premium White Paper

Power BI Premium White Paper

How to enable Excel Pro’s to use Power BI Datasets

In the steps below, I am going to demonstrate how to access your Power BI Dataset through Excel.

This is a great example for people who love Excel and use it as their tool of choice, but the company or organization still wants to have a single version of the data. By leveraging the steps below the data is available in the Power BI Service, as well as from Excel.

NOTE: This currently only works on a PC.

Installation Power BI Publisher for Excel

There are 2 ways to access data in the Power BI Service via Excel. I prefer the method below, because it allows for easier connectivity as well as not having to go and download ODC files and then store and open them each time.

By using the Power BI Publisher for Excel, people who use Excel will have another item in the ribbon in order to access Power BI data.

  • The first thing I needed to do was to install Power BI Publisher for Excel.
  • I chose the Download for Office 64-bit, because that is the installation that I have gotten installed.
  • Once the download was completed, I ensured that Excel was closed and completed the installation.
    • I simply accepted all the defaults until the installation was complete.
  • I then opened Excel and verified that I could now see Power BI in the ribbon

How to connect to a dataset

Next, I will demonstrate how to connect to a dataset.

  • I opened Excel and went to the Power BI Ribbon.
  • I then clicked on Profile and Sign In
  • I then signed in with my Organizational account
    • I verified that I was signed in by clicking on Profile again
  • Next, I clicked on Connect to Data
  • This then brought up all the reports and datasets in my own workspace, as well as other reports and datasets that had been shared with me.
  • As you can see below these are datasets that were my own
  • And if you had a dashboard shared with you, that also means that you have got access to the underlying reports and datasets.
  • In order to access datasets shared with you, make sure where it says Select a workspace: it says My Workspace
  • This is shown below with the Share Icon
  • Then click Connect
  • This will then connect and create the Pivot Tables to the dataset, as you can see with my example below.

Enabling Access to Dataset via Sharing or App Experience

There are two ways that I am currently aware of where you can grant access to the dataset.

  • The first is via Sharing a dashboard to a particular user.
  • The second is create an App from an App Workspace, and then the user gains access once they have got the App.

Completing either of the following above will ensure that when the user clicks on Connect to Data in the Power BI Ribbon they will then be able to see the dataset. As shown below where my user “Pro” can see the dataset that he has got access to via getting an App.

And now when in Excel and the user “Pro” clicks on the Connect to Data, ensures that it is set to “My Workspace”, as well as the data that user “Pro” is connecting to is the Dataset, user “Pro” can connect to the dataset called “Fourmoo Google Analytics”


As I have demonstrated how to connect to a Power BI Dataset using Excel, as well as how to grant access so that your users can gain access.

If there are any questions, or advice please leave it in the comments below.

Power BI Embedded InfoGraphic

There has recently been a lot of talk around Power BI Embedded for various reasons.

The first being that Power BI Embedded can be used to for Power BI Reporting requirements, where the users are viewing it from an internal application. This can work for existing applications where a business has data. As well as potentially as another avenue to limit costs of using Power BI.

The second is that there are now 3 different Power BI Embedded SKUs which can also create confusion with regards to which SKU is applicable to me in my organization.

So below is the Power BI Embedded Infographic that I have created. I do hope that this infographic will simplify the different versions for Power BI Embedded.

As well as give a clear indication, to show based on what you are trying to achieve, which option is best suited.

I did source this information from the following blog post from the Power BI Team: Power BI Developer community October update

You can also find the image in the following location: Power BI Embedded

As always, if there are any questions or there is anything that I have left out, or incorrect please let me know and I will happily assist or resolve any issues.

Nov 2017 Update to Power BI Overview Infographic

There has been quite a few changes and updates since I last reviewed my Power BI Overview Infographic. I thought it was time for an update, and here is what has been updated.

Please feel free to share this with other people new to Power BI, or people who want to get a more general feeling of all the moving parts in Power BI.

Here is a link to where you can find the Power BI Overview InfoGraphic as well as what the main page of the Infographic looks like.


The bookmark feature has been blogged about quite a lot, and it is a great edition to enable better story telling using Power BI.

You can find details about bookmarking here: Use bookmarks to share insights and build stories in Power BI (Preview)

Selection Pane

The selection pane allows you to control if an item in your report will be made visible or not visible (Invisible maybe!)

It is also a great way when there is a lot of items on your report page, to select the item from the Selection Pane, which will then have it selected for you.

You can also use to show or hide items from the Selection Pane as part of your Bookmarks, so you can make it appear as if items are moving or vanishing from your report.

The Power BI team has already made some improvements by allowing you to order the items in the Selection Pane.

You can find more details here: Power BI Desktop Oct 2017 – Selection Pane

Lock Objects

This new feature allows you to lock Objects so that they will not move. This is great when you are showing people what you have been doing and are in edit mode.

As well as great for presentations when you do not want anything to move.

You can find more information here: Power BI Desktop Nov 2017 – Lock Objects


If there are any items that are not included in my Infographic or are incorrect please let me know in the comments section below.

BI-RoundUp – Power BI (Nov 2017 Desktop Update – Single Sign-On for Gateway Data Sources)

It is the start of the second week, and that meant that there was an update for Power BI Desktop, as well as some of the other related Services. I do hope you enjoy this weeks updates.

Power BI – Nov 2017 Desktop Update

As you can see we now have the Power BI Desktop update for November 2017. And as I do every month, I will go through what I feel are the highlights up the recent update.


There is a lot to highlight in this section so below are some of the details.

  • All below relates to both table and matrix.
    • Cell Alignment, which as you can see above you can align them left, right, center and Auto
    • Rule based Conditional formatting, which you can also see in the image above.
  • Next there is the option to control the ordering of your images that you have in your selection pane, which is a great improvement.
  • Now you can also lock the objects in your report, so if you are doing a demo or showing someone the report, none of your objects will move.
  • ESRI Plus subscription available for ArcGIS. This allows you to use your ESRI subscription within the Power BI Service, with regards to more geocodes, additional base maps, living Atlas reference layers enabling you to add more context. As well as world demographics with the Infographics features.
  • Query Reduction has now been implemented for Live Connections or DirectQuery.
    • What this will do is for example when you are using slicers it will wait until you have made your entire selection, and once you click Apply will it then send one query. In the past it used to send one query every time you clicked on a slicer.
  • Filtering has also been improved so that there is no longer a 500 value limit.
  • There have been 4 new Custom Visuals added.
    • Image Timeline – This allows you to display events along a timeline with images. And then you can interact with them.
    • Social Network Graph – Which will show visual connections between people.
    • Venn Diagram – to find commonality between different categories
    • HTML Viewer – Lets you display HTML text strings, which enables your text to look a lot better


There is now support for Cell-Level formatting for Multi-Dimensional Analysis Services to Multi-Row Cards this month.

Data Connectivity

There is now support for Windows Authentication for the Impala connector

Query Editing

There have been even more updates for the Columns from Examples.

The additional items below are:

  • Basic Conditional Column – Define a mapping between values in an input column and the desired output
  • Conditional column ranges – Define a new column with non-uniform ranges (I am thinking that they mean different length requirements)
  • Null fallback – This is where you could have multiple columns and you want to extract the values that are NOT null
  • Bucketing (Uniform Ranges) – Define an upper/lower boundaries for a range for a certain row and it will try and copy the uniform ranges

You can find all the details as well as the video here: Power BI Desktop November Feature Summary

Power BI – Single Sign-On for Gateway Data Sources

There is now single Sign-On for selected data sources that use DirectQuery via the On-Premise Data Gateway.

Currently the supported data sources are:

  • SQL Server
  • Teradata

And there will be future support for:

  • Oracle
  • Impala
  • SAP BW
  • Spark
  • More to follow

You can find all the details here: Announcing Single Sign-On Support when connecting to data sources from the Power BI Service

Overview of what is coming to Power BI in the next few months!

With all the recent updates and announcements from SQL Pass, as well as the Power BI World Tour.

And there is a lot of great things to look forward to coming in the next few months!

Please understand that some of the pictures I have got from Twitter or elsewhere, so whilst they are not the greatest image quality, but rather showing what is coming. I am really excited to start using the new capabilities once they become available.

What is coming to Power BI Desktop

  • It is great to see that there will be enhanced enterprise readiness in terms of higher data points, which has been seen with the scatter plots.
  • As well as additional report sharing functionality.

  • There is also going to be a whole host of new connectivity to Adobe Analytics, Hive.
  • It is also great to see that there is Azure Active Directory Authentication coming to Azure SQL DB, Azure SQL Data Warehouse.
  • As well as even more enhancements with Columns from Examples

  • Next in the Visuals & reports, FINALLY to have slicers working across multiple pages.
  • Drill filters to other visuals in the report will also get filtered based on what you drilled down to.
  • Theming improvements, which I know a lot of people are starting to use.
  • And finally adding in more conditional formatting rules.
    • And here is an example below of what it might look like.
    • Thing to note is the rules are applied top to bottom.
    • So if you wanted to have a specific colour for a single value, then put it at the bottom and you will get it because it gets evaluated last.
    • There is also the ability coming to add column description information, which is great for end users to easily understand what the column is being used for.

  • And finally, Q&A is coming to Power BI Desktop, which means that you can use it on your report pages in Power BI Desktop and leverage Q&A. It is an easy way to create some visuals for your data.
    • You can easily double click and ask a question to your data.
    • Apparently you can use Q&A for TimeLine type of questions such as This Week, or This Month, etc and the charts should automatically move over time.
  • One thing that really interests me is the Query Reduction options, and how that can improve the query performance or loading of data, this is for Live Connection or DirectQuery sources.

Additional Power BI Updates for the Roadmap

Unfortunately, I did not have the time to make it all look pretty and together, so here is another list of roadmap items that are coming in the next few months.

Power BI Desktop

  • There will be additional Quick Insights added. Interested to see what will happen here!
  • Cross highlighting across multiple visuals which I am looking forward to, so that I can now select multiple items and see what story the data is telling me. Or find some amazing new insights within my data.
  • Updates to Bookmarks, which will give you more control over what is being booked marked for each individual bookmark.
    • This could potentially apply to the data, page etc.
    • This could also potentially decide which visual will be affected by a particular bookmark.
    • There is a long time plan to get Bookmarking to all the different flavours of Power BI, being the Web Service, Publish to Web and Power BI Embedded.

Power BI Service

  • The ability to Share datasets, I am not sure how this will work, but once again it will be interesting to see how this will work, and potentially will be great for larger customers.
  • I think that the Clustered capacity might be for the On-Premise Data Gateway, which I talk about below.
  • And finally, the capability to export to PDF, which I know is going to be a very welcome addition for people who want to have a hard copy of a Power BI report.

Power BI Report Server

  • The ability to bring in multiple data sources and refresh them is now available.
  • Adding additional items to the REST API.
  • And having additional SharePoint Integration, which in larger organizations is great, because it means that they can leverage their existing SharePoint investments.

App Updates

  • This is something that I personally know some organizations have been struggling with is the Office 365 groups when creating App Workspaces, and finally this is going to be removed.
  • Pushing the app to the users is great, because sometimes you want to give it to the users, so that they do not have to go and search for their data or are unaware of the analytics available to them.
  • Being able to selectively publish content from App Workspace is something people have been asking for a long time, and one of the main reasons people have not moved from Organizational content packs.
  • Being able to copy between content packs, which will enable the development lifecycle to work more seamlessly from Dev, to Test to Prod.
  • And finally, another big one is the ability to be able to share your Apps externally with external clients. And if you leverage this with Row Level Security you can now let your customers view their data.

On-Premise Data Gateway Updates

Once again there are a whole host of updates coming for the On-Premise Data Gateway

  • There is now Single Sign On (SSO) for Direct Query for SQL Server Database, Teradata & SAP HANA
  • They are looking to enable SSO to Oracle, Impala & SAP BW before the end of 2017
  • OAuth support for data mashup scenarios, which means using the Query Editor to import data from multiple sources into one dataset without any Mashup (or Applied Steps)
    • And coming soon will be including using the Query Editor to import data from multiple sources with steps being applied to the data.
  • Once again, another great feature that enterprise organizations as well as smaller organizations is the ability to have High Availability and basic load balancing.
    • This will now mean that you can have the data refreshing always available.
    • And I am interested to see how the Basic Load Balancing will work, which will be awesome when multiple datasets are refreshing at once.
  • And finally, is the use of Custom Connectors within the On-Premise gateway, so that once you have created your own custom connector it now can be refreshed via the Gateway.

Power BI – Client Advisory Team

Coming soon to Power BI is a Client Advisory Team (CAT), which Adam Saxton is going to be a part of. This will be great for strategic Power BI Customers on how to ensure that they are able to leverage Power BI.