[October 2nd Update] We have released a new Power BI Desktop build that includes a few additional bug fixes for visualization labels, DateTime picker, and DAX formula bar. You can install this new version to benefit from these fixes by clicking the Download button below.
Today we are announcing the September update for Power BI Desktop. As you can see on the title of the blog post, this is a massive update to our product, with 44 (yes, forty-four!) new features spread across our Report Authoring, Data Modeling, Data Sources and Data Transformation capabilities. In average, this represents almost two new features per working day since our previous update… We’ve been busy!
Before diving into the list of new features, we want to thank everyone of you who has sent us feedback about the product. You are directly contributing to making Power BI Desktop a better product every month and we really appreciate that you take the time to send us feedback. Please continue sending your feedback and questions via Send a Smile/Frown in the product, voting for what features you’d like to see next or by asking questions on our Power BI Community Forums. We are committed to reading and responding to every one of you.
Here is a summary of the new features included in this update (it will not be a short one, though):
- Report-level filters.
- Drill Up/Down for column and scatter charts.
- New Page Size and Page View options.
- Support for inserting Shapes in the Report canvas.
- Fields pane improvements.
- Search Box to make it easier to find fields by name.
- “Expand/Collapse All” to improve navigation when there are multiple tables in the Fields pane.
- Field well cardinality support, drag-replace for buckets with 1 item.
- Additional Data Labels formatting options.
- Chart Cartesian Axis Improvements:
- X-Axis label direction (horizontal / diagonal).
- Support for Logarithmic & Linear scales for values in Y-axis.
- Display Text for hyperlinks in textboxes.
- Improvements to existing visualizations: Table, Matrix, Slicer, Scatter Chart, Single Cards, Combo Charts and Gauges.
- Support for displaying Color Saturation values in tooltips.
- Ability to resize images and apply additional formatting options.
- Calculated Tables.
- Relationships view:
- Create relationships via drag/drop between two tables in the diagram.
- Delete relationships in relationship view by selecting and hitting the Delete key.
- Rename/delete tables and columns
- Data view:
- Copy Table contents to clipboard.
- Field Summarization:
- Support for additional operations in the Fields pane: median, standard deviation, and variance.
- Default summarization: Users can now customize the default summarization operation for any given field in their model.
- Support for On-Prem Spark distributions.
- Support for SharePoint lists from non-English sites.
- Exchange connector – Enhanced support, now allowing connections to multiple mailboxes.
- Excel Workbook Connector – Automatic Column Type detection when importing .XLS files.
- “Select Related Tables” option when connecting to database sources.
- Enhanced Active Directory connector credentials, allowing alternate Windows Credentials.
- Improved Function Invocation experience when loading functions from a data source (such as a database).
- “Import Excel Workbook Contents” feature released last month now also supports external connections to Analysis Services tabular models.
- New option to “Delete All” entries in the Data Source Settings dialog.
- Option to “Enable Relationship Import during Refresh operations”.
Data Transformations and Query Editor Improvements
- Copy to clipboard (available for cells/columns/tables).
- Filter date columns by earliest/latest date (dynamic filter).
- Extract min/max date/time value from a column.
- Replace Values – Provision for specifying special characters.
- “Detect Column Types” option to trigger type detection on demand.
- “Refresh All Previews” to refresh all Query Editor previews with a single click.
- Performance Improvements:
- Choose Columns dialog: Faster user experience for dealing with wide tables.
- Auto-filter & Expand/Aggregate popups: Faster for large number of values/fields.
The following video covers all of these features in more detail. You can also continue reading the rest of this blog post for a description of each new feature.
Report Authoring
Report-level filters
Before this update, users were able to define filters at the visualization and page levels, but it was not possible to define a filter that would apply across all visualizations in all pages on your report. We have added Report-level filters with this update, which can be found under the Filters pane in the Report view.
Drill Up/Down for column and scatter charts
It is now possible to define Drill Up/Down behaviors for column and scatter charts. Drill Up/Down behaviors allow users to enable report consumers to see aggregated data and be able to drill into a detailed breakdown of the data by a different category.
For instance, users could create a “Sales by Country” column chart like the one below and also provide “Sales by City” when the user drills down. In order to do this, they need to drag both Country and City to the Axis area in the Visualizations pane.
Once the Drill Down behavior has been defined, it is possible to drill down for all columns/values in the chart at the same time (“Drill all to the next level”, in the top-left corner of the chart), or one by one by enabling “Drill Mode” (top-right corner of the chart) and then clicking on the desired value to drill down to the underlying view.
New Page Size and Page View options
We have added several new options to customize the size of report pages, as well as the behavior for how to view these pages in Power BI depending on window and screen sizes:
- Page Size: This controls the size and aspect ratio of each page in the report. Note that users can assign a different page size to each page within their report if desired. Available page sizes are: 16:9, 4:3, Dynamic, Letter and Custom (fully control the width and height of the page in pixels).
- Page View: This option allows users to configure how pages should be rendered depending on the size of the report viewer (either Power BI Desktop or Power BI service). Available options are: Fit to Page, Fit to Width and Actual Size.
Both of these options can be found in the Home tab in the ribbon under Report and View respectively. Additional page sizes can also be found in the formatting pane when no visualization object is selected.
Support for inserting Shapes in the Report canvas
Users can now insert shapes in the canvas via the “Insert -> Shapes” menu in the ribbon. Supported shapes include Rectangle, Oval, Line, Triangle and Arrow. Several formatting options are available for each shape, such as Line/Fill Colors, Rotation, Title, Background, etc. These options can be found under the Formatting pane.
Fields pane improvements
We’re making a couple of improvements to the Fields pane to make it much easier for users to navigate and find items within their tables:
- Search Box to make it easier to find fields by name.
- “Expand/Collapse All” to improve navigation when there are multiple tables in the Fields pane.
Field well cardinality support, drag-replace for buckets with 1 item
It is now possible to replace an item in the Visualizations pane (such as the X or Y axis, or the Values field) by dragging another field on top of the existing one. Before this update, users had to remove the previous field before adding the new one. If the previous field was the last field in the Visualizations pane for that chart, the chart would have been automatically deleted. This change greatly improves the usability of modifying configuration for an existing chart.
Additional Visualization Formatting Options – Data Labels Formatting
We have added new formatting options for Data Labels:
- Adjust Display Units for data displayed in labels, including: automatic, none, thousands, millions, billions and trillions.
- Customize Font Colors.
- Data Precision (number of decimal places).
These options can be found under the Data Labels section in the formatting pane.
Chart Cartesian Axis Improvements
The next group of improvements in this month’s update is around Cartesian charts (such as Column, Bar Charts or Scatter Charts). These improvements include:
- X-Axis: Automatic adjustment of labels direction (horizontal / diagonal) to better optimize canvas space.
- Support for Logarithmic & Linear scales for values in Y-axis: This option is available under the Y-axis sections in the Formatting pane.
Display Text for hyperlinks in textboxes
In one of our previous updates, we enabled the ability to include hyperlinks as part of textboxes. However, the hyperlinks had to be visible in the textbox which in many cases was not the desired end result for the user.
With this update, users can now add hyperlinks to any given display text within a textbox in the canvas. Simply highlight the text and use the hyperlink option in the floating menu to set the link or to click on it.
Improvements to existing visualizations
We have made a number of improvements to existing visualizations, based on your feedback from previous releases:
| Visualization Type | Improvements |
| Table and Matrix | Ability to resize columns |
| Tree Map | Support for Data Labels |
| Slicer | “Select All” with a single click
Additional Formatting options:
|
| Scatter Chart | Ability to configure bubble’s color stroke |
| Single Cards | Support for the following Formatting options:
|
| Combo Charts | Performance improvements to chart rendering and interactivity gestures |
| Gauge | Ability to customize Min, Max and Target values. |
Ability to resize images and apply additional formatting options
Additional formatting options for Image objects allow users to control the scaling, aspect ratio, title and background for images within the report canvas.
Color Saturation values displayed in tooltips
We have added support for displaying the Color Saturation field values in tooltips.
The following video shows all Report Authoring improvements in this update.
Please note that all these Report Authoring features are also available in the Power BI Service.
Data Modeling
We’re making several improvements to the Data Modeling features in Power BI Desktop in this update. Some of these improve existing capabilities based on your feedback, while some others open up to new possibilities thanks to added features.
Calculated Tables
This month we’re adding the ability to create Calculated Tables. A Calculated Table is like a Calculated Column. It is calculated from other tables and columns already in the model and takes up space in the model just like a calculated column. Calculated Tables are a general purpose feature that can hold any valid table DAX expression. Some of the common functions that can be used with Calculated Tables include:
- Distinct
- Crossjoin
- Union
- NaturalInnerJoin
- NaturalLeftOuterJoin
- Intersect
- Calendar
- CalendarAuto
Calculated Tables are re-calculated when the model is re-processed. The columns in a calculated table have data types, formatting, and default summarization. Relationships can be created between Calculated Tables and any other tables.
Since Calculated tables are part of the model they can be used, among other things, to simplify DAX formulas with use of intermediate calculated tables, speed up calculations where the calcs are already in the model, create calendar tables, and to partition a model up with role-playing dimensions. In addition to creating permanent model tables, Calculated Tables can also be used to help debug DAX expressions that return tables.
Relationships view
A few improvements have been made in this release to the Relationships view. This view now allows users to:
- Create relationships via drag/drop between two tables in the diagram
- Delete relationships in relationship view by selecting a relationship and hitting the Delete key
- Rename/delete/hide tables and columns via context menu options
Data view
The Data view has been enhanced with the following improvements:
- Copy: Users can now copy the contents of a column or the entire table to the clipboard.
Field Summarization improvements
- Support for additional operations in the Fields pane: median, standard deviation, and variance.
- Default summarization: Users can now customize the default summarization operation for any given field in their model.
Watch the following video to see all Data Modeling improvements in action.
Data Connectivity
Support for On-Prem Spark distributions
The new data source in this month’s release is support for On-Prem Spark distributions. Last month, we added support for Microsoft Azure HDInsight Spark, and this month we’re expanding to other Spark distributions.
This new connector can be found under the “Other” category in the “Get Data” dialog.
Support for SharePoint lists from non-English sites
The SharePoint Lists connector (under “Other” in the “Get Data” dialog) allows users to import data from lists in their SharePoint sites. However, there used to be a limitation that only sites where the Site Language was set to English would work with our connector.
We have improved the connector in this release to remove this restriction, so now users can get data from any SharePoint list in any site (SharePoint 2013 or newer) regardless of the site language. Note that SharePoint 2010 sites continue to work but still expose the same language limitation, as this is a change available only in newer versions of the SharePoint API.
Exchange connector
We have improved the Exchange connector to allow connections to multiple mailboxes. Users can provide the email address to connect to as part of the Source dialog and then specify credentials for that source (which may or may not match the email address to connect to). This way, users can easily combine data from multiple Exchange mailboxes into a single report.
Excel Workbook Connector
The Excel Workbook connector now performs automatic column type detection when importing .XLS files. Before, this type detection was done only when importing .XLSX files.
“Select Related Tables” option when connecting to database sources
Very frequently, users will import multiple tables when connecting to a database. Before this update, users had to manually select all tables that they wanted to import, which often required an understanding of the underlying database schema so they would pick all tables that are related. With this update, we’re adding a new button named “Select Related Tables” to the Navigator dialog. When users click this button, Power BI Desktop will automatically select all tables that have a direct relationship to one or more of the already selected tables.
Enhanced Active Directory connector credentials
The Credentials dialog for Active Directory now allows users to select alternate Windows Credentials.
Improved Function Invocation experience
The experience for invoking functions loaded from a data source has been improved in this update. For instance, users will now get prompted for parameters when loading one or more functions from a data source (such as a database), after clicking Load or Edit in the Navigator dialog.
“Import Excel Workbook Contents” support for AS Tabular models
Last month we released a feature that allows users to convert their existing Excel Workbooks with Power BI artifacts into Power BI Desktop reports, This feature now also supports external connections to Analysis Services tabular models defined in Excel. These connections will get converted to AS Live connections in Power BI Desktop.
New option to “Delete All” entries in the Data Source Settings dialog
Users can now easily delete all stored Data Source Settings by using the new “Delete All” button.
Option to “Enable Relationship Import during Refresh operations”
Power BI Desktop automatically creates relationships between tables when loading them if these relationships exist in the data source (such as a database). In addition to creating these relationships during load, Power BI Desktop might create or delete relationships between tables while refreshing them if the relationships changed in the source. This used to be the default (and only) behavior before this update. With this update, we’re adding an option under “Current File – Data Load” to control whether to update relationship on refresh or not. The default behavior has been switched to not detect relationships on refresh, but still create them as part of the initial load.
Watch the following video to learn more about the Data Connectivity improvements in this month’s update.
Data Transformations and Query Editor Improvements
Copy to clipboard
We have enabled the ability to copy contents from the Query Editor preview to the clipboard. This option is available in the context menu for cells, column(s) and tables in the Query Editor window.
Filter date columns by earliest/latest date
It is now possible to filter a Date or Date/Time column by the earliest or latest date within the column. This filter is dynamically calculated, so it will always keep the rows with the earliest or latest date value within this column, on every refresh of your query.
This new filter can be found under the Date or DateTime filters menu in the column header filter dropdown.
Extract earliest/latest date/time value from a column
Users can extract the earliest or latest value from a Date or DateTime column by using the new Earliest/Latest options in the Transform tab, under the Date and Time dropdown menus.
Replace Values – Provision for specifying special characters
The “Replace Values” dialog has been enhanced to allow users to specify special characters (such as tab, carriage return, line feed, etc.) to find or replace with.
“Detect Column Types” transform
We have added a new transform called “Detect Column Types” under the Transform tab. This new option will trigger column type detection for all currently selected columns in the preview. This operation saves users time so they can select several columns and detect types, rather than having to select columns one by one and manually set their types.
“Refresh All Previews”
In this update we have added a new option to refresh all Query Editor previews with a single click. This option is called “Refresh All” and it is available within the Query Editor’s Home tab in the ribbon, under the Refresh button group.
Performance Improvements
As usual, we continue making Performance improvements across various areas of the product with every new update. This month, we’re improving Performance in the following two areas:
- Choose Columns dialog: Faster user experience for dealing with wide tables.
- Auto-filter & Expand/Aggregate popups: Faster for large number of values/fields.
See these new Data Transformations features in action in the following video:
That’s all for this month! We’re looking forward to your feedback about these improvements and any other area in Power BI Desktop. Download this update and give it a try!
This was a massive update in terms of new capabilities for our product, but we’re already working very hard on the next round of improvements.
October will be another great month for Power BI Desktop so please stay tuned for future updates…
Additional resources: