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

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

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

NOTE: This currently only works on a PC.

Installation Power BI Publisher for Excel

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

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

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

How to connect to a dataset

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

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

Enabling Access to Dataset via Sharing or App Experience

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

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

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

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


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

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

Power BI – DAX Measures for Excel based “ % of Column Total “ or “ % of Row Total ”

Where I am currently consulting there was a requirement to create a measure like you can in the Excel pivot tables for the % of Column Total or the % of Row Total.

One of the things that you can currently do in Power BI, which I only learnt as part of this requirement is that you can use % of Column Total, or % of Row Total when using a table, as seen below.

But the thing is that as soon as you put that into a visual the only option that you have is the Percent of grand total.

So below are the two measures that I created so that I could then successfully have a % of Column Total (Percent of column total) or % of Row Total (Percent of row total)


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.


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 (Granular Tenant settings, Amazon Redshift connectivity in Power BI Service, Featured Data Stories, Availability Monitoring Publish to Web) – Excel (Get & Transform updates) – SQL Server (DAX support in Report Builder and SSMS)

Here are the weekly updates.

Power BI – Granular Tenant Settings

I can personally say that I know a few of my clients who are very happy that the Power BI team has already implemented the Granular Tenant settings for Power BI. I have to say that I am once again impressed by the Power BI Team really a great job.

I also like the fact that they have gone one step further and put in the option for “Except specific security groups”, because so often you want to allow it for almost everyone, except one group. And with their implementation they make this really easy and simple to implement.

Currently as at 16 Mar 2017, the granular settings are for the following:

  • Export and sharing settings
    • Share content to external users
    • Export Data
    • Export reports to PowerPoint presentations
    • Print dashboards and reports
  • Content pack settings
    • Publish content packs to the entire organization
    • Create template organizational content packs
  • Integration settings
    • Use Analyze in Excel with on-premise datasets

You can read the blog post here: Announcing granular tenant settings in Power BI

Power BI – Gateway Update for March

As you can see above the Gateway for Power BI has had a whole host of updates in the March 2017 release.

In this release there is now an updated UI (User Interface), where you can now see the status of the Gateway.

You also have the options under the Service settings to restart the service, as well as change the Gateway Service Account.

In the Diagnostics section, you can now enable verbose logging, as well as export all the gateway logs to one zip file.

And finally under the Network section there is the capability to view the Local network status, as well as modify the Azure Service Bus connectivity mode.

There is also an update for the Gateway to use a FQDN instead of a list of IP addresses so that you will no longer be required to keep a list of IP Addresses.

Also if you are looking to use an Oracle data source with Kerberos configured the Power BI Gateway team are looking for candidates.

You can find the blog post details here: Power BI Gateways–March Update

Power BI – Amazon Redshift in Power BI Service

This is a informative blog post by the Power BI team where they explain how to connect, and publish your Power BI reports using Amazon Redshift.

You can find the details here: Announcing support for Amazon Redshift connectivity in the Power BI service

Power BI – Featured Data Stories

Congratulations to David Eldersveld on his featured data story, really a great way showcasing Power BI as well as the other people featured.

Next month’s feature requests are tables and matrices.

You can find the featured data stories here: Congratulations to this month’s Featured Data Stories

Excel – Get & Transform Updates

There are two updates to the Excel Get & Transform updates in the March 2017 update. The first is a new transformation which allows you to extract data values from a column. And the second is where you now have support for SQL Server Failovers when using a SQL Server database connection.

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

Power BI – Availability Monitoring Publish to Web

Charles Sterling from the Power BI team has a great blog post if you want to monitor the availability and ensure that your Publish to Web is working. He shows this in a few simple steps.

You can find the details here: Setting up availability monitoring for Power BI Publish to Web Results

SQL Server – DAX support in Report Builder and SSMS

In the latest release of Report Builder and SSMS (SQL Server Management Studio) there now is the ability in the query designer for DAX.

