BI-NSIGHT – Power BI (Garnet Magic Quadrant)

It has been a great week in the world of Microsoft BI, with the release of the Gartner Magic Quadrant and how well Microsoft is doing in this space.

Other than that, nothing else to report on this week.

Power BI – Gartner Magic Quadrant

As you can see from the image above Microsoft is leading as a Visionary as well as in the ability to execute. And I personally agree with this view. With the implementation of Power BI into the Microsoft BI stack, it is only going from strength to strength. And I have no doubt that when we look at next year this time, Power BI will be even more awesome.

You can read the blog post from the Power BI Team here: Gartner positions Microsoft as a leader in BI and Analytics Platforms for ten consecutive years

Power BI – Infographic for Power BI from an end to end perspective

So for a bit of a change, this blog post will not have a whole lot of technical details, but instead I wanted to share something that I have been working on for quite some time.

It is an infographic for Power BI from an end to end perspective.

I have found this very useful when chatting to people about Power BI, as well as in training sessions. This allows people to get an overview of how all the pieces of Power BI piece together.

I have tried to keep it as simple as possibly, but at the same time also provide enough information.

Below are the images in this blog post, as well as also links to where you can download the images if you wish to use them in presentation or training session.

Page 1

Page one is the graphical overview of the Power BI from an end to end view.


Page 2

Page 2 is a high level text based Infographic of Power BI from an end to end view.


If there is anything that I have left out, please let me know, as I will endeavour to keep this current and up to date.

You can access the images from the following link: Power BI Infographic – Latest Version

BI-NSIGHT – Power BI (Data Insights Summit 2017, Integrate Reports into SharePoint, Dual KPI, Featured Data Stories, Apply Filters to your report per User, Grid Custom Visual)

There have been some really great updates coming out especially from the Power BI team in the past week. So let’s get into it.

Power BI – Data Insights Summit 2017

I can remember that the Data Insights Summit of 2016 was a great success and heard some great content and really deep dive information was shared at the summit. I would really love to attend, so I need to see if I can make it work this year.

For those of you interested here is the link to the website:

Power BI – Integrate Power BI Reports into SharePoint

I do know that this is one of the features that have been requested by a lot of people. Not only does this mean that the people in your organization do not have to log into the Power BI service, but that it is a seamless experience that is all contained within SharePoint.

I actually did try this myself and as they said, it was as simple as a copy the URL, add the Power BI Web part and you are good to go. Really amazing!

The only small catch is that you will have to have a Power BI Pro user license to use this functionality. And I feel that most organizations or companies that are using SharePoint online this should be really be an issue. As larger organizations or businesses will require the Pro features. And for the low price the amount of Pro features is ever increasing.

You can find the blog post here: Integrate Power BI reports in SharePoint Online

Power BI – Dual KPI

I did blog about this last week with regards to the new Custom Visual for the Dual KPI. And now there is a great video by Guy In a Cube which explains how it works, and how best to use it.

You can find the blog post here: Visual Awesomeness Unlocked: Dual KPI custom visual

Power BI – Featured Data Stories

Congratulations for the winners in the Featured Data Stories in Power BI, there were some great winners and content that the community posted.

You can find the details and additional data stories here: Congratulations to this month’s Featured Data Stories Gallery submissions

Power BI – Apply Filters to your report per User

Here is a great blog post from Kasper De Jonge, where he explains how to filter a report based on the user who has logged in. This does use the Row Level Security but it does it in a different way, to only filter the data.

I am sure that in some instances this would be really useful.

You can find the blog post here: Automatically apply filters to your report per user

Power BI – Grid Custom Visual

Here is another really great custom visual. Where it allows you to have a grid with pagination. So that you do not have to try and fit all your data in a grid onto one page.

Here is the link to the Custom Visual: Grid Custom Visual

Power BI – Getting your report to always filter this month’s data dynamically

I had a requirement where I needed to have the capability to set the filter once on a report for the current month and then it dynamically move as each month progresses.

I will show how I completed this in the steps below.

NOTE: As with almost all Power BI Models, this is going to be leveraging off my Date table that I created. If you want to know how to create your own Date table you can follow my blog post Power BI – How to Easily Create Dynamic Date Table/Dimension with Fiscal Attributes using Power Query

Getting the Current Year-Month in the Query Editor

