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”

Conclusion

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.

BI-RoundUp – Power BI (Nov On-Premise Data Gateway Update – Weekly Hot Topics – Project Online Content Pack)

After very quiet in the previous week, here is this week’s round up details.

Power BI – November On-Premise Data Gateway Update

Whilst it might not appear as though there is a lot with regards to the November update to the On-Premise data gateway there IS!

The first thing is that they have now implemented High Availability. This is really great news, because it means you now can have the On-Premise data gateways in a cluster. So that if one of the servers fail, it will automatically move over to another one.

It appears to be really simple to setup. And then when you are in the Power BI Service, all the gateways in a cluster, appear as one. Which makes it again really easy for people who are Gateway Administrators to just use the gateways as they previously did.

There is also now additional logging for Mashup Engine Traces, so that you can see what the Mashup Engine is doing.

Now if the On-Premise Data Gateway cannot connect via TCP, it will automatically fall back to HTTPS, making the gateway that much more redundant to ensure that your data gets refreshed.

And finally, the Mashup Engine for the On-Premise Data Gateway has been updated with the November version.

You can find more details here: On-premises data gateway November update is now available

Power BI – Weekly Hot Topics

There is now a weekly hot topics blog post from the Power BI team, which will highlight members from the Power BI community who have helped out the most.

Also, it highlights the Top 10 Viewed Threads and the Most Active Threads.

Go and have a look here: Weekly Hot Topics

Project Online Content Pack

As you can see from the screenshot above there now is the capability to use a Project Online Content Pack if your organization is currently using Project Online.

The content pack has got a lot of default reports and great information out of the box.

There have been quite a few additions to the previous version of the Content pack, so it would be a great idea to get this newer content pack.

You can find all the details here: A new Project Online Content Pack for Power BI

Power BI Embedded InfoGraphic

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

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

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

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

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

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

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

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

Nov 2017 Update to Power BI Overview Infographic

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

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

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

Bookmarks

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

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

Selection Pane

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

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

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

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

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

Lock Objects

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

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

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

Conclusion

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

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

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

Power BI – Nov 2017 Desktop Update

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

Reporting

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

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

Analytics

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

Data Connectivity

There is now support for Windows Authentication for the Impala connector

Query Editing

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

The additional items below are:

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

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

Power BI – Single Sign-On for Gateway Data Sources

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

Currently the supported data sources are:

  • SQL Server
  • Teradata
  • SAP HANA

And there will be future support for:

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

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

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

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

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

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

What is coming to Power BI Desktop

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

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

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

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

Additional Power BI Updates for the Roadmap

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

Power BI Desktop

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

Power BI Service

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

Power BI Report Server

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

App Updates

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

On-Premise Data Gateway Updates

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

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

Power BI – Client Advisory Team

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

BI-RoundUp – Power BI (Power BI Report Server GA Update – Announcements from SQL Pass – Help improve Power BI – How Data will Influence the future of Story Telling)

Here is my weekly BI RoundUp, it is a day late but I thought there might be more announcements from SQL Pass, so decided to delay it by one day. So here are this week’s updates.

Power BI – Power BI Report Server GA Update

With SQL Pass, there has been an announcement that you can now download and either update or install the Generally Available Power BI Report Server. There are some amazing new capabilities highlighted below.

  • Scheduled Refresh supported for data sources.
    • You can also schedule the refresh as often as you like.
    • As well as have multiple refresh schedules.
  • The size of the PBIX has been increased to 2GB
  • Additional support for DirectQuery for the following sources:
    • SQL Server Analysis Services
    • SQL Server
      – Azure SQL Database
      -Oracle
    • Teradata
    • SAP HANA
    • SAP BW
  • If you have a Shared Dataset in Reporting Services, you can now connect to this using the OData source in Power BI.
  • URL Report Filtering is now also available, which is very similar to the Power BI Service Implementation
  • Updates to the REST APIs for Power BI Report Server which can access folders, reports, KPIs, data sources, datasets, refresh plans, subscriptions, and more