You can find all the details here: Query designer support for DAX now available in Report Builder and SQL Server Data Tools

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

BI-NSIGHT – Power BI (Mobile App Update – Android Tablets, Navigation Preview, AMA, Driving Power BI Adoption in your Organisation) – Excel (Get & Transform Update)

It does appear that as we approach the festive season, the Microsoft team are still full steam ahead, which is great for myself and the Data Analytics community.

Power BI – Mobile App update for Android Tablets

As per their blog post, it is great to see that there now is an App for the Android tablets, whilst this has been around for quite some time on the iPad tables, it has now come to Android.

It is great to see and you can find the blog post here: Now in preview: the Power BI you love on your Android tablet

Power BI – Navigation Preview

I have to say that I have already enabled the preview features, and whilst it did take a little time to get used to, it is definitely a step in the right direction.

It makes it easier to navigate when you have a lot of reports, dashboards and workspaces. As well as less clicks to get to the options that you want to use.

You can find more details here: Announcing the Power BI Navigation Preview

Power BI – AMA (Ask Microsoft Anything)

Depending on when you read this, you might be a little late, but it is great concept where you can ask Microsoft anything relating to Power BI.

I have no doubt that there will be some tough questions posed, but also a great wealth of information into Power BI.

You can see the dates and times above.

You can find the blog post here: Announcing the Power BI Ask Microsoft Anything

Power BI – Driving Power BI Adoption in your Organisation

This is a great blog post in which Microsoft gives an example of how they have used and enabled the business into data driven decisions using Power BI and how to get it adopted within Microsoft.

The really interesting part I think is that Microsoft is a large multi-country company, so if they can adopt Power BI in such a large scale, then it should be achievable with any other companies.

And not only that, but because Power BI is their own product I often like to think that because they build it, they might not use it as much as an external organisation, but they are which is great to see.

I would recommend reading through this if you are looking to get your organisation into Power BI: Driving Power BI adoption in your organization – Learn how Microsoft does this at scale

Excel – Get & Transform Update

It is great to see that there have been so many ypdates to the Get & Transform for Excel. I often feel like the Excel people do get left behind due to the sheer pace from the Power BI Team.

All of the updates are shown below

  • Web Connector—UX support for specifying HTTP Request Headers.
  • OData Connector—support for “Select Related Tables” option.
  • Oracle Connector—improved Navigator preview performance.
  • SAP HANA Connector—enhancements to parameter input UX.
  • Query Dependencies view from Query Editor.
  • Query Editor ribbon support for scalar values.
  • Add custom column based on function invocation.
  • Expand & Aggregate columns provide support for “Load More” values.
  • Convert table column to a list—new transformation.
  • Select as you type in drop-down menus.

You can read their blog post to get more details: November 2016 updates for Get & Transform in Excel 2016 and the Power Query add-in

BI-NSIGHT – Power BI (Desktop Update Oct 2016, Features Coming Soon, R Visuals without understanding R, Community Blog Highlights, Custom Visual Stocked Bar, InsightCentr Content Pack, Webinar Nov 1 Big Data with HDInsight & Datameer, Advanced Analytics with Power BI Embedded and R) – Excel (Get & Transform Updates Oct 2016)

Well there sure is a lot of information with regards to BI in the past week, now that PASS has finished, as well as the end of the month, so there are some great Power BI updates as well.

Power BI – Desktop Update Oct 2016

Another month has come to an end, and with that we get a whole host of new features and updates within Power BI, so let’s get into the details which I think are most relevant.

Reports View

As shown in the image above they have really done a great job at modifying the date slicer to make it really easy and simple to filter your data by date.

I have tried it out very quickly and the great thing is that it appears to work on any date column (where the data type is specified as date).

And the amazing thing is that it does not take up a lot of screen real estate space.

As you can see the next option is the ability to very easily change the date range formatting for your charts. So this makes it very quick and easy to put in the specific date ranges for your charts using a date picker. Another thing to note is that you can also manually put in the time if required.

