BI-RoundUp – Power BI (Office 365 Adoption Content Pack – Embedded Accelerator) – Excel (Get & Transform Updates May 2017)

This will no doubt be a quieter week, leading up towards the end of May and into June when there will be the next wave of updates from the Microsoft Power BI Team

You might also notice that I have changed the name from BI-NSIGHT to BI-Roundup, as there was another blogger who has his blog URL as BiInsights, so rather than make it confusing I thought to change the weekly update name.

Power BI – Office 365 Adoption Content Pack

There is a new content pack for Power BI, which will leverage off of the Office 365 reporting, so that you can gain some insights into how your organization is adopting Office 365.

You can find more details here: Explore your Office 365 Adoption Data in Power BI

Power BI – Embedded Accelerator

If you are looking to use Power BI Embedded, Microsoft have a limited offer to help you get up and running.

You can find the blog post here: Power BI Embedded Accelerator

Excel – Get & Transform Updates May 2017

While the features below have been available in Power BI, it is great to see them also incorporated into Excel, as these are some really useful updates.

The first one is the auto selection of the delimiter when using the Split By, I have been using this for a while and whilst it does not appear to be time consuming it is awesome to have it pre-selected for you.

The second one is when combining files to be able to select which sample file to use from a folder.

And finally you can now connect to DB2

You can find more details here: May 2017 updates for Get & Transform in Excel 2016 and the Power Query add-in

Power BI – Dynamic Banding across all fields

I had a requirement from the customer where I am consulting and what they wanted was to be able to have dynamic banding. So that no matter what they put into the table it would show the banding where it would not go greater than the banding amount.

This is easiest shown with an image, so if the band was < $1000 the amounts should not be greater than $1000.

So as you can see above in this context the Banding is working as expected, showing values that are < $1000

But when you remove the Document No. I got the following shown below.

Now whilst the SUM Amount is correct, the banding is incorrect, because the SUM Amount is greater than $1000.

So below I show how I got this working.

Creating my Value Banding Disconnected Table

The first thing that I had to do was to create a disconnected table which would hold my banding.

  • To do this I used the Enter Data from the Ribbon in Power BI Desktop
  • And I put in the following details
  • I then clicked Ok which loaded it into the Data Model.

Creating measures for my Bands

Next I had to create individual measures for each of the above bands.

  • I found that this was the easiest way to ensure that when the data was being filtered by which ever field the user used, it would bring back the correct results.
  • I then created my 5 measures as shown below, the reason for using the IF statement is so that if the measure is not within the banding bounds make it BLANK. Which effectively in Power BI will not show any data.
    Band 0 - 100 =
    IF ( [Amount] >= 0 && [Amount] < 100, [Amount], BLANK () )
    Band 100 - 1000 =
    IF([Amount] >= 100 && [Amount] < 1000, [Amount], BLANK() )
    Band 1000 - 10000 =
    IF([Amount] >= 1000 && [Amount] < 10000, [Amount], BLANK() )
    Band 10000 - 100000 =
    IF([Amount] >= 10000 && [Amount] < 100000, [Amount], BLANK() )
    Band > 100000 =
    IF([Amount] > 100000 , [Amount], BLANK() )


Final Measure to be used in Table or Visuals