The first thing that I needed to do was to create a new column in the query editor which will show what the current month is.

  • In order to this it involved first create a column which had a combination of the Year and Month Number.
    • NOTE: This was so that I was selecting the current month.
  • I went into the Query Editor and created the following column as shown below for the Year and Month Number
    • I clicked on Add Column in the ribbon then Custom Column
    • Below is the syntax that I put in the Custom column formula

      Number.ToText([Calendar Year]) & “-” & Number.ToText( [Calendar Month Number])

      • What I had to do above is because the [Calendar Year] and [Calendar Month Number] are formatted as Numbers, I had to change this to Text with the Number.ToText highlighted in GREEN above.
    • As you can see above, what I did here was I combined the Calendar Year and Calendar Month Number, so that the output was as sown below.
  • Next is where I created another column which will be the current Year-Month.
    • I clicked on Add Column in the ribbon then Custom Column
    • Below is the syntax that I put in the Custom column formula

      Number.ToText(Date.Year(DateTimeZone.SwitchZone(DateTimeZone.FixedLocalNow(),10))) & “-” & Number.ToText(Date.Month(DateTimeZone.SwitchZone(DateTimeZone.FixedLocalNow(),10)))

      • To explain this best was for me to explain this from the inside out, so starting with the DateTimeZone.FixedLocalNow() is where it is getting your Local Data for the time zone that you are in. This is highlighted in ORANGE.
      • Next I put in the DateTimeZone.SwitchZone() and set this to 10.
        • NOTE: The reason that I did this, is so that when this Power BI Desktop file gets uploaded to the Power BI Service, it will still show the correct time for my current time zone. This was highlighted in LIGHT BLUE
      • Next what I did to get the Year, I used the Date.Year function which will just return the Year, for what is currently being requested, and in this case is the current date. And is highlighted in PURPLE
      • Next what I did to get the Year, I used the Date.Month function which will just return the Month, for what is currently being requested, and in this case is the current date. And is highlighted in GREY
      • And finally I had to do above is because the Date.Year and Date.Month
        automatically get formatted as Numbers, I had to change this to Text with the Number.ToText highlighted in GREEN above.
    • So as you can see below for my current date (15 Feb 2017) I got the following in my column called Current Year Month which should be 2017-2

Creating the Comparison Column and required output

In the next steps below I will show how I now easily created the comparison column with the required output.

  • As with the example I wanted to get the Current Month.
  • I went into the Query Editor and created the following column as shown below for the Year and Month Number
    • I clicked on Add Column in the ribbon then Conditional Column
  • I then put in the following conditions as shown below.
  • As you can see above I used both of the columns [Year-Month] and [Current Year Month] to evaluate my condition.
  • The result of the conditional column is shown below.
  • And what I did to confirm that it was working correctly is I filtered my [Is Current Month] column to “Current Month“, so that I should only see the Dates for Feb 2017, which is what I saw as shown below.
    • NOTE: I did remove this filter before loading the data into my Power BI Model.

Putting the page filters in place for current month

So the final step I did was to now put in the page filter, or even using a slicer to filter the data to always show the current month.

  • So as you can see below what I did was I created a Month Selection slicer and applied the Interaction Only to the chart on the right hand side.
  • Whilst the chart on the left hand side I set the interaction to None

Additional column filters for different periods

Below are some additional column filters, so that you can use them for your own periods.

In order to do this, you will use the code below and use it when creating the column called [Current Year Month] or new column.

Current Fiscal Year

Number.ToText(Date.Year(DateTimeZone.SwitchZone(DateTimeZone.FixedLocalNow(),10))-1) & “-” & Text.End(Number.ToText(Date.Year(DateTimeZone.SwitchZone(DateTimeZone.FixedLocalNow(),10))),2)

  • NOTE: The above has the following output.

Current Date

  • Date.From(DateTimeZone.SwitchZone(DateTimeZone.FixedLocalNow(),10))
  • NOTE: The above has the following output.


So as you can see from the final output, by creating the columns in the query editor and then using the output as a slicer in the above example or as a page filter, you can then apply this to your data to always be current.

One thing that you need to be made aware of, is that in order for this to work correctly, you will have to refresh your data based on the frequency of your filter. As with the above example it was Monthly, so I would have to ensure that my data was updated monthly.