Another feature that I know a lot of people have been asking for is the snap to grid. Personally I have had to nudge the visuals into place in the past, so it is fantastic to see that not only can you snap it to the grid, but you can also see the grid lines. Just note that this is a preview feature so you will have to add it to the Preview features to enable.

With regards to the data labels it is good to see that you now have additional formatting options for the orientation as well as position of the data labels. Once again this is only available in the column, bar and waterfall charts.


The first analytical feature for this month’s release is the ability to Group your data by simply selecting the data in your chart. This has been really well thought out, and they have made it really easy to group data together. What I really like is that not only can you easily group the data, but you can also rename the column, and do it without having to know any DAX. And this group can also then be used within your report as any standard column.

Binning is another great feature that forms part of the grouping and is mostly related around a measure. It gives you the ability to create a bin for your measure. And once again this then becomes a standard column that can then be used within your reports, and what I especially like is that it NO longer is required to be used on the VALUE AXIS, but can be used on the Axis, Legend or Color Saturation!

Now once again this is a feature that a lot of people have been asking for, and in the past I honestly have done this by creating a rank and then filtering using a rank to get the TOPN. But now that they have built this functionality into the filtering makes it really simple, quick and easy to filter for the Top or Bottom amounts.

What I think is the hidden gem here is that it gives you the ability to use a different value for your Top or Bottom amounts. And this is something that I am certain will flow into other parts of the product.

The Include/Exclude capability is going to be really useful as what this does is it gives you the ability to very quickly and easily decide on what information you want to still show.

Data Connectors

As you can see above they have included 3 additional connectors.

For me personally the big one is the OLEDB, which will now mean that there is the capability to connect to a whole range of additional database systems.

Query Editor Improvements

This is another great improvement in terms of being able to use tables as parameters for your “Invoke Function” dialog. As in the past I can recall that you could only do it from Lists (and they did build the capability to transform to lists easily). But with this new and improved feature it will mean that we can then dynamically pass data from a defined table to a function.

Power BI – Features Coming Soon

Below are the details from a screenshot that I have seen from Reza Rad who was at SQL Pass

Power BI – R Visuals without understanding R

This is something that I can personally see starting to gain momentum very quickly. Personally I do not currently know R or how to program in R, but what this now does is it gives me the ability to utilize the work of others which I can then use.

I am going to be looking into this in the near future and I really think it will become invaluable.

You can find the details here: Shape Power BI R visuals, without understanding R

Power BI – Community Blog Highlights

This is a quick mention to always go and view the community blog highlights. There is often some really good blog posts, and a wealth of information.

As well as a quick mention that I also have a community blog post relating to quick tips for getting dynamic row level security working.

You can find the details here: Power BI Community blog highlights for October

Power BI – Stock Chart Custom Visual

Here is another custom visual from MAQ Software, which can be used for Stock Analysis

In order to view this go to the Custom Visuals Gallery here: Power BI Custom Visuals

Power BI – InsightCentr Content Pack

Here is another great content pack for Microsoft Power BI if you are an existing InsightCentr customer. This provides insights into workforce productivity.

You can find more details here: Explore your Insightcentr data with Power BI

Power BI – Webinar 01 Nov – Big data with HDInsight & Datameer

If you are interested or currently looking to understand Bid Data using HDInsight, Datameer and Power BI then this Webinar is for you.

Find the details here: Nov 1 Webinar: Big Data with Power BI, HDInsight, and Datameer

Power BI – Advanced Analytics with Power BI Embedded and R

This blog post from the Microsoft Power BI team will show you how it works and how to integrate a Power BI Embedded report using R and advanced analytics.

I think that as times goes on, there will be more adoption in terms of embedding your Power BI reports into existing applications. And the requirement for advanced analytics will increase as more organizations understand and want to leverage their data investments.

You can find details here: Advanced Analytics with Power BI Embedded and R

Excel – Get & Transform Update for Oct 2016

