We are very excited to announce that as part of our September update, we have a preview of aggregations, which allows you to seamlessly store only summarized values in memory without having to load your entire dataset into Power BI, greatly improving the performance of your reports. This month also includes a preview of a PDF file connector, which is the most requested connector on UserVoice; and one of our biggest updates to our advanced query editor in quite a while, M intellisense.
Here’s the complete list of September updates:
- Dot plot layout support in scatter charts
- Copy value and selection from table & matrix
- Built-in report theme options
- Report page tooltips generally available & Card support
- Accessibility improvements for analytics and formatting pane support
- PDF file connector (preview)
- SAP BW connector – support for measure properties
- Dataflows connector (beta)
For a summary of the major updates, you can watch the following video:
Reporting
Dot plot layout support in scatter charts
We have a substantial update to our scatter chart this month. You can now use categorical fields on the x-axis of your scatter chart, allowing you to create dot plots.
You don’t need to do anything special to enable this feature. Just add your categorical field to the x-axis and remove anything you may have in the detail’s bucket.
You can this in combination with all of our formatting features, and of course it works with drilling on the x-axis.
Let us know what you think and if there is anything you’d like to see added to this new visual layout!
Watch the following video to learn more about the dot plot layout:
Copy value and selection from table & matrix
We’ve heard from many of you that you need to copy data out of Power BI into other applications like Dynamics CRM, Excel, and even other Power BI reports. Our first feature related to this is the ability to copy a specific value or a selection of data from a table or matrix. You’ll be able to find both of these options on the right-click menu.
Copy value will add the unformatted value to your clipboard, and from there you can paste it wherever you want. For example, copy value from this cell in my table will add 32130 to my clipboard.
Copy selection on the other hand will add a tabular version of all data currently selected in your table or matrix to the clipboard, including the row and column headers. The data will retain the data formatting that has been applied and subtotals are excluded. This option is very useful if you want to reuse a selection of data in another report in Excel for some quick calculations.
For example, if I have the entire Economy column selected in my matrix and pick Copy selection…
… I’ll get all this data in a tabular format when pasting into Excel.
Note that when you have multiple measures in your matrix, they are always selected together, so you’ll always get both when copying selection. For example, copying the below selection…
… Will add the following to your clipboard.
This is just our first copy related feature, and we want to hear your feedback on this release. Please let us know if there is anything you’d like to see added to copy value or selection!
Watch the following video to learn more about copy value and selection:
Built-in report theme options
Over the summer, the Power BI Desktop team had several great interns who worked on some exciting features. The first of these intern projects to ship in the Power BI Desktop is a themes gallery in the Desktop that lets you pick between several built-in report themes.
You’ll be able to see these new theme options under the Switch Theme button in the Home ribbon tab. In addition to the default theme, you can now pick between 7 new options.
All you need to do is pick the theme of your choice and it will automatically apply its color palette to all your visuals using the default color palette. There is now also a quick link the theme gallery on our community site if you want to try out a theme built by our great community!
This project was done by our intern Chelsie Eiden, thank you Chelsie for your great work!
Watch the following video to learn more about built-in themes:
Report page tooltips generally available & Card support
This month, we are making report page tooltips generally available. This means you’ll be able to use the feature without needing to enable it in the Options dialog. To go along with making it generally available, we are also adding support for report page tooltips on the Card visual.
Watch the following video to learn more about report page tooltips in cards:
Accessibility improvements for analytics and formatting pane support
The Power BI team is dedicated in making sure data is accessible to everyone, and as part of that, we are invested in improving the user experience for those who are using assistive technology. This month, as part of that larger effort, we added keyboard and screen reader support for our formatting and analytics pane. If your focus is in the formatting pane or analytics pane, you’ll be able to tab and shift+tab between all the controls, hear useful information read out by a screen reader for any given control, and change the settings using arrow keys and space/enter depending on the control type. We’ll add support to the other areas of the pane, including the field well and filter pane in the coming months as well.
Just like any other accessibility improvement, this update improves the experience for all users as well. I expect many of our power users will find the keyboard navigation will allow them to work a bit faster when making many changes to a visual’s formatting all at once.
Watch the following video to learn more about accessibility in the formatting and analytics pane:
Analytics
Aggregations (preview)
Aggregations are a way to store only summarized values in memory, to provide the fastest access to aggregated data without having to load your entire dataset into Power BI. A trillion rows from a Hadoop store might take up ¼ petabyte, but caching the values aggregated up to a daily level might reduce that to a few gigabytes which can be cached in memory much more easily. The magic is that this is seamless to the report users – as they slice further down to the detail level, if Power BI doesn’t have the answer in memory it automatically passes the query down to the underlying source using DirectQuery. Using aggregations can dramatically reduce the cost of unlocking large datasets for decision making in enterprise organizations.
Some benefits of aggregations include:
- Query performance over large datasets. As you interact with visuals on Power BI reports, DAX queries are submitted to the dataset. Boost query speeds by caching data at the aggregated level using a tiny fraction of the resources that would be required at the detail level. Unlock big data in a way that would not otherwise be possible.
- Data refresh optimization. Reduce cache sizes and refresh times by caching data at the aggregated level. Speed up the time to make data available for users.
- Achieve balanced architectures. Allow the Power BI in-memory cache to handle aggregated queries, which it is generally very good at. Limit queries sent to the data source in DirectQuery mode, helping stay within concurrency limits. Queries that do get through tend to be filtered, transactional-level queries, which data warehouses and big-data systems normally handle well.
I’ll walk you through an example to show how aggregations are set up. Let’s say we have a model where all the tables are DirectQuery. The Sales fact table contains billions of rows. Setting the storage mode of Sales to Import for caching would consume considerable memory and management overhead.
Instead, we create the Sales Agg table which will become our aggregation table. It is at a higher grain than the Sales table, so it will contain far fewer rows. The number of rows should equal the sum of SalesAmount grouped by Customer, Date and Product Subcategory keys. Instead of billions, this might be millions of rows, which is much easier to manage.
The dimension tables most commonly used for the queries with high business value are set up so the tables that can filter Sales Agg using one-to-many (or many-to-one) relationships. In our case that includes the Geography, Customer, Date, Product Subcategory, and Product Category tables. Other relationship types such as many-to-many or multi-source are not used for aggregations.
Now that we have our table set up, we’ll set the storage mode of Sales Agg to Import to speed up queries.
When we do this, we’ll be setting the storage mode to Dual, since we are mixing DirectQuery and Import sources. Setting the storage model to Dual allows the related dimension tables to act as either Import or DirectQuery depending on the subquery.
Queries that aggregate metrics from the Sales Agg table, which is Import, and group by attribute(s) from the related Dual tables will be returned from the in-memory cache. However, queries that aggregate metrics in the Sales table, which is DirectQuery, and group by attribute(s) from the related Dual tables will be returned in DirectQuery mode. The query logic including the group by operation will be passed down to the source database.
Next, we define the aggregations by selecting the manage aggregations context menu for the Sales Agg table.
The Manage aggregations dialog shows a row for each column in the Sales Agg table, where we can specify the aggregation behavior.
After this, queries submitted to the Power BI dataset that refer to the Sales table are internally redirected to the Sales Agg table. This means you can hide the Sales Agg table and other users of the dataset don’t need know the Sales Agg table even exists.
Watch the following video to learn more about aggregations:
Q&A support for RLS
You can now use Q&A and Row Level Security, which lets you configure your datasets and reports to only show rows of data that the current user should have access to, on the same dataset. This means that your users will be able to ask natural language question over these datasets, and they will only see results and suggestions that the RLS rules allow them to see. You can start testing it out now in Power BI Desktop using the View as Role experience while using the Q&A explorer dialog or Q&A for report creation. It’s rolling out to the Power BI service as we speak so you’ll be able to enable it for shared models later in the week.
Custom visuals
Horizon Chart
The Horizon Chart custom visual allows you to visualize partially overlapping line charts to show changes in distribution over time. It’s effectively a small multiple area chart that allows for greater precision in a vertical space. The visual also allows you to using diverging color scales to differentiate positive and negative values.
You can download this visual or a sample from AppSource.
Watch the following video to learn more about the Horizon Chart custom visual:
Text Enhancer by MAQ Software
The Text Enhancer by MAQ Software custom visual allows you to go beyond plain text. You are able to add text shadow, rotation, angling, and skewness in addition to the all the standard text styling options you’d expect. The custom visual works with both static and dynamic text.
You can find this custom visual on AppSource.
Watch the following video to learn more about the Text Enhancer by MAQ Software custom visual:
Advance Card
The Advance Card custom visual is an enhanced version of the numeric card visual available in Power BI. It allows you to display numeric values and enhance it with conditional formatting, stroke & border control, a prefix and postfix, and other formatting controls.
You can download this visual on AppSource.
Watch the following video to learn more about the Advance Card custom visual:
Multi KPI
The Multi KPI custom visual allows you to easily show your main key performance indicator (KPI) along with supporting information. The visual has the ability to show sparklines, KPI trends over time, and interpolation and auto-scaling to get rid of noisy points.
Try out this visual on AppSource.
Watch the following video to learn more about the Multi KPI custom visual:
Custom visual API updates
In case you missed the announcement on our blog last month, we are making some changes that affect older custom visuals. Our newest API for custom visual creation is above and beyond better than our earlier versions, and since we the majority of custom visuals have been migrated over to using the new API, we’ve decided now is the best time to start deprecating the older version. If you author a report that has an older custom visual using deprecated API, you will see a warning icon suggesting you update the visual. You can learn more on our dedicated blog post.
Data connectivity
PDF file connector (preview)
This month we are releasing a preview of the PDF Connector, which has been a huge ask from the community. In order to try it out you will need to enable it under the Preview features list in the Options dialog.
After enabling this Preview feature and restarting Power BI Desktop, the PDF File connector will appear under the File category in the Get Data dialog.
After selecting this connector, you will be prompted to specify a path to a PDF file. Once the file is specified, Power Query will extract tables automatically and present them to you in the Navigator dialog, where you can preview and select one or multiple tables.
We look forward to your feedback on this Preview feature and to making further enhancements to it in future releases. Please stay tuned!
SAP BW connector – support for measure properties
A new capability added this month to the SAP BW Connector allows you to select additional measure properties when using the SAP BW connector in Import mode. Within the Navigator dialog, you can now select one or multiple measure properties.
In the future, we plan to add this capability to SAP BW DirectQuery mode as well.
Dataflows connector (beta)
Dataflows is a feature that about to be released for limited preview. With dataflows, you can connect to multiple data sources and prepare their data for use in Power BI.
Dataflows are an integrated part of the Power BI suite – the dataflows desktop connector is now a part of Power BI Desktop allowing you to seamlessly connect to data carpeted by dataflows.
Dataflows are currently available in private preview and customers enrolled can now start accessing data there from Power BI Desktop.
Data preparation
M intellisense (preview)
With this month’s release, we’re shipping a Preview of Intellisense support for the M language. This support is available in the Advanced Query Editor in this release. In the future, we plan to add M Intellisense support for the Formula Bar and the Custom Column dialog.
To try out this new feature, you need to enable it under the Preview features tab in the Options dialog.
After the feature has been enabled and Power BI Desktop is restarted, you can leverage Intellisense within the Advanced Editor in the Power Query Editor dialog. This includes syntax coloring, line numbers, function completions and parameter metadata for M library functions.
We look forward to your feedback on this Preview feature and make sure to watch for further enhancements to it in future releases.
Watch the following video to learn more about M intellisense:
Add Column From Examples – support for text padding
We’re improving Add Column From Examples in this release by adding support for Text Padding transformations, which can now be composed with any other transformations in order to generate the right set of transformations to go from input columns to desired output based on user-provided samples.
Text Padding allows you to make values within a Text column conform to a fixed width by adding characters at the beginning or the end of the original value.
Coming soon
We hope the ability to copy data from tables and matrixes shipping this month is useful but we have more planned for copying in future releases as well! One of the top requests on ideas.powerbi.com is the ability to copy visuals between .pbix files and we’re working on this right now. During the recent Microsoft Hackathon we encouraged the team to work on features they thought would add most value to Power BI and this was one of the engineers’ projects. There are a number of these ‘Office-like’ features we have planned in the coming months as we keep trying to make Power BI Desktop your ‘PowerPoint for data’.
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. For any preview features, you can always give us your feedback in our active community. 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.