Finally you can download a copy of the above file here: which has got the additional columns also: Filtering Reports to Current Month.pbix

BI-NSIGHT – Power BI (February Desktop Update, iOS Mobile App with Q&A, Dual KPI Custom Visual, PowerApps Common Data Service) – Excel (Get & Transform Updates for January)

I had to re-write this, as I had a suspicion that the Power BI Desktop would be released soon and here it is, along with other updates.

Power BI – February Desktop Update

This Power BI Desktop update is here, and I have to say that there are some awesome additions and new improvements. So I will highlight below what I think are most relevant.

Report View

It is great to finally have the word wrap not only on the headers but also on the actual rows in the data.

Also having the capability to change the font size for the X and Y Axis in your charts is really handy. As sometimes the font was just too large. Along with this is the chart line thickness, which also can be a great guide for the users consuming the report to highlight something specific and draw their attention to that.


I am sure that this will continue to expand and great to see that you now have got the options to calculate the Percent of Row Total and Percent of Column Total.

Data Connectivity

There are some great additions in here and having the capability to select related tables from ODBC and OLE DB connectors.

As well as having the capability to having unified text & CSV connectors, as well as being able to connect to the PowerApps Common Data Service.

Query Editor

I do really enjoy working in the query editor, and now having the option from the column header to select the locale directly from the column header.

As well as now the ability to insert steps into existing queries very easily. Which I have found in the past was not too difficult, but this new addition makes it a lot easier and seamless.


And finally they have included the options to find Solution Templates as well as Partner Showcases very easily. This can assist you if you are looking for a particular solution template, or looking for a partner to potentially assist you with your Power BI project.

You can read all the updated details here: Power BI Desktop February Feature Summary

Power BI – iOS Mobile App now with Q&A

It is fantastic to see how the mobile app has evolved and I think that having the Q&A in the mobile app is really a smart move. Very often you want to find some piece of information that is not currently in a visual. And by using the Q&A you can find the information you are after.

And I have no doubt that you can also actually ask a question and it will translate that into words and bring up the visual you are after.

Here are the blog details: Now in preview: Conversational BI with Q&A on Power BI mobile apps (iOS)

Power BI – Custom Visual – Dual KPI

This is another really interesting visual from Microsoft, the dual KPI. This gives you the ability to have two KPI’s where you can compare different measures over the same timeline. I am sure that this can bring some valuable insights into your data.

You can download the visual here: Dual KPI

Power BI – PowerApps Common Data Service

As you can see above you can now connect to the PowerApps common data service. With the preview release they have enabled 10 perspectives to connect Power BI to. I am sure that as time goes on this list of perspectives will increase.

If you are using PowerApps and want to see how to connect and enrich your data using Power BI, read the blog post below.

Create Power BI reports and dashboards with PowerApps Common Data Service

Excel – Get & Transform updates for January 2017

For those people who use Excel it is great to see that they have updated the Get & Transform with the following.

  • New OLE DB connector.
  • Enhanced “Combine Binaries” experience when importing from any folder.
  • Maximize/Restore buttons in the Navigator and Query Dependencies dialogs.
  • Support for percentage data type.
  • Improved “Function Authoring” experience.
  • Improved performance for OData connector.

As you can see from the list above, there are some great additions as they keep on building on the existing features and I personally have used the Combined Binaries and it works really well and makes things a lot easier when working with Excel and CSV files.

You can find the blog post here: January 2017 updates for Get & Transform in Excel 2016 and the Power Query add-in

Power BI – Why creating columns in the Query Editor is better, quicker and more efficient than creating Calculated Columns.

I have been reading and helping out where I can on the Power BI Community forums. And what I often see is people trying to use the Calculated Columns in the Power BI Desktop Model.

So in this blog post below I am going to explain why it is better, quicker and more efficient to use the Query Editor to create additional columns.

Example Scenario

For this example, what I am going to be doing is searching within the Description
column for a piece of text, and then creating a new column which specifies the area.

The two pieces of text that I want to find are “Gold Coast” and “Brisbane“, which will then go into my new column called Area.

If I cannot find “Gold Coast” or “Brisbane” I will then make it “Unknown

The harder way doing it with a Calculated Column