In this months update for Get & Transform they have included some great features (which have already been implemented in Power BI)

The Query Parameters support is really valuable as it gives you the opportunity to make your data more dynamic, both in letting the user input what they require. As well as using a List of Values, which can also be dynamically created.

Also they have improved the Web Connector, so that you can then view what you are importing before you import it.

And finally the capability to Merge or Append queries. Where the Merge is a join type of query. Whilst the Append is a Union type of query.

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

BI-NSIGHT – Power BI (Featured Data Stories, Dashboard Makeover, Office 365 Adoption Content Pack, JIRA Content Pack) – Excel (Get & Transform Update)

Here are this week’s BI-NIGHTS updates.

Power BI – Featured Data Stories

There are some great data stories featured in the Power BI Data Stories showcase. I have to have a special mention to myself that I managed to get my Create Dynamic Periods for Fiscal or Calendar Dates and Dynamic Month Names for Charts also featured which I am really happy about.

You can find all the details here: Congratulations to this month’s Featured Data Stories Gallery submissions

Power BI – Dashboard Makeover

Due to one of the most popular webinars, the Microsoft Power BI Team are going to have another live webinar for the Dashboard Makeover.

It is scheduled for the 13 October 2016.

You can find the details here: Return of the Dashboard Makeover Webinar!

Power BI – Office 365 Adoption Content Pack

In this month’s new content pack from the Microsoft Power BI Team, they have created an Office 365 Adoption Content Pack. This does not only provide great insights into how your organization is adopting and using Office 365, but it also provides great insights into Yammer, Skype for Business, OneDrive for Business and SharePoint usage.

You can find all the details here: Announcing the preview of the Office 365 adoption content pack in Power BI

Power BI – JIRA Content Pack

Here is another content pack released this month, and this month it is JIRA. They provide bug, issue tracking, as well as project management capabilities.

So if you are an existing JIRA customer this can provide some great insights into your data.

Find the blog post here: Explore your JIRA Data with Power BI

Excel – Get & Transform Update

It is great to see that they are still updating the Get & Transform (Power Query) updates in Excel.

With this update they are the enhanced Web Connector with the ability to be able to control the command timeout.

And then also enhanced the CSV/Text connector for the support for fixed-width delimited files.

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

BI-NSIGHT – Power BI (Secure and Audit Power BI, Data Driven Parameters, Snowflake Data Connector) – Excel (Get & Transform Updates / Power Query Updates)

Here are the BI updates for this week.

Power BI – Secure and Audit Power BI

One of the questions that is regularly asked with regards to Power BI is around how secure the data is. And along with that is how do people gain access.

This new update goes a long way not only to make Power BI more secure. But also to have the ability to look at the audit logs and see who is doing what if required.

I have no doubt that this will go a long way in helping organizations secure their information, as well as be compliant from an audit perspective.

You can find the details here: Secure and Audit Power BI in Your Organization

Power BI – Data Driven Parameters

This is another great blog post by Chris Webb where he explains how to use the new updates in Power BI which enable you to now use Data Driven Parameters. So now you can dynamically have your requested parameters come from a list, which can be derived from an existing data set.

You can read up about it here: Data-Driven Power BI Desktop Parameters Using List Queries

Power BI – Snowflake Data Connector

In this blog post from the Microsoft Power BI team they explain what the Snowflake data connector is and how to connect and use it.

I am sure that it will be very welcome for the existing and potential Snowflake customers.

You can read up about it here: Power BI enables connectivity to Snowflake

Excel – Get & Transform Updates / Power Query Updates

IT is good to see that the Excel team has also found a way to keep on adding features and updates to the Get & transform or Power Query within Excel. I often find that they get released first to Power BI Desktop, but then they soon make it into Excel also.

This month there is a whole host of updates as shown below.

As you can see from above there are additional data connectors, as well as changes in the query editor. As well as finally making it easier to extract Date and Time functions from your existing columns. Which in the past can be rather tricky?

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