You can find the download location and blog post here: New version of Power BI Report Server now available

Power BI – Announcements from SQL Pass


From the SQL Pass there are some updates coming to the Gateway. As you can see above there will now be the capability for Single Sign-On for Row Level Security which is great.

As well as high availability for the Gateways, I know that this is something larger enterprises, as well as organizations have been looking for to ensure that their data is always up to date. From the people that I follow on twitter this should be available very soon (Possibly Mid-Nov 2017)

As you can see above the capability to share specific items from an App Workspace is coming to Power BI.

Amazing, coming soon to Power BI will be the capability to share to an external email address or B2B (Business to Business). This will really be good when wanting to leverage your existing Power BI reports to external customers.

Power BI – Help Improve Power BI

There is a quick Survey where I was asked some questions with regards to Power BI. It is one page long, so it does not take a lot of time.

Along with this there is the opportunity to win 3 $50 gift cards, if you wish to leave your name and email address.

Here are the details here: Help Improve Power BI Desktop

Power BI – How Data will influence the Future of Story Telling

This is a great article in which they go on to explain how in the future a lot of storytelling will be backed by data. And how Microsoft Power BI is enabling people now to tell their story in an easy and meaningful way.

You can read all about it here: How Data will Influence the Future of Storytelling

Power BI – How to do Pagination in Power BI Reports

In this blog post I am going to demonstrate how to achieve Pagination in Power BI reports, using the new Bookmarking feature. I got this question from a follower on Twitter Murali Krishna

With the new bookmarking feature there now is a way to make it appear that you can page through Power BI reports.

Below is what it looks like in action.


Parameters

I used the following Parameter as shown below, which I then used later to automatically determine how many rows to show per page

How I got the Page Numbers based on the Rows Per Page Parameter

In this next section, what I did was to use my parameter with my data so that I could determine the page numbers dynamically.

By using the parameter this enabled the data to always get the right number of rows for each page dynamically.

  • This is what my data looked like at the start.
  • The first thing that I did was to add an Index by going into the Add Column ribbon and clicking on Index Column and then From 1
    • NOTE: The reason that I started at 1, was because when I am showing the rows it starts from 1
  • I then duplicated the Index Column, so that I could then use this to create the page number.
  • Next, I clicked on my column called “Index – Copyhighlighted below in Yellow
  • Then in the Transform ribbon I clicked on Standard and then Integer-Divide
  • I then manually put in a value of 10
  • Next, I wanted to change it from a static value to use my parameter value, so I did it by doing the following.
    • This is what it looked like with the Static value in the M Code
      Table.TransformColumns(#"Duplicated Column", {{"Index - Copy", each Number.IntegerDivide(_, 10), Int64.Type}})
    • I then changed it from 10, to my Parameter value, as shown below highlighted in PURPLE
      Table.TransformColumns(#"Duplicated Column", {{"Index - Copy", each Number.IntegerDivide(_, #"Rows Per Page"), Int64.Type}})
      • NOTE: The Parameter name is case sensitive.
  • The final steps was I added 1 to my column, so that once again the page number started from 1. And then I renamed it to reflect the page name
  • This is what it looked like once complete.
  • I now loaded this into my data model.

Creating the Pagination

I am not going to go into every step due to it will make this blog post too long, but I will demonstrate how I got the Pagination working.

  • The first thing that I did was to show the Bookmarks Pane.
  • Next what I did was to show my data based on the page number.
    • What this did is it enabled my data to be dynamic
      because it was based on the parameter, if I had to change how many rows to show to 25, due to the way I modelled the data in the Query Editor it would then have 25 rows per page.
  • I then created 4 bookmarks as shown below, with each being linked to a page number in the filter section.
  • I then also created measures to display the page that I am on.

    Pagination = “Page ” & SELECTEDVALUE(‘Sheet1′[Page Number],1) & ” of ” & [Max Page Number]

  • As well as the Records Measure

    Record Rows = ” Records ” & [Start Index] & ” to ” & [Max Index] & ” of ” & [Max Index Records]

  • Now the final piece, which does take some time to get right, was to enable the buttons to go forwards and backwards. So that when the user clicks on the buttons it appears that they are going forwards or backwards on each page.
    • I had to enable the Selection Pane Window
    • I had created an image for a Forward button and a Backwards Button
    • I then inserted my Forward Image.
    • NOTE: Always make sure that you are on the Bookmark Name where you want the interaction to take place.
      • I then clicked on the Forward Image and went to the Properties.
      • I then set the properties for the Link to type Bookmark and to Page 2 as shown below.
      • Then I went to the Bookmark called Page 1 and clicked on Update
      • I then went to the Bookmark called Page 2
        • I then went to the existing image and clicked on Hide
        • I then imported my Forward Image again.
        • I then clicked on the Forward Image and went to the Properties.
        • I then set the properties for the Link to type Bookmark and to Page 3 as shown below.
        • I then imported my Backward Image.
        • I then clicked on the Backward Image and went to the Properties.
        • I then set the properties for the Link type to Bookmark to Page 1 as shown below.
      • I then had to repeat this for all the other pages, as well as hiding the images on the different pages.
      • As well as ensure that the images were in the identical place as per the other images. This made it appear as it was the same button.
  • I then finally uploaded this to the Power BI Service and tested it to ensure that it worked as expected.

Conclusion

As you can see I demonstrated how to do Pagination in Power BI, it is a bit complex to get it right. But what I really like is that when it is done right, it appears seamless to the end user and VERY easy to use. Which is always my goal to make the end result as simple as possible.

Below is the report in action, if you want to click through and test it.

BI-RoundUp – Power BI (Choosing the best licensing for you and your Organisation – On-premise data Gateway Update – Developer Community Update – Duplicate Workspaces using the REST APIs

Here is the weekly BI Roundup

Power BI – Choosing the best licensing for you and your Organisation

In this blog post from the Microsoft Power BI team, they go through the different licensing options that are available.

They also go through the differences between Power BI Pro and Power BI Premium.

As well as give some answers to the common questions that are asked when evaluating the Power BI licensing options.

You can read more about it here: Power BI Pro & Power BI premium: Flexibility to choose the licensing best for you and your Organisation

Power BI – On-premise Data Gateway Update for October

In this month’s update includes the updated version of the Mashup Engine (Query Editor Engine).

As well as some new beta connectors (Vertica)

You can find more details here: On-Premise data gateway October update is now available

Power BI – Developer Community Update

In this blog post, there are details around new functionality and changes for the Power BI Developer.

There are some great details around Power BI Embedded, where they explain the difference scenarios on how to leverage Power BI Embedded.

I know that this can get confusing as there is Power BI Embedded with an “A” SKU, an “EM” SKU and “P” SKU. They go through this really well so that it is easy to understand which SKU to use in which scenario    .

There is now also new functionality to embed Q&A. This is great if you want to provide the Q&A functionality within your app. The interactive mode is where you use it as you would in the Power BI Service, whilst in the Results only mode where the user will only see the result of the Q&A question.

There is now added support to set new filters dynamically for the Relative Date Slicer, the Include or Exclude as well as the TopN filters.

Finally, there is an update to the Custom Visuals API which is now on version 1.8

You can find more details here: Power BI Developer community October Update

Power BI – Duplicate Workspaces using the REST APIs

This update relates to how you can duplicate as well as move content around to new App Workspaces using the Power BI APIs

They even provide a sample PowerShell script, where all that you need to do is to put in your ClientID which you get when you register an app and you are good to go.

There are some limitations currently, but what this does allow you to do, is to be able to copy content to different App Workspaces, as well as be able to change the source connections. So this can work if you have a Dev App Workspace and a Prod App Workspace, and you can transfer the content across changing the source connection from Dev to Prod.

You can find more details on how to use the APIs here: Duplicate workspaces using the Power BI REST APIs: a step-by-step tutorial