This is the final measure which I can use in a table or visuals. And this uses a combination of the disconnected table and the measures we created above.

  • Here is the measure.
    Amount Bands =
    IF (
      HASONEVALUE ( 'Value Banding'[Band] ),
      SWITCH (
        VALUES ( 'Value Banding'[Band] ),
        "< $100", [Band 0 - 100],
        "< $1 000", [Band 100 - 1000],
        "< $10 000", [Band 1000 - 10000],
        "< $100 000", [Band 10000 - 100000],
        "$100 000 +", [Band > 100000]
      [Band > 100000]


  • What the above measure is doing, is looking to see which item has been selected on the Disconnect Table and display the corresponding measure.
  • As well as if nothing is selected to default to > $100 000

What it looks like in Action

Below is now what it looks like when I select < $100 and have it using the Document No. (This table is sorted highest to lowest)

And then again if I change it to < $100 000 I see the following for the Document No. (This table is sorted highest to lowest)

Then I did a test where I dragged in a field that I know would aggregate to a higher level, and I wanted to ensure that it would display correctly. And as you can see below, it has put the correct Reason into the correct Bands.


So by creating the dynamic banding I was able to create something for the customer to get some really valuable insights into their data.

BI-NSIGHT – Power BI (Zoom Charts – Preview Power BI Report Server – Featured Data Stories)

For once it has been a quieter week in the world of BI, so here are the updates below.

Power BI – Zoom Charts

I have to say that it has been a while since a visual has really impressed me and the Zoom Visuals do just that.

They provide an almost natural way to interact with your data via the charts.

It is best shown when you go to the blog post and interact with the Power BI Embedded report.

You can find all the details here: Power BI + ZoomCharts = (Power BI)²: Boost your productivity and add the cool-factor to your reports

Power BI – Preview of Report Server

Since the news of Power BI Premium details did emerge that there now would be Power BI Report Server, which you could use as part of Power BI Premium. They have now released a preview version for people to use.

Due to it being a preview version not all the functionality is there, but there are enough options to download, install and start testing it.

You can find the blog post here: Power BI Report Server preview now available

Power BI – Featured Data Stores

There have been some great submissions for Data Stories in the past 2 months. And this is highlighted with the image above. As well as a congratulations to the other people whose stories were also featured.

You can find the blog details here: Congratulations to the Featured Data Stories Gallery submissions for March and April

Power BI Desktop/DAX – Using Unicode (Arrows) as Measures in a Table

I was helping out in the Power BI Community and I came across the following requirement.

The user had sales figures and based on the Previous month, they wanted to have an arrow showing if it was up or down. As well as if there was no data to say that there was no data. So this is how I achieved this below.

Creating a Date Table to make PREVIOUSMONTH DAX measure easy

The first thing that I did was to create a Date table and link this to my table above.

This ensured that when I wanted to create my PREVIOUSMONTH DAX measure it would be simple and easy.

I did it using my blog post: Power BI – How to Easily Create Dynamic Date Table/Dimension with Fiscal Attributes using Power Query

Once I had my Date Table I then created the relationship between my Sales Data table and my Date Table

Creating PREVIOUSMONTH DAX measure

Next I created my Previous Month measure with the following DAX Syntax

Previous Month Sales =
CALCULATE ( [Sales Amount], PREVIOUSMONTH ( 'Date'[Calendar Date] ) )

Getting the Unicode numbers for Arrows

Now in order to get the Unicode Characters I followed the very interesting blog post by Chris Webb: The DAX Unichar() Function And How To Use It In Measures For Data Visualisation, in which I learnt how to use Unicode Characters in a measure.

Next I found the following website which contained the Unicode numbers for my arrows I required.

I then made a note of the following ones that I wanted to use

Creating the Arrows measure

The final piece was where I created the Arrows measure.

Below is the DAX Syntax with an explanation afterwards

Arrows = SWITCH(
                ISBLANK([Previous Month Sales]),BLANK(),
                    || ISBLANK([Sales Amount]),BLANK(),
                [Sales Amount] <= 0, "No Data",
                [Sales Amount] >= [Previous Month Sales],UNICHAR(9650),
                [Sales Amount] <= [Previous Month Sales],UNICHAR(9660),

  • Due to having multiple conditions instead of having nested IF statements I found it easier to use the SWITCH DAX function.
  • I also know that with the SWITCH DAX function it evaluates from top to bottom.
    • So the first condition was to check for if the [Previous Month Sales] or the [Sales Amount] was blank meaning it was the start or end of the data to make it BLANK. It is highlighted in GREEN
      • This could be changed to anything value, but I prefer it being BLANK.
    • Next I put in a condition to see if the current Months [Sales Amount] is less than or equal to 0 to put in the value of “No Data” It is highlighted in PURPLE
    • Next is where I use the Unicode characters in my measure. Here I compare if the Sales for the [Sales Amount] is >= the [Previous Month Sales]and if it is then use the UP Arrow. This is highlighted in BLUE.
    • Next is where I use the Unicode characters in my measure. Here I compare if the Sales for the [Sales Amount] is <= the [Previous Month Sales], and if it is then use the DOWN Arrow. This is highlighted in ORANGE.
    • And then finally if none of the above conditions are met then make the value BLANK

Table Output

Below is the outputted table, which is doing as what was required.


As you can see from above being able to make sure of the Unicode Characters can make your dataset that much easier to read.

You can view it here: PBI – Unicode Measures

You can download the Sample File here: PBI – Unicode Measures.pbix

BI-NSIGHT – Power BI (Data Connector SDK, Analytics Challenge, April Service Content Packs, ArcGIS May Update) – Azure (SSAS Model into Azure

After all the announcements last week here are this week’s updates.

Power BI – Data Connector SDK

I know that this has been something that I have seen asked on the Power BI Community, as well as thought about in terms of having the capability to create your own Custom Data Connector in Power BI.

Don’t get me wrong they have supplied a vast array of connectors already. But by creating the SDK this means that now virtually ANY data, or even potentially anything could be integrated into Power BI. I saw a tweet from Kasper mentioning a data connector for Strava (Which I use a lot for my exercising, as well as having a wealth of data).

You can find all the details here: Data Connector SDK Developer Preview

Power BI – Analytics Challenge

With the Microsoft Data Insights summit coming up they have created an Analytics challenge in three area’s.

Complex Data Preparation, Advanced Analytics and Just for Fun.

It will be really interesting to see what people can do, I am hopeful that I will submit something.

Here are the details: Announcing the Microsoft Data Insights Summit Analytics Challenge

Power BI – April Service Content Packs

There is a new feature from the Power BI Team which as you can see above is to give more details around what are the new Service Content Packs for each month. So as you can see there are quite a few new service content packs for April 2017.

You can read the blog post here if any of the above interest you: Power BI April Service Content Packs

Power BI – ArcGIS May Update

As you can see from above the people from ArcGIS are now updating it on a nearly monthly cadence.

In this month’s updates you now can use Drive Time Area’s, as well as Tooltip fields and a host of bug fixes.

You can find the blog post details here: What’s New in ArcGIS Maps for Power BI (May 2017)

Azure – Getting your SSAS Model into Azure

This is a great blog post by Kasper De Jonge where he shows you how to get your SSAS Tabular model into Azure.

You can find more details here: Getting your on premises SSAS Tabular model to Azure

Power BI – Configuring SharePoint/Folder refreshing using On-Premise Gateway

I had a requirement to connect and refresh their files stored in SharePoint On-Premise using the On-Premise Gateway. And are the steps that you can follow in order to connect to files stored within SharePoint On-Premise or files stored in Folders.

In my working example below I am going to connect to a SharePoint Folder. You can apply the same steps for a Folder as well.

How to find the SharePoint Folder for Windows Explorer

I am not sure if people are aware, but you can actually connect to SharePoint Files and Folders using your Windows Explorer. The trick I am going to show below is how to find the correct URL.

Typically when files are stored within SharePoint the SharePoint Administrator will create a document library. If you cannot see the details below please confirm with your SharePoint Administrator that where you are looking is indeed a Document Library.

NOTE: For this to work you HAVE to use Internet Explorer

  • I went to the location where I had my files stored.
  • Then in the ribbon on the top I clicked on LIBRARY
  • Now if you look across the ribbon, under the section Connect & Export there will be a button which says Open with Explorer, click on this.
  • This will then open in Windows Explorer
  • Now if you look above you will see that the notation is a web based notation.
  • All that you now need to do is to change this to use a UNC notation.
  • So with my example I changed it to the following.
    • So my UNC was:
      • \\\business\fg-bi-hub\Program Dashboards
    • If you see above I have changed it from the https:// to the double backslash \\
    • And then I have changed it from the forward slash / to the backslash \
  • Now you have the folder location of where your files are stored.

Connecting to your files using Power BI Desktop

In the steps below I will demonstrate how I connected to my files using Power BI Desktop.

The steps below are critical if you want to ensure that the On-Premise Gateway
refresh will work later.

  • I opened my Power BI Desktop file and clicked on Get Data
  • Now the critical part
    is right at the start, because in order for this to work I HAD to select Folder
    as shown below.
    • NOTE: If I did not select Folder (I did not with my initial testing) then when it comes to the refreshing I got an error which I will explain later. (Related to the Gateway setup for the Data Source)
    • I clicked on Connect
  • Next I then selected my folder as with my example above.
    • \\\business\fg-bi-hub\Program Dashboards
    • Then I clicked OK.
    • If I did not put in the UNC Folder Path, I got the following error
  • When the window opened I clicked on the Edit button
    • The reason that I did this is because I still wanted to select my individual file from within the folder.
  • This then opened the Query Editor.
  • What I did next was to select the file that I wanted, in my example it is the file called Calculated Columns Data.xlsx
    • I did this by right clicking on the file in the Name Column and selecting
      Text Filters and then Equals
    • This then filtered my Folder to only show the file that I wanted.
  • Next I clicked on the Combined Files Button
    • This then opens the Combined Files Window.
  • As with my example I selected the Table that contained the data I required. I selected Table1
    • Then clicked OK.
  • Once loaded I then got my data loaded into my Table in the Query Editor
  • Now I modified my table and did what I required to get it into the required format.
    • NOTE: For those interested what I found was key to making sure the refresh work was by going into the Advanced Editor and looking at how the data was imported.
    • It is that it has to have Folder.Files, if it is anything else then the refresh in the On-Premise Gateway in the Power BI Service WILL fail.
  • I then clicked Close & Apply to load this to my Power BI Desktop Model.
  • I then created a table so that I can see my required data.
  • I then uploaded this to the Power BI Service.

Creating the Data Source in the Gateway on the Power BI Service

Next I had to create the related Data Source in the Gateway section of the Power BI Service.

NOTE: You need to be an Admin in the Power BI Service to complete the steps below, if you are not, then ask your Admin to configure the steps below.

NOTE II: I had already installed and configured the On-Premise Gateway within the domain I am working in. You can reference this installation
guide: On-premises data gateway

  • I logged into the Power BI Service and then clicked on Manage Gateways
  • I then clicked on Add Data Source
  • Next I added my Data Source with the details of my example below.
    • As you can see I had to choose the Data Source Type as a Folder.
    • This is so that the connection will be made from my Power BI uploaded Dataset with the Gateway in the Power BI Service.
    • I had to also ensure that the Windows Username and Password that I used in this Data Source, also had the correct permissions in the SharePoint Document Library.
      • In the Organization I was consulting, they had the following user setup in the SharePoint document library
      • This is the same Windows username and password that I used to configure the Data Source.
    • I then clicked Add
  • Now the Gateway configuration is complete.

Connecting my dataset to the Gateway in the Power BI Service

The final step I did was to connect my dataset to the Gateway in the Power BI Service and make sure that it refreshes.

  • I went into the Power BI Service.
  • Then clicked on Settings and selected Settings
  • Then I clicked on Datasets
  • I then went to my dataset that I had previously uploaded, where I wanted to connect to it using the Gateway connection.
    • I then expanded the Gateway connection
    • Now I had the option to Use a data gateway
    • And in here I selected my Gateway that I had previously configured.
    • I then clicked Apply.
  • I got the following confirmation.
  • Then the final step was to test that it was all working and I did this by going into the Datasets and clicking Refresh now
  • Once that had completed I went back into Settings, then
    Datasets and clicked on my dataset.
    • Then at the top I clicked on Refresh History

Error – Could not find the file

What happened is previously I had not configured everything as per my steps above so I got the following error below when trying to refresh.

As in the past the error messages can be a bit cryptic. But what I learnt was that for this particular dataset that I was using I was NOT connecting use the Folder.Files, but rather the File.Contents

And now looking at the above it became clearer to me as to why it was failing.

The issue is that when I added the Data Source for the Gateway in the Power BI Service, I was selecting the Folder as a Source, but in my Power BI Desktop connection I was connecting use a File and NOT a Folder.

As soon as I changed the above to a Folder the refresh worked.


What I have demonstrated in the steps above is how to connect to files
stored in SharePoint or in a Folder. As well as how to resolve if you get an error.

If you have any comments or suggestions please leave them in the area below.

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

What makes up a Power BI Desktop PBIX File

I know that I personally have been interested in what makes up a PBIX file and in this blog post below I will explain from my understanding what are the different parts that make up the PBIX file. It is rather interesting in that it is actually made up of a few different aspects.

How to view the contents of a PBIX file

The starting place, as well as a question some people might have, is how do you view or know what the contents of the PBIX file are?

From my understanding the PBIX file is loosely based on the XLSX file, in that there is a very simple way to see the underlying contents.

So in order to view the contents of a PBIX file you can do the following below.

  • Either right click on the PBIX file and select Rename, or double click the File to get the option to rename.
  • Then rename the file from the extension of PBIX to ZIP
  • You will get prompted with the following Window asking “Are you sure you want to change it?”
    • Click Yes
  • So now the file will have the ZIP extension

Now if you double click the ZIP file you will see all the contents, which I will go through the known contents in this blog post. How awesome is that?

Report Folder

The report folder contains the following 2 files below.

The Layout file contains all the information with regards to the Report Layout. Which is essentially the report sheets, as well as the placement of the visuals and all of their related properties.

This is a snippet of what the contents of the file looks like below, and as you can see it stores a lot of information that is not very friendly to read.

The LignuisticSchema file appears to hold the contents for the Sheet names if you rename them from the defaultPage 1

As you can see above I renamed a sheet to “Item count on Slicer” and when I open the LignuisticSchema file I see the following below.

[Content_Types].xml File

This XML file contains all the content within the PBIX file

DataMashup File

The DataMashup file contains all of your Query Editor information.

From my understanding it contains all of the following.

  • Connection Details to your Source Data
  • File Names or database names
  • All the Table information
    • Within each table it also contains all the steps

As you can see below here is a snippet from the DataMashup file and in the content
highlighted below it is where it has a step called “Calculated Week of Month

And here is the identical step in the Query Editor.

With the same syntax from the Query Editor.

It is important to note that you can actually copy the DataMashup file and send it to someone who is working with the same data, and get them to replace it with your copy. This will mean that they now have got all the Query Editor information in their
Power BI Desktop file.

DataModel File

The DataModel file is the file that actually stores all of your data in a highly compressed format.

Essentially this is your Power BI In-Memory Analysis Services model. As you can see below it has some detail information and then the stored data. This is where I think to myself a lot of the Power BI Magic happens because it is where the blazing fast query performance comes from.

The size of this file also is an indication of how much memory your Power BI Desktop file will consume.

As with my example the file size is 486KB, which once again shows how good the Vertipaq Compression Engine is.

If you are interested there is a great book which goes into much more details around the Vertipaq engine, in which you can read a snippet in terms of how the Vertipaq engine works here: The VertiPaq Engine in DAX

And I would suggest getting a copy of the book if you are interested in the finer details.

DiagramState File

The DiagramState file appears to store the information for the Table and Matrix locations, but not for the Matrix Preview from what I can gather.


It would appear that the Metadata file contains all the names with regards to what you see when in the Report View.

As you can see below from my Metadata file I have highlighted the Table names in Green below. I have modified the source data so that it is easier to read.

And highlighted the Parameter Names in highlighted the Table names in GREY below.

Here is a list of my tables

Here is the list of my Parameters

SecurityBindings, Settings & Version Files

When I opened up the files SecuritySettings, Settings and Version they appeared to not have any meaningful content or details to talk about. Possibly someone else might have some input as to what these files are responsible for.


I do hope that having a look at the contents that make up the PBIX file has provided a bit more insight as to how a PBIX file works and pieces together.

NOTE: You can rename the file from a ZIP back to a PBIX to get back to your original file and open it again with Power BI Desktop.