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 (June Desktop Update – Data Insights Summit Details – Power BI Embedded Pricing & Details – Power BI Premium Generally Available – Power BI Report Server Generally Available)

I was fairly certain that there were going to be a whole host of updates and additions to Power BI, and I can say that I am not disappointed. There is a whole host of information below, where I will endeavour to try and get it all to you, so this might be a bit long to read, but it will be worth it.

Power BI – June Desktop Update

First off is another big update to Power BI Desktop and as I do every month I will highlight the new features.

Report View

As with the image above you now can add in data bars for tables and matrixes, this is really handy and as they are continuing to do, is to add in Excel like functionality into Power BI

Next is the ability to add in Line Markers for your charts. Which can make it visually easier to see, but as they mention also easier for color blind people. A thing to note is that currently (June 2017) it is limited to Categorical Axis, and the Continuous Axis will be coming in a later release.

You can now also change the fonts in the formatting pane for your charts in the following area’s below (The rest of the area’s will be added in future releases)

  • Axis Labels
  • Axis Titles
  • Legend Values
  • Data labels
  • Every section of the new Table and matrix visuals
  • Slicer header and items

They have also now enabled the ability to use a Horizontal Image slicer to your report. This makes it easier and more flexible so that you can now use this in your report as required.

You can now use the Combo chart to align the zeros in your data, as well as shading so that it all aligns to show the data as accurately as possible.

It is nice to see that you now also have got some updates to the Bing maps being used in Power BI, as well as they are going to leverage the new Bing API which is going to be released at the end of June.

Also in this update there is now a new algorithm for high density line sampling, I know in the past that Power BI has been called out that it cannot sample enough data points, so now the Power BI team has updated the algorithm so that it now will take each point, and put it into bins, and then takes the min and max which it will then plot into the data. This ensures that you will not only get a more accurate visual representation of your data. But you will also get to see the outliers.

Note that the new High Density Sampling option is enabled by default.

The last part for the reporting update is that there now is the ability to be able to create Accessible Reports. They are starting with using Keyboard navigation and as well as you can now add Alt Text to all the visuals, so that when people who require accessibility options there will be some additional text for them to understand what the chart is telling them. The Alt Text will be read out by the accessibility options.

Data Connectivity

With the previous months release of custom data connectors. They have now enabled it as a preview feature so that it can now appear under the Get Data when you open Power BI Desktop.

They have also released the following new data connectors

  • Power BI service Live Connect to On Premises and Push streaming datasets
  • Impala connector is now generally available
  • Amazon Redshift connector is now generally available
  • SAP BW connector – DirectQuery support (beta)
  • IBM Netezza connector (beta)

Query Editing

They have updated the “Add Columns from Examples” so that it now is giving you multiple suggestions for what you are trying to achieve. I personally think that this in incredible because it now means you can choose which one you want to use when you can have more than one outcome.

You can find the blog post from Microsoft here: Power BI Desktop June Feature Summary

Data Insights Summit Details

With the Microsoft Data Insights Summit that is currently happening in Seattle the Power BI team has been really busy letting us know what features are coming and what to get excited about in the coming months, so below is a quick overview of what has been released.

What is coming

As you can see below there are a whole host of details around what is now available and what will be completed in the next quarter.

Drill through Pages

This is one of the announcements that have created quite a bit of excitement, in that very soon you will have the capability to be able to drill through to other pages. But not only that when you do drill through it will remember what you selected or filtered and use this on your drill through page. So now you can drill through, as well as go back to your previous page.

I personally have been asked about this before and I know that it is something that people have been waiting for. As well as this can make the entire reporting experience a lot better and interactive.

What-If Analysis

This is a really amazing feature in that you can quickly and easily create a What-If scenario and then use this within your data to see how it will be affected.


Another new feature which will be released soon is the capability to create bookmarks. You can then use the bookmarks to do story telling with your data. And you can enhance the bookmarks that you have saved, so that you can make things pop out or highlight certain aspects.

What I really am excited about is that you can still interact with your data in your bookmark. So it is totally interactive, and as they say this is the Power Point for Data Insights.

M Language

As you can see below M language is going to have more integration with Office products, as well as versioning & dependency tracking for extensions which appears to be to have version control!

Power BI Desktop Version Control

You can use OneDrive for Business to ensure that you have got version control for your Power BI Desktop files

Organizational Folders

It appears that you will now be able to organize your folders as you could previously do with SSAS OLAP

Power BI Write back with Power Apps and Visio Custom Visual

This is a big one, where they have now used Power Apps in order to facilitate a write back of your data into your Existing Power BI Model. This is something that people have been asking for, for a long time.

