Power BI Desktop March update feature summary

Hello everyone, we’ve just released another monthly update of Power BI Desktop.

For the March release we have introduced the following improvements:

Report View:

• Ability to publish reports to a Group Space in Power BI Service
• Reports respect Do Not Summarize model setting from SSAS MD
   

Data Modeling:

• Ability to change the datatype of a column when in DirectQuery mode
• Ability to assume Referential Integrity on relationships in Direct Query
• DirectQuery for Oracle and Teradata moves out of preview
   

Data Connectivity:

• SAP BW Connector (Preview)
• R Scripts connector
• Support for Command Timeout in the UX
• Setting to disable Privacy Level prompts at machine level
• New Transformations:
  • Remove Blanks via Column Filter menu
  • Convert Duration values to Years
  • Keep Duplicates
• Support for whitespace and line feeds in Query Editor preview
• Navigator enhancements:
  • Ability to disable preview from Navigator
  • Technical name support
• Load: Auto-step to disambiguate conflicting column names
• Rename queries directly from Queries pane.
• Smartsheet connector GA
   

For a summary of the major updates, you can watch the following video:

Report View

Publish to group space

When a user publishes a report to the Power BI Service for the first time, they are shown an option to choose the report's destination workspace. The list includes all workspaces the user has access to as well as their My Workspace. This allows the user to publish to shared workspaces, making the report immediately available to its members.

More details in the following video:

Do Not Summarize model setting respected on visual controls

Sometimes when working with data it doesn’t make sense to add up the totals, for example when amounts are stored in different (local) currencies.

In SSAS, this can be configured in the data model by setting the IsAggregatable property to false. In previous versions, Power BI did not respect this property and always showed totals in controls that had this ability. In this release the property is respected and values are not summarized.

 

Note that the feature is not available when importing data and only works with the live connection setting. The above image shows the feature in the table on the right, compared to the old behavior on the left.

Data Model

Change column datatype in DirectQuery mode

The DirectQuery mode of data access has many advantages over imported data, but a downside of using it was that you had to use the data with the datatypes as they are configured in the database.

This has been improved in the latest release. If you go into the Modeling tab and select the data column from the Fields pane, you can configure its data type as shown below.

More details in the following video:

Ability to assume Referential Integrity on relationships in DirectQuery mode

A new setting has been added to the Edit Relationship dialog: a checkbox to “Assume Referential Integrity”. The setting is applied to imported and created data model relationships.

To access the setting select the Home tab -> Manage Relationships, select a relationship, and click Edit.

This is an advanced setting, and is only enabled when connecting to the data in DirectQuery mode. It will enable more efficient queries to be generated when it is known that:

• The From column of the relationship is never Null/blank
• For every value of the From column, there is a corresponding value in the To column

By setting the property in these cases, it allows more efficient queries to be sent to the backend database using INNER JOINs instead of OUTER JOINs. This will result in faster loading of dashboards for the end user.

DirectQuery for Oracle and Teradata moves out of preview

A preview of the DirectQuery for Oracle and Teradata feature was added to the PBI Desktop February release. With support for DirectQuery for Oracle and Teradata making it now to the Enterprise Gateway, this features moves out of preview in the Desktop.

Data Connectivity Features

SAP BW Connector (Preview)

We have released a new Preview feature: a connector that allow you to import data from SAP Business Warehouse.

To try it, first enable it under the Preview features tab in the Options dialog: File->Options and Settings -> Options -> Global -> Preview Features.

Once enabled, you will find “SAP Business Warehouse Server” under the list of Database connectors in the “Get Data” dialog.

To connect, specify a Server, System Number, and Client ID. You can optionally specify a Command Timeout for the connection, as well as a custom SQL statement to execute. When no statement has been specified, the Navigator dialog will display objects available in the server.

Note that to use this connector you need to have the SAP Business Warehouse Client tools installed on your machine.

We’re very glad to bring this new connector into Preview and encourage users to try it out and share their feedback so that we can improve it for future releases. A deep dive blog post about this connector will be coming soon.

R Scripts Connector

The R scripts data connector moves out of beta and into general availability in this release.

You can find “R Script” under the Other list of connectors in the “Get Data” dialog.

This connector allows you to connect to any data source supported by R, and run R scripts to create a data model to use in your reports. Note that you have to have R installed locally in order to run R scripts.

The following image shows prediction results loaded into a data model with the R Connector and then visualized in Desktop:

Support for Command Timeout in the UX

You can now specify a command timeout value (in minutes) when connecting to database sources.  This was previously possible only via custom formula authoring.