Below are the steps where you can do this using a calculated column in your Power BI Model.

  • To do this I opened up my Power BI Desktop file and then in the columns clicked on New Column
  • Now in order to find the piece of text that I am looking for I have to use the SEARCH
    function in DAX, but as you can see below in order for this to work I have to add in some additional DAX functions

    Area (Model) =

     IF (

        IFERROR ( SEARCH ( “*Gold Coast*”, [Description] ), -1 ) = 1,

        “Gold Coast”,

    IF (

    IFERROR ( SEARCH ( “*Brisbane*”, [Description] ), -1 ) = 1,





  • As you can see above I have had to use the IFERROR, so that if there is indeed an error I can capture this, and not cause the calculated column to fail.
  • As well as the outer IF condition is where I am saying if the SEARCH = 1 meaning that it is TRUE or Correct, then what Text I want it to display.
  • And as with my two conditions this is for the “Gold Coast” and “Brisbane”
  • And then finally if either of the conditions are not met, then make the output “Unknown
  • NOTE: There might be a more efficient way to do this, and if there is please leave it in the comments section.

As you can see with the above method, this is very clunky as well as very prone to errors.

As well as in the past when I had quite a few conditions I was searching for, it becomes very long and complex.

The easier way, using the Query Editor

Below I am going to show how to achieve the same result in a much easier way using the Query Editor.

  • To do this I opened up my Power BI Desktop file and clicked on Edit Queries
  • Now once the Query Editor Opened I clicked on Add Column in the Ribbon.
  • Next I selected Conditional Column and create it as shown below.
  • As you can see above this is searching for my requirements within the Description column.
  • I then clicked Ok, and then clicked Close And Apply to Load the data into my Power BI Desktop Model.

As you can see to create the conditional column is very easy and simple to search for text and create the additional column.

As well as if there is a requirement for additional conditions, it is very easy to add.

You can see below that both columns have the same output.


So in conclusion you can see that there is a simple way, as well as a more complex way to get the same desired output.

I would also like to note that with my current understanding of Power BI and how the compression and performance works, that it is best practice to ideally create your columns in your source data. If you cannot it is then next best to do it within the Query Editor. And then last resort is in the Power BI Model.

And with each process above being less optimal.

And in my opinion I do not see why you would need to create a Calculated Column, when this can very easily be achieved in the Query Editor.

You can download the sample file here: Calculated Column.pbix

BI-NSIGHT – Power BI (January Service Update, Push data to Power BI Streaming, Mobile Apps update Jan 2017)

I cannot believe that is already February which means that really soon there should be the next Power BI Desktop release.

Here are this week’s updates, which I hope you will find informative.

Power BI – January Service Update

There are quite a few service updates this month with some of them being more for the monitoring and administration of Power BI, which is vital.

They have created a new AD Role for a Power BI Admin, so that you no longer have to be an Office 365 Admin to look after Power BI.

They have also enabled Audit Logs for Power BI, which is really key to ensure that you can see what is happening with regards to Power BI, especially when looking for how the business is using Power BI.

As well as already mentioned before is the Email Subscriptions, New APIs for Custom Visuals, Real Time Streaming Generally available and finally the ability to stream data to Power BI using Microsoft Flow. Which has more details below.

You can read the blog post here: Power BI Service January Feature Summary

Power BI – Push data to Power BI Streaming

This is really a great addition to the Power BI offering, and I personally have tried this myself and it is really easy and simple to get going. I showcased it to the organization I am currently contracting in, and they were really amazed.

I can see this being the opening door, in terms of how easy going forward it is going to be to stream data, real-time into Power BI.

You can find the Blog Post here: Push data to Power BI streaming datasets without writing any code using Microsoft Flow

Power BI – Mobile Apps Update Jan 2017

In this month’s Mobile Apps update, they have now enabled Single Sign On (SSO) with Active Directory Federation Services, which now means you can sign on with your Organizational account and access SSRS reports.

They have also removed the 100 row limit on tables and matrices. As well as updating the Annotate & Share for Android Devices.

You can find the blog post here: Power BI Mobile apps feature summary – January 2017

Power BI – General Availability of Streaming Datasets

It is great to see that the Power BI Streaming datasets is now Generally available. As with the previous details, I have no doubt that as companies evolve, there will be sutiations where they will want to monitor data in real-time and in other instances be able to historically track trends and patterns. Now with this capability it is a lot easier to do both.

The blog details are here: Announcing General Availability of Power BI Real-Time Streaming Datasets