As well as now you can also integrate Visio visuals into your Power BI report, and if you create your Visio visuals with ID’s you can map these to your data so that they will interact as you can currently do with all other visuals within your report canvas.

Azure Analysis Web Designer

There is a preview coming where you can now design and tune Azure Analysis Services from within your Web Browser.

The things to take a note of is that you can import Power BI Desktop files which will be converted into Azure Analysis Services, which means you can create a proof of concept locally and then enable it to be used for the enterprise.

As well as then integrate these datasets with Power BI, Excel, Visual Studio and GitHub, so if you are looking for more source control around your development this might be the option going forward.

Power BI Embedded Pricing & Explanations

There now is a white paper in which they explain how to leverage Power BI Embedded with Power BI Premium. As well as also detail what the pricing is and also explain how page renders work, which is important for when deciding which size to go for.

Here is what the pricing points are below.

You can download the White paper from the following location: Plan capacity for embedded analytics with Power BI Premium

Power BI – Premium Generally Available

Power BI Premium is now generally available, so organizations who are looking to leverage the capacity or to significantly save on licensing costs can now purchase Power BI Premium.

You can get more details here: Power BI Premium

Here is also some additional documentation around Power BI Premium:

Power BI – Power BI Report Server Generally Available

As you can see from above, which they also announced at the Microsoft Data Insights Summit, Power BI Report Server is now Generally Available. This is great news for people who are going to purchase Power BI Premium, or they have the correct SQL Server Licensing model to install this.

You can find the details here: Power BI Report Server now generally available

BI-RoundUp – Power BI (Service Update April & May – Personal Gateway Update – How to Purchase Power BI Premium – Managing Power BI Premium – Memory Consumption in Power BI File – Power BI – Exposing M Code)

It has been a busy week with the start of another month leading into June, so here are the updates. I am pretty confident that with the Microsoft Data Insights Summit happening Monday & Tuesday of next week that the new version of Power BI Desktop, as well as some new features will be revealed then. And I cannot wait, I always look forward to the start of each and every month.

Power BI – Service Update April & May

Due to this update being for almost 2 months there have been quite a lot of updates in the service, which I will highlight the significant ones below.

Already announced and available in the Power BI Service, is now the ability to be able to view usage metrics for your dashboards and reports.

The biggest update is now the capability to be able to pass multiple URL parameters as a filter.

I know that this is something I have personally been asked in the past and will be very welcome.

Also there is now support for PostgreSQL, the Power BI SharePoint Web Part is available for All Users, the Data connectivity SDK, New Service Content Packs as well as Power BI Apps Public Preview.

You can find out all the details here: Power BI Service April and May Feature Summary

Power BI – Personal Gateway Update

There has been a new release for the Personal Gateway, which has now got the following new features.

  • Delete a personal gateway from the Power BI Service
  • Improved performance
  • Configuration and service logs

The personal gateway appears to be moving towards a lot of the functionality that there currently is in the On-Premise Gateway, which is great because it will now mean that it is reliable, as well as having troubleshooting tools, which I feel is essential when there are issues, and can help quickly find the problem.

You can find the details here: On-premises data gateway (personal mode)

Power BI – How to purchase Power BI Premium

Even though Power BI Premium is not officially been released yet, there is already content out there in terms of how to go about purchasing Power BI Premium when it becomes available.

You can find all the related details here: How to purchase Power BI Premium

Power BI – Managing Power BI Premium

This is another blog post, where Adam Saxton explains and goes into details around how to manage Power BI Premium.

There is some great content and one of the things that I am looking forward to is the ability to be able to assign specific users to the Premium capacity. As well as even if you wanted to only assign specific App Workspaces to the Premium capacity. I think that this will also make it really flexible for different options within your organization.

You can read up about it here: Manage Power BI Premium

Power BI – Memory Consumption in your Power BI Desktop File

This is a fantastic blog post by Imke Feldmann where she shows how to leverage the internal DMV’s within Power BI Desktop to reveal the memory consumption of your Power BI Desktop File.

One thing to note is that I think you can only have one Power BI Desktop file open in order to get the DMV’s. This is because of how in her code, she goes and looks for the Port ID, and if you have more than one file open it will not be able to know which Port ID to use.

I have emailed Imke Feldmann to confirm that you should only have one Power BI Desktop file open at a time.

In her blog post she explains how to get this data, by downloading the Query Editor M code and then pasting it into a table (which you create by using Get Data, then Blank Query)

Her blog post details are here: Analyse your memory consumption in PowerBI

Power BI – Exposing M Code

This is another really interesting blog from Reza Rad, where he leverages off one of my existing blog posts (What makes up a Power BI Desktop PBIX File) and goes into more details around the DataMashup file.

