This month we have major updates across all areas of Power BI Desktop. Along with many other reporting features, we have our biggest update to conditional formatting in while, the ability to format any fields, including strings and dates, by a different numeric field in the model. Drillthrough also gets a major update this month with the ability to carry all filters through to the destination page. We are also enabling enterprise level scalability through incremental data refresh.
Here’s the complete list of May updates:
- Conditional formatting by a different field
- Advanced slicer syncing
- Log axis improvements
- Data label options for funnel chart
- Set line stroke width to zero
- New “From Web” connector – By example data extraction (preview)
- Common Data Service for Apps connector (beta)
- Azure KustoDB connector (beta)
- Google BigQuery and Azure HDInsight Spark connectors now generally available
- Adobe Analytics connector update – Support for multiple domain logins (preview)
- Visual Studio Team Services connector update – Analytics views support
- OLE DB connector update – Support for alternate Windows credentials
- SAP BW DirectQuery connector update – Improved technical name support
This month for our summary video we are featuring Adam Saxton from the Power BI CAT team! You can watch it here:
Before jumping into the details, we also want to encourage you to register for the Microsoft Business Applications Summit in July! There are going to be many great Power BI sessions and tons of chances to interact directly with the Power BI team.
Reporting
Conditional formatting by a different field
We are very excited to announce a major improvement for our conditional formatting experience, the ability to formatting a column by a different field in your model.
Now, whenever you open the conditional formatting dialog, you’ll see two new dropdowns. The first, Color based on, is where you can pick what field from your model to base your rules on, and the second, Summarization, is where you’ll pick the aggregation type for that field. There is also a Apply color to box that lets you know what field in your table or matrix is being formatted currently.
The field and summarization type to color by are auto-populated with the same column in your table you’ve chosen to format, so you won’t have to do any extra configurations unless you want to customize it.
When you do customize the field for the color to be based on, you’ll get a similar experience to the field list where you can expand and collapse tables in your model and search to help you find the field you’re looking for.
In the below example, I’m using showing the total sales by product and coloring that column by the average net satisfaction of the product.
You can also pick a new source of formatting when using the Color by rules version of conditional formatting.
With this update we are also opening up conditional formatting to text and date fields as well, as long as you choose a numeric value to format on.
A common use case for this would be to create a measure in your model that does your custom business logic, such as comparing target vs. actuals and the use Color by rules to format the text based on the result. For example, you could create a measure to return a -1, 0, or 1 if a product is under, at, or above its sales expectations, and then in Color by rules mode chose to format the product name red if the measure returns -1, yellow if it returns 0, and green if it returns 1.
Advanced slicer syncing
here's now an Advanced section of Slicer syncing pane that allows you to create custom groups of slicers to sync. By default synced slicers will be put a group with a name that matches the field used in the slicer but you can override this with any name you want. This means you can create separate groups to sync slicers that use the same field; for example you’ve got two slicers using the same field on the same page, and you want to sync one of them with a slicer on another page.
You can also put slicers that use different fields in the same group; for example you’ve got two different date fields and you want to have the selected date sync between the slicers. If the value selected in the first slicer doesn’t exist in the second slicer, you’ll see it appear at the bottom of the list of values.
You can also check the Sync field changes to other slicers. With this option checked, if you swap the field used in the slicer, all the synced slicers will update to use the same field.
Log axis improvements
While we’ve supported log axis for a very long time, the support has been inconsistent across charts and could sometimes be hard to use. Hearing your feedback, we’ve greatly improved log axis in your cartesian charts.
You should now be able to select log scale for the numeric axis of any cartesian chart, including combo chart, when you have data that is completely positive or completely negative. Reference lines should also observe the log scale and will cause the scale to resize to accommodate them.
If you have data that crosses zero, you’ll now get a helpful warning in the property pane, and the scale type will revert to linear. Once your data is updated to no longer include both positive and negative values, the visual will start to use the log axis again.
Data label options for funnel chart
For this month’s formatting feature, we’ve added more control for data labels in funnel chart. You can now pick if each bar shows the actual value, the percentage of the first bar, the percentage of the previous bar, or the actual value and one of the two percentage options.
The first bar will always show only the data value since the percentages are always 100%.
Set line stroke width to zero
Another small formatting option we are adding this month is the ability to set the stroke width of lines to 0.
This works for combo charts as well as line charts.
Analytics
Measure drillthrough
Back in September, we released a drillthrough experience that allows you to move from one page to another carrying specified filters through. You’d set this up by creating your drillthrough page (i.e. the page you want to land on) and then adding any categorical fields to the Drillthrough filters bucket in the filter pane as you want.
Any fields you put in this bucket would be tied to the page. Any chart in the rest of the report that uses that field could be right-clicked on to move to the drillthrough page.
This is a very powerful feature but was limited that only filters on the fields explicitly placed in the Drillthrough filters bucket would be carried through. There was no way to carry the entire filter context of your data point through the drillthrough page.
This month, we are closing this gap by allowing you to carry all filters through. When you are setting up your drillthrough page, if you want all filters to pass through and not just the fields placed in the bucket, you can turn the Pass all filters toggle on.
Once the toggle is on, whenever you right click on a data point and drillthrough, all filter context from the source page is passed to the drillthrough page. You’ll be able to see a restatement of all these filters in the Drillthrough bucket.
With the toggled turned off, only filters on the columns specified will be carried through.
With this drillthrough update, you can also now use measures and summarized numeric columns in the drillthrough bucket. You can pick if you want to allow drillthrough on numeric columns when used as a category (e.g. a chart showing number of reviews by rating) or summarized (e.g. a chart showing average rating by category).
The Pass all filters option is on by default for new drillthrough pages and off by default for existing pages.
You’ll also see this new experience for report page tooltip.
Power BI Premium
Incremental refresh (preview)
Incremental refresh will unlock very large datasets in Power BI Premium. You can define the refresh policy in Power BI Desktop to determine how data is incrementally refreshed when published to the Power BI service. Refreshes are faster, more efficient and more reliable because only the new data needs to be refreshed.
To leverage incremental refresh in the Power BI service, first filtering needs to be done using Power Query date/time parameters with the reserved names RangeStart and RangeEnd.
With the parameters defined, you can apply the filter by selecting the Custom Filter menu option for a column.
And then ensure rows are filtered where the column value is after or equal to RangeStart and before RangeEnd.
Once this is set up, you can Close and Apply the Power Query Editor. You can then set up your refresh policy by right clicking on the table and selecting Incremental Refresh.
The incremental refresh dialog includes several options:
- How long to store data for
- The length of time to refresh data for
- If the data should refresh only on data changes based on a date/time column in your data
- If only completed periods should refresh
This is a preview feature, so you will need to enable it under File > Options and Settings > Options > Preview features. This feature is a Premium feature currently, so it will only work if you publish to a premium workspace. We will eventually enable this feature for Pro users as well, but for now, it is limited to Premium.
There are some things to be aware of when using incremental refresh:
- Once you set up incremental refresh, it will not be possible to download your .pbix from the Power BI service
- Your first refresh may take a little longer while we load the historical data, but subsequent refreshes will be much faster
- Since re-publishing reports overwrites the entire dataset, you will need to reload the historical data again on the next refresh after the re-publish
Please see this article for more detailed information on how to use incremental refresh.
Custom visuals
Collage by CloudScope
The Collage by CloudScope custom visual displays images in either a compact grid display or a larger detail view in a style similar to Instagram.
In addition to displaying an image from an image URL, you can optionally display any of the following field types:
- Caption
- Media date
- Media type
- Video url
- Comment count
- Like count
- Permalink
- Author profile image
- Author name
- Caption sentiment
In addition, you can provide any generic data fields that you wish and will automatically be formatted in the familiar Instagram style for numbers and dates.
Download this visual from AppSource.
Chinese Color Map
If you are using geographical data for China, you might want to try out the Chinese Color map custom visual. This map has several features:
- Offline maps
- One-click switching between a national China map and provincial maps
- Rich visual formatting including shading, colors, and patterns
- Arbitrarily set the latitude and longitude of the area you want to display
- Gradient fill for regions on the map
Try out this visual on AppSource.
Data connectivity
New “From Web” connector – By example data extraction (preview)
One of the most differentiating connectors in Power Query is the From Web connector. This connector allows you to easily scrape data from HTML tables and import them into Power BI Desktop.
With this month’s release, we’re dramatically enhancing this connector by allowing any HTML data, not just tables, to be extracted. To do so, you can provide a few samples for the data that you want to extract, and Power Query will apply smart detection algorithms on top of the HTML page content to identify the entire set of rows to generate.
To try out this feature you need to enable it under the Preview Features tab in the Options dialog.
After enabling it, you can find the Web connector within the Other category in the Get Data dialog.
You can specify the URL of the webpage from which you want to extract data. For example: https://www.microsoft.com/en-us/store/top-paid/games/xbox?category=classics
You will be taken to the Navigator dialog where you would see a number of auto-detected tables (HTML tables). In this case, none of them were found since the data in this webpage is not exposed as HTML tables. With the new Web By Example enhancements, you can now access the “Extract table using examples” button in this dialog.
This option brings you into an interactive experience where you can preview content in this page and specify sample values for what data you would like to extract.
For example, in this webpage we would like to extract the Name and Price for each game. We can achieve that simply by specifying a couple of examples for each column, like showed in rows #3 and #5 in the following screenshot. After specifying these samples, Power Query is able to detect the rest of values to extract from this webpage based on smart data extraction algorithms.
Once you are happy with the data extracted from the webpage, you can click OK, which will take you into the Query Editor, where you can apply further data transformations and filters, or combine this table with data coming from other data sources.
Over the next few monthly releases we plan to enhance this new Web By Example experience in a couple of ways:
- Allowing you to add one or more tables in a single pass, either multiple By Example tables, or a combination of auto-detected and By Example tables.
- Allowing you to specify examples by clicking at the relevant content in the Web Page preview, within the Add Table By Example dialog, instead of requiring you to type in the examples.
We’re excited about making this new Preview feature available to all of you. Last year, we released Add Column From Examples, which also allows you to apply data transformations based on sample output values on top of any table in the Power Query Editor.
With the new Web By Example feature we’re giving you an initial Preview of how Smart Data Extraction algorithms can be applied on top of semi-structured data sources like HTML webpages. We’re lining up lots of Smart Data Preparation capabilities within Power Query for many different scenarios. Expect to hear more details on this area very soon! In the meantime, we encourage all of you to try out this new Preview feature and share feedback regarding your experience, sample input webpages, etc. to help us make the feature even more powerful!
Common Data Service for Apps connector (beta)
We’re adding a new connector this month to enable you to import data from you Common Data Service for Apps.
Common Data Service for Apps allows you to securely store and manage data that's used in apps you've developed or apps from Microsoft and app providers. Data within CDS for Apps is stored within a set of standard and custom entities. An entity is a set of fields used to store data similarly to a table within a database. You can learn more about the Common Data Service for Apps in this article.
The Common Data Service for Apps connector is available under the Online Services category in the Get Data dialog.
Azure KustoDB connector (beta)
Azure Kusto is a big-data, interactive analytics platform that provides ultra-fast telemetry search and advanced text search for any type of data. Kusto is perfect for IOT, troubleshooting and diagnostics, monitoring, security research, usage analytics, and more.
Azure KustoDB is currently in Preview phase. If you haven’t signed up yet, you can do so from: http://aka.ms/kustopreview
With this month’s release of Power BI Desktop, we’re adding a new connector allowing you to connect to your Azure KustoDB data in order to analyze it and build reports.
The new Azure KustoDB connector can be found under the Azure category within the Get Data dialog.
After selecting this connector, you can specify a Kusto Cluster and, optionally, a Database and Table or query to retrieve data, as well as other advanced options. You can then establish a connection using either Import or DirectQuery mode against your Kusto cluster.
Google BigQuery and Azure HDInsight Spark connectors now generally available
We’re very glad to announce that two of the connectors that have been in beta for the past few months have now reached general availability.
The Google BigQuery connector can be found under the Database category within the Get Data dialog. You can find more details about this connector in this previous article.
The Azure HDInsight Spark connector is located under the Azure category in the Get Data dialog. And you can check out our documentation to learn more about this connector.
We want to thank everyone who has tried these two connectors and shared feedback with us during their beta phase for helping us taking the connectors into GA.
Adobe Analytics connector update – Support for multiple domain logins (preview)
Based on customer feedback, we’re enhancing the Adobe Analytics connector in order to allow you to pull data from multiple domains.
In order to try out this connector enhancement, you need to enable it from the Preview Features tab within the Options dialog.
Upon enabling this Preview feature, you will be able to see multiple companies as top-level nodes in the Navigator dialog when using the Adobe Analytics connector.
Visual Studio Team Services connector update – Analytics views support
We are excited to announce an easy-to-use solution for integrating Power BI with VSTS Analytics. The new Analytics views feature in VSTS makes getting work tracking data into Power BI simple, and it works for the largest accounts. Similar to a work items query, an Analytics View specifies filters that scope the result of work items data and the columns. Additionally, views allow you to report on past revisions of work items and easily create trend reports.
VSTS provides a set of Default Analytics views that work well for customers with smaller accounts and basic scenarios. Larger accounts can easily scope your data and history to exactly what you want to report on in Power BI. To learn how to create your own views, check out the dedicated VSTS blog.
Analytics views you create in the Analytics hub in VSTS are immediately available to select from the VSTS Power BI Data Connector.
OLE DB connector update – Support for alternate Windows credentials
In this month’s release, we’re enhancing the authentication options for the OLE DB connector, by adding the ability to provide alternate user credentials when using the Windows authentication type.
SAP BW DirectQuery connector updates – Improved technical name support
When connecting to SAP BW, it was previously possible to see the SAP BW technical name of the dimensions, hierarchies and measures in the navigator (as well as the longer friendly name). With this month’s update, it is also possible to see those same technical names within the field list when connected using DirectQuery. These names will appear in the Description property for the relevant field, as well as in the tooltip caption for the field.
Data preparation
Improvements to Add Column From Examples
“Add Column From Examples” enables you to easily define new columns that derive data from existing columns based on data transformations, by providing a few examples of the expected output values and allowing Power Query’s smart detection logic to automatically infer which transforms should be applied.
This month we’re making significant enhancements to Add Column From Examples:
Composition of Data Transformations
With this month’s update, we’re making it possible for you to derive new columns from examples that require the composition of multiple column transformations. For example, extracting from Full Name and Job Title columns the Name initials followed by the uppercased version of the job title in parenthesis.
Domain specific transformations
We’re enhancing the set of supported data transformations in this feature by including specialized, domain-specific transformations, in Power Query’s smart detection logic, such as additional Date extraction (5/8/2018 -> MAY-2018) and other similar transformations.
That’s all for this month! We hope that you enjoy these updates and continue sending us your feedback. Please don’t forget to vote for other features that you’d like to see in the Power BI Desktop. You can also download the .pbix file I used, and if you’re looking for a similar design for your reports, I was using the Sunset layout from PowerBI.Tips.
Finally, don’t forget to register for the Microsoft Business Applications Summit in July! I’m excited to meet many of you there.