The new Command Timeout option can be found under the “Advanced options” section in data source dialogs.

Setting to disable Privacy Level prompts at machine level

Power BI Desktop allows users to combine data from multiple data sources into a single report. When dealing with multiple data sources, users often define queries that require sending data from one data source to another. In order to prevent disclosure of private or enterprise data, you can now specify the privacy level (public, organizational, or private) for each data source. Based on the selected privacy level, Power BI Desktop will ensure that data from a private source is not sent to any other sources and that data from an organizational source is only sent to sources within the organization.

To accomplish this, in some cases Power BI Desktop will download the data locally to perform the data combination in a secure way. This could result in a performance degradation because data from different data sources will need to be cached locally and combined in-memory. In those cases, users have the ability to ignore privacy levels as a “current file” setting. Note that this option is applicable per file per user, so it needs to be enabled on each file separately. In addition, ignoring this privacy protection needs to be approved by each user of the report, so someone opening a .PBIX file from a different user in their computer would have to either provide privacy levels for the data sources involved on the report or manually disable this feature in the Options dialog under Current File -> Privacy.

We are introducing another setting that allow users and enterprises to select the privacy level. It allows you to:

1. Combine data according to each file’s privacy level settings. This is the default behavior and matches the Power BI Desktop behavior in previous releases.
2. Always combine data according to your privacy level settings for each source. This new option allows a user to ensure that Privacy Levels are taken into account for every PBIX file in their machine, regardless of whether the “Current File” Privacy Level setting is enabled or disabled.
3. Always ignore privacy level settings, and potentially improve performance in this machine. This new option allows a user to always bypass privacy level settings for every PBIX file in their machine, regardless of the “Current File” setting.

These options are available to the user under the Options dialog:

More details in the following video:

New Data Transformations

We have added the following new data transformations to the Query Editor in this release:

• Remove Empty values via Column Filter menu
  This new filter will remove all rows where the value for the current column is null or empty. It can be accessed via the Column Filter menu dropdown.

  

• Convert Duration values to Years
  This new transformation can be found under the Transform or Add Column tabs. “Total Years” divides the total number of days of a duration type column by 365.

  

• Keep Duplicates
  This new transformation allows users to keep only the rows with duplicated values on the selected column(s). This new option can be found in the Home tab in the ribbon, under the “Remove Duplicates” split button.

  

Support for whitespace and line feeds in Query Editor preview

We have added support for visualizing whitespaces in data cells within the Query Editor preview. This includes any whitespace characters, including line feeds.

You can toggle between “Show whitespace” (default behavior) and not showing it (old behavior) from the View tab in the Query Editor ribbon.

Navigator dialog enhancements

We have made three major enhancements to the Navigator dialog in this update:

• Ability to disable preview from Navigator
  We have added an option to disable previews in the Navigator dialog. This allows users to reduce the number of calls being made to the data source in order to retrieve these previews.
• Technical name support
  Some data sources (such as SAP BW) support the notion of a “technical name” for objects within the source that provides a more meaningful name to the end user. In this update, we have added a new option to the Navigator dialog to allow users to switch between “physical name” (previous behavior) and “technical name” (new behavior).

Load: Auto-step to disambiguate conflicting column names (case-insensitive comparison)

One issue that many users ran into with previous versions of Power BI Desktop has to do with name conflicts when loading tables into the Data Model where two or more column names only differed in casing. The only workaround for these users after hitting this error during Load was to manually rename all conflicting columns in the Query Editor, and then reload their data.

With this update, we’re making this scenario seamless and Power BI Desktop will take care of automatically renaming conflicting column names when running into this situation. Column rename operations will be reflected as new steps in the Query Editor, so that they can be customized by the user.

Rename queries directly from Queries pane

It is now possible to rename queries directly from the Queries pane inside the Query Editor. In order to rename a query from this pane, simply select and right-click the query to access the “Rename” option in the context menu, or double click on the query name, or select it and then press the F2 key.

Smartsheet Connector GA

After multiple improvements and customer feedback, we’re excited to announce the Smartsheet connector is now generally available.

The largest improvement you may notice is automatic type detection of columns in the sheets you import. Choosing “Edit Query” will show this new “Changed Typed” step which displays the automatic detections. As with previous versions you can continue to build on the table in the query editor, including changing types that the automatic detection may not have caught.

More details in the following video:

Additional resources:

• Try Power BI
• Follow @MSPowerBI on Twitter
• Join the conversation at the Power BI Community

Tweet