How to Edit Power BI Live Connection Without Destroying Everything
Welcome to the Power BI Monthly Digest for May! You may remember we just did an episode a couple weeks ago – that was for the April updates that were released in May by the Power BI team. There are some new features and enhancements to tell you about. As always, be sure to check out our video included at the bottom of this blog for a brief demo of the newest updates.
1. Featured Tables for Excel (in preview) – In this new addition, you can take a table(s) inside your data model and mark them as a featured table. By marking tables as such, it allows you to use Excel to connect into Power BI data sets.
For example, if you have a customer table that contains all the details of your customers for your business. You publish that table to the Power BI Service and set it up as either a shared or certified data set. Then users that are in Excel can tap into those data sets. So if you have a customer table in Power BI and an Excel sheet that has some info about customers, and you want to pull in other information you don’t have in Excel, a phone number for instance, you can tie that table and spreadsheet together.
This does require you to have the Insider Version of Excel. In our demo, I’ll show you how to turn on the featured table in Power BI.
- You go into the model view in the Power BI Desktop and select the table that you want to make a featured table.
- Then go over to Properties and toggle on the new Featured Table option.
- When you toggle that on, it opens a box where you can mark that selected table to be featured in Excel. You’ll need to identify a row label and key column. This will now be able to map something like a customer’s email address for example that showed up in your spreadsheet to a customer’s email address in your data model.
2. Filters Pane – The new filters pane is officially here! Any old reports you have where you’ve used the old filters pane have been migrated with the new filters pane.
They’ve also added a nice new feature called apply all filters. In the past, when we put multiple filters on a page, you had to click every time you needed to apply a filter to a visual.
This new feature will help optimize the filter pane for query reduction by adding a single apply button to the filter pane and allowing you and your end users to apply all filter modifications at once.
You’ll need to turn this on by going under File and Options. Under Current File, select Query Reduction and click on ‘Add a single Apply button to the filter pane to apply changes at once’. You will have to turn this on for every file you load as the default set is to instantly apply basic filter changes. Watch the video for a demo on this feature.
3. Drill Through Buttons - They’ve made it easier to use buttons to navigate to drill throughs. A couple months ago, Power BI introduced an new action for buttons in which you could do drill throughs and navigation. Now they’ve added 2 new features with those buttons.
We can do conditional formatting based on what filter page to take you to and the ability to have the button change during that process, making it easier for people to see that they can click that button for a drill through to happen and whether it’s been enabled/disabled, hovered over, etc.
- We start by adding a button and we want to make this button change how it looks based on different states.
- Under Visualizations, go into Button Text and toggle that on. We add Click Me as the text and that will be the default state. But now we want to change this button based on how/when it’s being used.
- In our example we’ll make this a drill through button by going under Action and choosing Drill Through as the type and select the Destination, which in our case is the details page.
- When we do this our button is now shaded a light grey, but when a part of a visual is clicked on, the button appears more obviously so users know they can click there to drill through to another page.
- With the new update to this we can make this clearer to users. To do this we’ll change our button text (Click Me) from default state to disabled. Now we can change the text to show our users when they can use this button.
- In our demo we change the button text to Select a Country and we can change the font color and size to make this stand out. There are many options for making this button stand out, like the outline or fill color, but be sure to select disabled instead of default whenever you make any of those changes.
- Now the button tells users to Select a Country when nothing is selected. Once they select a country on a visual, the button will change to Click Me. A simple way to use conditional formatting with buttons to make it very clear to users what they can do on a visual.
4. Data Connection into Web Pages – They’ve made a small update here that changes the default view.
- To show this, we go into Get Data and select Web and add or paste in the web page we want to get data from.
- The difference is now table view is the default selection. So, the Navigator is going through the web page looking for any tables and we can click on any table to preview what they look like on that web page. And you can click on web view tab to see the actual web.
- Bottom line with this is Power BI changed the default to table view as this is what most people want to see, so they made it the first option.
5. More Button Updates – Bookmarks are great, but sometimes you want to use a button for end users to click to get to another page. In the past, buttons allowed for page navigation to only one page for a button.
Now you could have something like a slicer to show the names of your pages and users could select the page they want, and the button could get them to that specific page.
- In our example, I add a ‘Go to Page’ button.
- For action, I select page navigation as my type and in the past, we could only select a single destination for that button.
- With this new change, we can make a table with the names of our pages that we want the button to go to. To do this, I go to Enter Data from my tool bar and create a table.
- Be sure to note what you name your column in your table as that will need to be exact when you pass that into an expression.
- Click to load that table and make that page navigation table into a slicer. I make that a drop down of the pages in my table, which in this case I named Nav 1 and Nav 2.
- Next, I go back to my button action fields and I’m going to put in a dynamic destination. I set up the destination based on the table I created and my page navigation column.
- Now when I go to the Page Navigation slicer, I can choose whatever page I want from the drop down (Nav 1, Nav 2) and click the Go to Page button and it will take me to that page.
Another cool addition that we show in our demo is you can now add shadow boxes or drop shadows around a visual. This quick update can be done by clicking on a visual and under formatting, you simply turn on shadow and you can do some neat things like choose the shadow color and its position (inside/outside, upper left, etc.). This can add a nice pop of a shadow behind a visual border, especially when you have multiple visuals on a page.
6. Change Detection (a Premium only feature) – This release was announced in the April release and it was all about being able to have page refresh for DirectQuery sources based on a detected change instead of a fixed refresh interval.
They have added to this to improve and optimize the performance of a change detection measure. One new thing is you can preview the behavior of your change detection measure in the desktop, just like with auto page refresh.
The other enhancement is the capability to analyze queries beyond visuals with change detection. You’ll do this using the Performance Analyzer which you’ll need to open through the View tab and turn on change detection in the page refresh section in the page formatting pane. We give a quick peak at this in our video but gave this a bigger highlight in our last video, so you may want to check that out to learn more.
7. Decomposition Tree – There are some new additions on this visual to make it more interactive. One new thing is drill through capability. Now you can right-click on a data bar in the tree, select drill through and it will take users to your drill through page based on the decomp tree.
Another thing added is conditional formatting. Simply select the decomp tree and under formatting go to conditional formatting. I click on data colors and advanced controls. I can then conditional format this based on another measure. I create a measure based on profit margin and I select that as my based-on field and make it diverging in color.
So, my decomp tree is displaying sales amount, but my data bars can be displaying based on another measure. We also have cross filtering capabilities so we can enable our decomp tree to cross filter with other visualizations.
Lastly, there were quite a few new data connectors released. There was an update to Dataflows, that data connector now can do DirectQuery.
That’s it for this month’s release. You can learn more about any of these features in Power BI’s blog. If you need training on Power BI, whether you’re just beginning or want to dig into more advanced topics, our On-Demand Learning platform has 18 deep-dive Power BI courses as part of our extensive library.
If you’re looking for Power BI training, our On-Demand Learning platform is the best in the business. Taught by Microsoft MVPs and industry experts, we have 18 courses covering Power BI from Intro to Advanced. Click below to get started with a FREE 7-day trial, plus you’ll get our Dashboard in a Day course free for life!
And be sure to subscribe to our blog and YouTube channel for all the Power BI updates and lots of free training. See you in June!
Sign-up now and get instant access
ABOUT THE AUTHOR
Devin Knight is a Microsoft Data Platform MVP, Microsoft Certified Trainer, and President of Pragmatic Works. He focuses on driving adoption of technology through learning. He is an author of nine Power Platform, Business Intelligence, and SQL Server books. He has been selected as a speaker for conferences like Power Platform Summit, PASS Summit, SQLSaturdays, and Code Camps for many years. Making his home in Jacksonville, FL Devin is a contributing member to several local user groups.
Free Trial
On-demand learning
Most Recent
private training
Leave a comment