I honestly did not know that it was also a ZIP file, so I learnt a great deal from this blog post, to understand how the DataMashup file works. But this will also enable me to be able to go and possibly use the M code from within the Query Editor into other Power BI Desktop files where I need an exact copy of the M Code.

You can find Reza’s blog post here: Exposing M Code and Query Metadata of Power BI (PBIX) File

DAX – Getting the Previous Year to Date Value up to and including the Current Month Selected – Not the entire Previous Year

So for this example it is best explained with an image below, then some additional context afterwards

  • If you look above we have got the [Sum of Sales] for Dec 2010-11
    • NOTE: This I selected in the Slicer on the right hand side.
  • Then I have created a Year to Date (YTD) measure which is the running total for the Year
    • NOTE: This is the measure below.

      YTD-MTD-CY = TOTALYTD([Sum of Sales],’Date'[Calendar Date],”06/30″)

  • As you can see for Dec 2010-11 with the measure [YTD-MTD-CY] I have the Year to date value.

Now the requirement was if the user selected any Fiscal Month (EG: Feb) from the Slicer, they are looking for the Feb 2009-10 value for the Previous Year (Feb 2009-10). So once again if I show this in a picture this is the value that I am looking to get.

DAX Measures

Below are the DAX measures that I used with an explanation afterwards.

  • DAX Measure
    YTD-MTD-CY =
    TOTALYTD ( [Sum of Sales], 'Date'[Calendar Date], "06/30" )

    • What this measure is doing, is getting the YTD total for the current Fiscal Year for the [Sum of Total Liability.]
  • DAX Measure
      PREVIOUSYEAR ( 'Date'[Calendar Date], "06/30" ),
     'Date'[Fiscal Month] = VALUES ( 'Date'[Fiscal Month] )

    • This is the DAX measure that does all the work required to get the result that I wanted.
    • The first thing is that I used the previous measure [YTD-MTD-CY]
    • Next in the filters, the first filter that I used was to go back to the PREVIOUSYEAR highlighted in BLUE
      • Again with the PREVIOUSYEAR I am ensuring that it goes back 1 year, based on the Fiscal End Month.
      • NOTE: So in the current context it will now be going back the entire Previous Year, and not stopping at the month selected.
    • The final filter that I added is where I am filtering the Previous Years values, to stop at the value selected based on what was selected on the slicer, highlighted in ORANGE.
      • So when a value is selected on the Slicer it will return this value to the filter.
      • And then instead of the values going to the end of that current fiscal year, it will stop at the Fiscal Month Selected.
  • DAX Measure
    ZCALC - YTD-MTD-PY Blank =
      PREVIOUSYEAR ( 'Date'[Calendar Date], "06/30" ),
      'Date'[Fiscal Month] = "Jun"

    • This measure that I created above, is so that if nothing is selected on the Fiscal Month Slicer, then return the last month of the Fiscal Year which is Jun.
    • The measure is identical to the one above, but I just hardcoded the Fiscal Month Value.
  • DAX Measure
    YTD-MTD-PY =
    IF (
      HASONEVALUE ( 'Date'[Fiscal Month] ),
      [ZCALC - YTD-MTD-PY],
      [ZCALC - YTD-MTD-PY Blank]

    • This is the final measure, where I evaluate if a value is selected on the Slicer (TRUE), then use the measure [ZCALC – YTD-MTD-PY]
    • If nothing is selected (FALSE) then use the measure [ZCALC – YTD-MTD-PY-BLANK]


So now I have the measure that I need with the correct results showing below.

  • As you can see above Aug was selected in the Fiscal Month Slicer.
  • So when you view the top table, you can see that the YTD value is $491,100,640 for Aug 2010-11
  • Then when you look at the bottom table you can see that for the measure
    [YTD-MTD-CY] is shown correctly for 2010-11
  • And then when you look at the bottom table for the measure
    [YTD-MTD-PY], this is showing the Previous Years
    value on the row for 2011-12


So as you can see above, when you change the value in the Fiscal Month Slicer, it will then correctly update the measure to show up to and including the Fiscal Month
selected for the Previous Year.

And here is what it looked like when completed when I selected Nov

And then once again when I selected Mar

BI-NSIGHT – Power BI (Premium, May Desktop Update, New Pricing 01 Jun 2017, Pride 2017, Azure Consumption Insights Content Pack)

I always anticipate that the start of each month will be busy with the updates to Power BI, but this month is an exception with not only the release of the May Power BI Desktop Update, but also a new pricing and capacity model with regards to Power BI Premium. So let’s get into it shall we.

Power BI – Power BI Premium

With the update to now having Power BI Premium coming on 01 June 2017, this means that there is a whole host of changes, updates and additions that have happened with regards to the Power BI Service.

I am going to highlight below what I think is most relevant, and provide links further below if you want to go into more details.

Power BI Premium Overview

What Power BI Premium is in one sentence is the capability to license for capacity instead of per user.

What this means that in the past if you had 5000 users you would have to pay and license each user. With the Premium model you now can pay for a dedicated capacity and have 5000 users for a much lower price, or even lower depending on your specific requirements. What this means is that you now can get a more cost effective, as well as dedicated offering from Microsoft Power BI.

Below is a simple Infographic in terms of what will be available at Launch and what is planned in the future.

Here is the Cost Calculator that you can use to determine which pricing model will be the most cost effective.

Power BI Premium calculator

Power BI Report Server

There is also the option to install Power BI Report Server on premise so that you can leverage Power BI on your own premises when there is the requirement. You can view the link here for more details.

Power BI Report Server

Power BI Apps

There is also an announcement that they have evolved the content packs to now be called Power BI Apps.

With the advent of Apps you now can also create and develop them and add users and admins at a later time.

Along with this there will no longer be the requirement to leverage Office 365 Groups. Which makes it less reliant on Exchange for Office 365.

When you create an app you will also have the capability to be able to push it not only in the web but also to iOS, Android or Windows devices.

They have also outlined in their blog post the roadmap going forward.

And here is the link to their blog post: Distribute to large audiences with Power BI apps

One final thing to note is that the licensing for the Free model has added additional capabilities but the one BIG takeaway is that you will NO longer be able to Share your data with other users. As an initial workaround you can sign up for the Pro trial which will give you the required sharing functionality.

You can find the blog post details here: Microsoft accelerates modern BI adoption with Power BI Premium

Power BI – May Desktop Update

Along with all the other announcements there also has been the May Power BI Desktop Update. And below I will highlight some of the changes.

Report View

The relative date slicers is a very welcome addition as I have had instances in the past where I have wanted to keep data relative and it did take some creative thinking to achieve this. Which now can very easily be done with the relative date slicer.

It also good to see that they have ported over the functionality from the Matrix Preview into the Table Preview. Having very similar capabilities and a very welcome update.

The combo chart label enhancements are another small but important update, having worked with combo charts in the past, it makes it a lot easier to see the data label information.

Also having additional URL support is great as you can see above, as often it is not only web links that need to be linked.


The biggest update in the May release is the capability to be able to create Report Level measures for Live connections to SSAS (Analysis Services Tabular) and Power BI Service datasets.

This is something that the community has been asking for a long time, and enables the report developers to further enrich the report model.

They have also added to new Quick Measures to make the total count of Quick Measures now 21

Data Connectivity

A new connector for Dynamics 365 for Customer Insights.

As well as the option when importing data from a folder to select the First File (So that you do not have to select it every time). But you still have the option to select the file from the drop down if required.

Query Editor

They have added 3 new transformations for Text Before, After or Between delimiter. So this can make it easier to get data that is in a specific delimiter format.

The final update is the ability to Unpivot Only Select Columns, what this does is it will ONLY unpivot the columns that you have selected. Even if more columns come into your table, it will only unpivot the columns you had selected initially.

You can find the entire blog post details here: Power BI Desktop May Feature Summary

Power BI – New Pricing June 2017

Below is the new pricing model for the different levels in Power BI from 01 June 2017

Power BI – Pride 2017

With this years Microsoft Data Insights Summit happening really soon, there is a call for people from the Power BI Community to show their pride with Power BI.

As you can see from the image above, those are the top 10 features, as voted by the community that they want you to use in your pride video.

You can find the blog post details here: Power BI Pride 2017: share your pride with the world!

Power BI – Azure Consumption Insights Content Pack

There is a new content pack available in which you can now use to view your Azure Consumption.

You can find more details here: New Power BI content pack for Azure Enterprise users

BI-NSIGHT – Power BI (Community Blog Highlights – Power BI Germany – Latest Updates for Power BI Desktop – How Microsoft uses Power BI to run its Cloud Business – Connecting Datasets to Power BI Service) – SQL Saturday Brisbane (I’m presenting) – SQL Server (SQL Server 2017 – DAX Editor for SSDT – New Get Data Experience for SSDT)

I thought that this might be a quieter week, but once again there are a whole host of updates from Power BI updates, to SQL Server 2017 announcements. Quite a bit to read through so I hope you enjoy it.

Power BI – Community Blog Highlights

I actively participate in the Power BI Community, as well as when I feel like I have something meaningful that will also contribute to the Power BI Community get it published onto the Power BI Community Blog. And in this month’s highlights I am thrilled to have my latest blog post as part of the highlight.

The other posts are below:

Power BI – Infographic for Power BI from an end to end perspective, by Gilbert Quevauvilliers

Design Pattern – Groups and Super Groups!, by Greg Deckler

Get Your Own Power Query Editor using Notepad++, by Lars Schreiber

What I Learnt Building Power BI Custom Visuals, by Chamara Ranasinghe

A Simple and Fun Guide to Microsoft Flow and Power BI, by Ruth Pozuelo

The Ultimate Waterfall Chart , by Klaus Birringer

Power BI – Germany

I know from personal experience that having your data reside in the same country you are working from means a much easier and broader adoption of Power BI. Now for people living in Germany they are able to fully comply with all the requirements in terms of having data hosted within Power BI.

As a side note I also saw that Microsoft is the first company to offer a data centre in South Korea which is great to see.

You can find the blog post details here: Experience your data with Power BI Germany and meet your compliance and regulatory needs

Power BI – Latest Updates for Power BI Desktop

I recently came across this link below, which always will point to the latest details with regards to the Power BI Desktop.

I would suggest bookmarking this page, to ensure you always have the latest information available.


Power BI – How Microsoft uses Power BI to run its Cloud Business

This is a really interesting blog post where they details how Microsoft uses Power BI to monitor Power BI as well as all the other BI components that fall under James Phillips. What amazes me is that for each of the KPIs shown in the screen there is a General Manager responsible for that particular product.

What this also shows is how to build an effective KPI dashboard which is easy to view and know when something is not going as it should.

You can find the interesting read here: How Microsoft uses Power BI to run its growing cloud business

Power BI – Connecting to the dataset in the Power BI Service

In this blog post from the Power BI team they go into some details around how and why you would want to use the Power BI Dataset in the Power BI Service.

One thing that I found interesting and I guess I had never thought of is that you can now connect to ANY dataset that has been published to the Power BI Service. This includes datasets from Curated Content Packs which makes the development experience that much easier.

You can find the details here: Connecting to datasets in the Power BI service from Desktop

SQL Saturday Brisbane – I’m Presenting on Power BI Real World Tips and Tricks

I am so excited that I will presenting for the first time at SQL Saturday Brisbane on Saturday 27 May 2017.

The session details are Power BI Real world tips and tricks. Below is the session excerpt:

Out in the real world, people are starting to use Power BI to gain insights into their data that were previously never seen. With Gilbert currently working full time in Power BI projects, he has first hand experience on how to leverage Power BI in work place.

By providing real world, real working examples I plan to show how some simple tips and tricks can make their reports and dashboards amazing and insightful. This will be an interactive and demo rich presentation.

The real world tips will cover the following sections of Power BI:

  • Query Editor
  • DAX
  • Visuals
  • Power BI Service

You can register for SQL Saturday here: SQL Saturday Brisbane – 27 May 2017

SQL Server – SQL Server 2017

I personally think that with the release date of SQL Server 2017, that Microsoft is looking to continue what it has been doing with the combination of experience with Power BI and deploying updates to Azure SQL Server.

This is allowing them to deploy and implement changes to SQL Server in at a faster pace. I do also personally think that with the deployment and testing with Azure, they can test real world scenario’s with a diverse environment to make sure that it works as expected. This allows them to then take all these learnings and put it into an On-Premise Solution.

You can find the details here: SQL Server 2017

SQL Server – DAX Editor for SSDT

It was just a matter of time before the DAX editor became part of SSDT (SQL Server Data Tools) and now it is finally here.

This will no doubt make the creation and editing of measures and calculated columns a lot easier and quicker to develop.

They are planning to have Intellisense as well as Code Formatting in future releases

You can find the details here: Introducing a DAX Editor Tool Window for SSDT Tabular

SQL Server – What’s new in SQL Server 2017 for Analysis Services

As you can see above there have been a whole host of enhancements for Analysis Services in SQL Server 2017.

The new Object-Level Security to secure more data looks really interesting in that you can now also secure it on a column level. As well as performance improvements for the developer experience, which I personally know in the past at times was particularly slow.

Along with improvements to the DMV’s, similar Date Hierarchy implementations that are currently in Power BI.

As well as other improvements you can read about here: What’s new in SQL Server 2017 CTP 2.0 for Analysis Services

Server – New Get Data Experience

With the new release of SSDT they have updated the Get Data Experience for SSAS models with level 1400.

It appears that they are looking to align with what is currently being implemented in Power BI, which is fantastic to see because it will mean a more consistent developer experience.

They are continuing to add more data sources with each release.

You can find all the details here: New Get Data Capabilities in the GA Release of SSDT Tabular 17.0 (April 2017)

BI-NSIGHT – Power BI (Service March Update – New Navigation Experience, Desktop Terms & Definitions – Whitepapers) – BI Survey (BARC BI Survey 2017) – Excel – (Get & Transform Updates for April 2017)

There has been a variety of updates and blog posts in the past week, so here are my BI updates.

Power BI – Service March Update

There were quite a few service updates for March within the Power BI Service.

The first was the granular controls which was a very welcome feature. And I know that something that a lot of companies have been asking for. (As well as where I am currently consulting)

Another great new feature was the capability to view related content. This gives the user the ability to see how their content is related, as well as to quickly go into additional items from the view related content.

There was also an improvement for the troubleshooting for DAX queries, which helps with the diagnosis of errors or issues.

Then there was the custom scheduled cache refresh which is really handy in terms of how to handle this for DirectQuery sources.

As well as the ability for Amazon Redshift, and finally the move of the Custom Visuals into the Office Store.

You can find the blog post here: Power BI Service March Feature Summary

Power BI – New Navigation Experience

I have enabled the preview features on Power BI for quite some time, and I have to say with each iteration it has gotten easier and better. The new navigation experience is awesome. And makes interacting with Power BI in the service a lot easier. This is especially true when you start to have multiple workspaces, compounded with multiple dashboards, reports, workbooks and datasets.

This new navigation experience makes it a lot simpler to use and navigate.

You can find the blog post details here: Get ready for the new Power BI navigation experience

Power BI – Desktop Terms & Definitions

There is a really insightful blog post by Sam Lester from Microsoft where he goes into the terms and definitions when using Power BI Desktop.

If someone new is starting out in Power BI and using the Power BI Desktop this is a great place to start.

You can find Sam’s blog post here: Power BI Desktop – Terms and Definitions

Power BI – Whitepapers

There is now a central location for all the related whitepapers for Power BI.

This will be really handy because it will be easy to reference this for customers and clients going forward.

As well as I enjoy reading and learning from released whitepapers, so this means that I now have a central location for some great reading material.

Here is the link to where you can find the whitepapers: Whitepapers for Power BI

BI Survey

Every year I have been working in the Business Intelligence space I have been participating in the BARC BI Survey, and due to being a participant I get a copy of the results once completed. I find this invaluable to see what is happening in the BI space.

If you are interested please follow the link to complete the survey: BARC BI SURVEY 2017

Excel – Get & Transform Updates for April 2017

It is great to see the changes and updates that are made to Power BI filter down into the Excel Get & Transform.

In the April update they have included the following updates:

  • Support for the same file extensions in Text and CSV connectors.
  • ODBC and OLE DB connectors—support for Select Related Tables
  • Enhanced Folder connector—support for Combine Binaries from the Data Preview dialog
  • New Change Type Using Locale option in Column Type drop-down menu inside Query Editor
  • New Insert Step After option in the Steps pane inside Query Editor

You can read up about it here: April 2017 updates for Get & Transform in Excel 2016 and the Power Query add-in

BI-NSIGHT – Power BI (Desktop Update April – Quick Measures Preview – Mobile Apps Summary – Video Gallery & Ask a Partner Anything – View Related Content Pane – Sneak Peak add new Columns from Example – Tracking Adoption via Power BI Audit Logs)

There has been a whole host of updates and interesting things in Business Intelligence in the past weeks, so please find below the updates.

Power BI – Desktop Update April

This month’s Power BI Desktop update for April has a whole host of great updates and new features this month, which I will highlight what I think is most relevant below.

Report View

The first update in the Report View section is the ability to rename the axis titles. I know that personally this is something that I have been looking for and now the capability is there.

Next is additional updates to the Matrix Preview, in which you can now sort the matrix by the grand total and row headers in ascending or descending order.

As well as now you can also resize the columns as you could do previously in the original matrix.

And finally, you can now word wrap column headers, row headers and values in the matrix preview. This can be configured separately in the formatting pane.


Not only has the Power BI team made it better and easier to create the default measures. But with the Quick measures they have made it as simple as a drag and drop and then Power BI Desktop writes the DAX for you. I personally think that is a giant leap forward as it enables users who are not very strong in DAX to still be able to create powerful measures, which in turn will create greater insights into their data. This will also then let people learn how DAX works because they can see and modify the code, as well as give more advanced DAX users the capability to edit the DAX measure if so desired.

This entire preview feature I think is another game changer and it will enable people to quickly create the measures that they require, as well as to get people to build their DAX skills if so required. And I have no doubt that more quick measures will be added.

As you can see above, you can now try Q&A in Spanish. This must be the result of the Power BI Surveys that have been conducted in the past.

Data Connectivity

Once again this is another idea that a lot of people have been asking for and it has already been delivered. This is the ability to connect to a dataset that exists in the Power BI Service.

This is wonderful as what it means is that as long as you have got the required access, you are now able to access a dataset already uploaded into the Power BI Service. So what this also means is that you will not have to worry about creating measures, how the data is updated etc, because you are a consumer of the Power BI dataset.

This also means that now you can have multiple people working on a single dataset. Which is the start of having multiple developers developing reports and insights into your data.

The one caveat is that when you use the Power BI Service dataset you cannot edit or modify anything within this dataset, you will have to get the dataset owner to make the required changes and then for them to upload it the changes or updates into the Power BI Service. But still a giant leap forward.

The Amazon Redshift data connector has now moved out of Preview and is in the Beta phase, so it is easier to find and use.

There has also been updates to the SAP Hana and BW connectors, now giving you the ability to have more control with regards to the parameters selection. I am sure that people that use SAP will find this very welcome.

Query Editing

The ability to add a column by example has been out for almost 2 weeks now, but this is once again (yes I am repeating myself a bit!) a game changer because it now allows people who do not understand Power Query or the M language to quickly and easily build new columns within their dataset. And this also does it in the Query Editor, where I personally think it is the right place for this to be done, so that before it is brought into the Power BI Desktop Model, the data is already there and can achieve better results.

As you can now see above you now can split your column by delimiter or number of rows and if you wanted to split it into Rows.

The basic Group By in the Query Editor now allows you to group by a single column and output by a single column.

The Go to Column is a very handy feature because it allows you to quickly find your column, which is especially helpful when your table is very wide with a lot of columns.

You can find the blog post for the Power BI Desktop Update here: Power BI Desktop April Feature Summary

Power BI – Quick Measures Preview

There is already a blog post out by the Power BI team with regards on how to leverage and use the Quick Measures Preview.

It has some great content and is well worth the read.

You can find the blog details here: Quick Measures Preview

Power BI – Mobile Apps Summary

There have been some additional updates to the Mobile Apps for Power BI which are the Q&A Improvements and 3D touch for iOS.

Multiple SSRS Server support for up to 5 SSRS Servers. As well as improvements to the slicers.

You can find all the blog details here: Power BI Mobile apps feature summary – March 2017

Power BI – Video Gallery & Ask a Partner Anything

As you can see from above the Power BI team has now launched the Video Gallery where you can view tips and tricks on how to do things with Power BI. I think that this is great because it is often easier to see how to do things.

As well as another Ask a Partner anything on 06 April.

You can find all the details here: Announcing the new Power BI Video Gallery and Ask a Partner Anything live event

Power BI – View Related Content Pane

Once again the people in the Power BI team, specifically on the Power BI Service side are making it a lot easier to navigate and understand how your reports piece together.

This is very powerful as well as easy to use, because you get to see all the related content, but you also get the capability to be able to go into the settings or item specific areas, where before you would have had to go through a few clicks to get there.

You can find the blog details here: Announcing the View Related Content Pane: Faster Results with Fewer Clicks

Power BI – Sneak Peak add new Columns from Example

As you can see from above, this is a sneak peak of a new feature that is going to be released in the next version of Power BI Desktop.

I have to say that this is amazing, as it is starting to do the hard yards for you. Yes it might take a bit of practice to get it right, but once you have mastered it, it can save you a lot of time and effort

And the thing that I really like is that it will enable the users who are not so proficient at Power Query to be able to expand on their data, which in turn will lead to amazing insights.

You can read the blog post here: A sneak preview of the new Add Column From Examples data transformation

Power BI – Tracking Adoption via Power BI Audit Logs

This is a great blog post from JAVIER GUILLEN, where he shows you how to use the Power BI Audit logs to show how adoption of not only Power BI, but also the dashboards and reports are being used.

You can find the blog details here: TRACKING ADOPTION VIA POWER BI AUDIT LOGS

SSAS / Power BI – DirectQuery WhitePaper

As you can see above there is a whitepaper for DirectQuery in SQL Server 2016 Analysis Services, it is noted that some of the concepts are shared with Power BI.

And I have already downloaded the whitepaper and will be getting stuck into it very shortly.

You can find the blog post details as well as the link here: DirectQuery in SQL Server 2016 Analysis Services whitepaper

Power Query – Adding Parameters within a piece of text

Below is where I had a situation where I wanted to put in a parameter within a piece of text. This was so that I could then dynamically change the Month Version for my budget and when I refreshed my data it would then use my specific Month Version. This was due to the requirement being that they wanted the ability to select ANY Month Budget Version.


  • I had a requirement where I wanted to use a parameter value, but it would form part of a complete part of text. And as shown below in this example it would be for Mar (March)
  • I wanted the Output to look like the following:
    • Budget_Mar_YR1
  • And the part which is part of the variable is highlighted in BLUE above “Mar
  • I also wanted to re-use this for multiple conditions later in my script.
    • I required it for the following:
      • CY – This is for the Current Year
      • YR1 – This is for the following Year 1, so if I am in 2016 it would be for Year 2017
      • YR2 – This is for the following Year 2, so if I am in 2016 it would be for Year 2018
      • YR3 – This is for the following Year 3, so if I am in 2016 it would be for Year 2019


In order to do this, I went into the Advanced Editor in the Query Editor.

The name of my Parameter was calledBudget Version“, so when using it in the Advanced Editor it would be used with the following syntax below.

#”Budget Version”

In the section below is where I now defined by additional conditions, so that they would be dynamic. An explanation will follow afterwards.

BudgetVersionCY = “Budget_”&#” Budget Version”&“_CY”,

BudgetVersionYR1 = “Budget_”&#” Budget Version”&”_YR1″,

BudgetVersionYR2= ” Budget _”&#” Budget Version”&”_ YR2″,

BudgetVersionYR3= ” Budget _”&#” Budget Version”&”_ YR3″,

As you can see above each line was compromised of the following:

  • I defined our name highlighted in RED
    • BudgetVersionCY
  • Then I started with what our name was, which is highlighted in BLUE
    • “Budget_”
  • Next is where I inserted our Parameter highlighted in PURPLE
    • &#” Budget Version”&
    • NOTE: When you want to add additional TEXT or parameters you have to open it with the ampersand “&” as well as close it off (or end it) with an ampersand “&” also.
  • And then finally I added some more text at the end highlighted in BLUE again.
    • “_CY”

Then later in my query is where I put in my conditional statements into my Conditional Column as shown below.

#”Filtered Rows” = Table.SelectRows(#”Added Custom2″, each ([Budget Version] = #”
” or [Budget Version] = #” BudgetVersionYR1” or [Budget Version] = #” BudgetVersionYR2” or [Budget Version] = #” BudgetVersionY3“)),

Final Note

Just one thing to note, is when I put in the following syntax into my Query Editor you will lose the capability to edit it by using the settings or Gear icon

Power BI – Create Last Dataset Refresh Date

Whilst I have read and found other ways to get the Last Dataset Refresh Date, I find that my method is a lot easier and cleaner to complete.

Creating the Last Dataset Refresh Data

Open Power BI Desktop and go into the Query Editor.

Then click on New Source and select Blank Query

Next I renamed my Blank Query from Query1 to Last Dataset Refresh

Next click on the Advanced Editor and put in the following M Code, which I will explain what it does after the code.


TodaysDate = DateTime.From(DateTimeZone.SwitchZone(DateTimeZone.FixedLocalNow(),10)),

#”Converted to Table” = #table(1, {{TodaysDate}}),

#”Renamed Columns” = Table.RenameColumns(#”Converted to Table”,{{“Column1”, “Last Refresh Date Time”}}),

#”Changed Type” = Table.TransformColumnTypes(#”Renamed Columns”,{{“Last Refresh Date Time”, type datetime}})


#”Changed Type”

What the above is doing is it is getting today’s Date and time. And this is where I created the magic on the first line.

If you have a look I have put in the Timezone that I am in. I currently am living in Brisbane which is +10 from GMT.

So as you can see from above I have put in 10 for the DateTimezone.SwitchZone, highlighted in BLUE.

The rest of the code converts it to a table, and then I rename the column and finally give it the data type of DateTime.

So the result is the following I created shown below.

I then loaded this into my Power BI Model.

Using the Last Dataset Refresh in your Power BI Reports

Now what I did was I had to create a Calculated Measure, so that I could then use the Last
Dataset Refresh Date.

Below is the Syntax that I used to create my Calculated Measure called Data Last Refreshed

Data Last Refreshed =

VALUES ( ‘Last Dataset Refresh'[Last Refresh Date Time] )

Finally I put the measure onto my report and configured it with the Card Visual and the following properties shown below.

I did this to ensure that it would take up the least amount of screen real estate as possible.

And I test it by publishing it to the Power BI Service and it displayed the correct Date and Time.


As you can see this is a simple and effective way to shown when the data in your Power BI Report was last updated.

Any questions or suggestions please feel free to let me know.