2016 has been quite the year for the Power BI Desktop. Over the course of our 11 releases this year, we’ve delivered over 190 features and improvements based on your feedback. For December, we are taking a break from our monthly releases to focus on quality. We’ll start the new year with another Desktop release. In this month's post, we’ll take a look back at the past year, recapping several of our favorite features, and taking a look at our big focus areas from this past year. If you'd like to see the features in action instead of reading about them, go ahead and download the file I used in this blog post.
Here’s the list of focus areas and features we’ll be recapping:
- See data
- Export data
- See records
- Trend lines
- Dynamic reference lines
- Analytics pane
- Forecasting
- Grouping
- Binning
- Clustering
- Pick the location of your R environment
- Support for Unicode characters and spaces in the R environment directory path
- Support for localized number formats
- R script data connector
- Run R scripts in the Query Editor
- Use your own R IDE
- R-powered custom visuals
- Chart borders
- Background images for reports and visuals
- Formatting features for tables, matrices, and multi-row cards
- Predefined table and matrix styles
- Configure line chart data labels
- Data label improvements for column and bar charts
- Customize tooltips
- Keyboard nudging for visuals
- Snap to grid
- Shape map
- Custom maps for Shape map
- ArcGIS maps
- Search in slicers
- Date slicer
- Top N filters
- Include/exclude
- Dropdown slicer
- Row-level security
- 22 new data sources
- DirectQuery support for 6 additional data sources
- Many improvements to existing connectors
- URL validation for SharePoint List connector
- Hierarchy support in SAP BW
- Multi-select values for a given variable or parameter when using SAP BW and SAP HANA
- Web page previews in the web connector
- Append tables improvement
- Conditional columns
- Query dependencies view
- Enhanced combine binaries experience
- Improved function authoring and invoking
Analytics
A big focal point for this year was analytics. At the start of the year, we began small, but by the end of the year, we were releasing major updates every month.
It started back in February when we released our first analytical features of the year, See data and Export data. With these two features, you can see the data we use to create the chart and then export it to do further analysis on it. This is especially valuable if you weren’t the report creator and want to learn more about the data.
In April, we followed that up with the release of See records, which allows you to see all the data records behind a data point. Using this feature, you can focus on a given data point and see the detailed records of it.
In April, we also released trend lines on visuals, which are a quick and easy way to identify either a positive or negative trend in charts. In August, we expanded this functionality with dynamic reference lines, which allow you to create data bound lines in your charts, such as Average lines or Median lines, to help with analysis of your charts. We also gave these trend and value lines a home in a dedicated Analytics pane. You can use these features in combination to find insights in your data. For example, you can use the trend line and average line to see how net satisfaction changes based on deal size and how that compares to the average net satisfaction of our customers.
In September, we added yet another feature to the Analytics Pane, forecasting. This forecasting feature uses built-in predictive forecasting models to automatically detect the step (monthly/weekly/annually) and seasonality in your data to provide forecasting results. This is a very powerful feature that can help you predict things such as your sales in the coming months.
In October, we introduced the ability to group points on your visual to help you more easily explore and analyze related categories. Our grouping feature lets you create these ad-hoc groups, which allows you to easily associate related data points, group many small data points into an Other bucket, and refine the data in your chart to make analysis easier. Binning gives a similar experience for numerical and time fields. In November, we followed up with our clustering preview. Clustering will automatically find associated data points for you and create the groups for you to use throughout your report. This will frequently find associations you might not have realized existed in your data. One example is finding related products that have similar satisfaction and likely to purchase again ratings. You could then use those product clusters in other charts in your report to do further analysis.
R
Power BI’s R integration is an integral part of our analytics offering. R is a programming language widely used by statisticians, data scientists, and data analysts to perform advanced analysis, clean data, and create visuals. Throughout this year, we’ve vastly improved upon the integration and added new ways to combine the power of R with Power BI.
The improvements started right away in our January release, where we made three improvements to the R integration. The first was the ability pick the location of your R environment from a drop down in the Options dialog. This location is shared between the R Script data connector and the R visuals. The other improvements allowed you to use Unicode characters and spaces as part of the R environment directory path and added support for localized number formats.
In March, we moved the R script data connector out of preview. This connector, which can be found under the Other list of connectors in Get data, 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.
R integration was further extended to the Query Editor in July. From the Query Editor, you can select Run R script to enter an R script to transform and shape your data. Data cleansing is an important and necessary part of data analysis and greatly affects how easy it is to find insights later in the process. With this feature, you can use the power of R to do all the prep work necessary for your analysis.
In September, we updated our R visual integration to allow you to use your own R IDE to create your R visuals. When you select an R visual from the visual gallery, the Power BI editor will open. But if you have a favorite IDE or need a more powerful editor, you can use the pop out icon to open your editor. In October, we released another update to the R visuals experience. The R visuals integration adds advanced analytical depth and endless visualization flexibility to your Power BI reports. These reports can then be used by all business users on the Power BI web service, not just R experts. However, report creators don’t always have R script expertise, and with this update, we removed this barrier with the R-powered visuals gallery. With this gallery extension, you can download any pre-built R-powered custom visual and use it in your reports.
Visual formatting
2016 has been a big year for visuals, with many new formatting features and a couple of new ways to visualize map data. A top priority for us this year was to allow you to customize your charts just the way you want, and we have many more features planned in this area for the coming year as well.
We started out in January adding the ability to add borders to your charts. The ability to add borders and choose the border’s colors was one of the first customizations added. Many users like to use this feature to align their charts and create a visible flow through the report. Also in January, we added the ability to control the background image for report pages and the plot area of charts, which is very useful when you are creating a report with a theme.
Tables and matrices are some of our most used visuals, and this year we started updating them with more formatting features. In April, we added many highly requested formatting features for tables, matrices, and multi-row cards. Starting with this release, you were able to start customizing the header, background, text, and grid colors of your tables, as well as grid lines, spacing, and padding. We followed up in July with predefined table styles and in August with predefined matrix styles. Using these predefined styles is a quick way to set the style of your chart and then do customization on top of that. We also added the ability to conditionally format a measure column in a table (May) and on matrices (November). This lets you quickly see low and high values in the visual. Additionally, in November, we released column formatting for both table and matrices, so you now have full control over your table column’s font and background color. You can use this feature to do things like change font color on conditionally formatted values so they are easier to read or use a bold background color on a key column that you want to draw attention to. We have many more table and matrix features planned for the coming year as well, so you will continue to see many improvements in this area.
Data labels and tooltips was another area we focused on this year. Tooltips are an essential component in providing more context and details to your viewers. With our May release, we added the ability to add additional context to your visual by adding more measures to the tooltip. You can look for another update in this area early next year. In June, we made an improvement to our line chart data labels. You can control the density of data labels on continuous line charts through a density property slider. In October, we extended this by providing a density control for data labels on column charts with a continuous axis. At the same time, we provided the ability to control data label position and orientation for column, bar, and waterfall charts.
Once you have well formatted visuals, you also need to lay them out on your report. At the start of the year, you had to manually align them the best you could without any guidance. In May, we improved this by allowing you to move visuals around with the arrow keys on your keyboard, which allowed you to be much more precise. However, this was still a difficult, time-consuming task until our October release of Snap to grid, which made laying out your charts a breeze. You can now easily drag your visuals around the canvas and they snap to a grid layout.
We’ve also done a fair amount of work on maps this year. In June, we added a new map type to the Desktop in preview. Shape map’s main purpose is to show relative comparisons of regions on a map by coloring them differently instead of showing precise geographical location of data points as our other maps do. We followed up in July with the ability for Shape maps to consume custom maps in TopoJSON format, giving you full control over the map’s appearance. This is a powerful feature as it allows you to create non-geographical maps such as floor plans. In September, we added a fourth map type, ArcGIS maps for Power BI by Esri, which unlocked new capabilities. In collaboration with Esri, a leader in the geographic information systems (GIS) industry, you can now take geographic information to a whole new level. Some of the benefits of this new map include map backgrounds, reference layers, and fine grained control over how your data appears on the map.
Slicers and filters
Many of the top asks from you in our UserVoice forums were around filtering and slicers. In the second half of this past year, we made great strides in this area.
It all started in June when we added search to slicers. Many times, the list of items in slicers is long, and it can be cumbersome to find the item you are looking for. By adding the search bar through the More (…) menu, it makes your slicers much more user friendly for anyone using your reports.
Our first new slicer type of the year, the Date Slicer, was released in October. Slicers are a great way for report viewers to quickly slice and dice the data they are seeing, and if you want to slice by dates, the date slicer’s slider and calendar picker is a very intuitive way to do this. It makes it very easy to filter your report to dates before or after a specific date.
October was also a big release for filter functionality, with the release of our Top N filter and include/exclude of data points. The Top N filter is a great way to quickly filter your data to a given number of key data points, homing in on what matters most. Including and excluding data points through the right click menu is another great way to do this. You can choose to just include the outlier points or filter out points that are irreverent to the analysis you are doing.
Finally, in November, we release the much-anticipated dropdown slicer. Reporting canvas space can be a precious commodity, and slicers can take up too much of this space, especially if the list is very long. By switching the slicer type to Dropdown, you can conserve this much-needed space, while still providing your report viewers with a quick and easy way to filter the report data.
Security
A big concern for many report creators is making sure the data is secure and viewers only see what they are allowed to see. In the June update, we provided a way in the Desktop to control data access with row-level security. Row-level security allows you to secure your data at the row level with security filters based on roles you create. Using the Manage roles button in the Modelling tab, you can create roles then use DAX expressions to control what each role sees. Then, once you publish it up to the web, you can assign users to each role.
Data connectivity
Over the course of this past year, we’ve vastly improved data connectivity in the Power BI Desktop. We’ve added support for 22 new data sources, such as the March release of our SAP Business Warehouse connector, the July release of the Amazon Redshift connector and Impala connector, and the August release of our Snowflake connector. We’ve also added DirectQuery support for 6 data sources: Oracle, Teradata, Amazon Redshift, Impala, Snowflake, and Spark.
We’ve also made many improvements to existing connectors based on customer feedback. In February, we improved the SharePoint List connector, which lets you provide the URL to your SharePoint site root and select the SharePoint lists you want to connect to. The improvement was adding URL validation when the URL provided is invalid. in July, one of many months with SAP BW improvements, we saw the addition of hierarchy support in SAP BW and the ability to multi-select values for a given variable or parameter when using SAP BW and SAP HANA. August included another big improvement with the addition of Web page previews in the Web connector, which lets you actually select the tables you want to connect to in context of the web page.
With over 50 different data connectivity improvements this year, this is just a snapshot of the work we did in this area. We will continue this pace next year as we strive to empower you to easily connect to any data you want in the manner that you want.
Data preparation
We pride ourselves on having very powerful built-in data preparation features in the Desktop that make it very easy to clean and transform your data before modeling and reporting on it. We put a lot of effort into continuing to innovate here, and during this year, we released almost 60 improvements in this area.
The first exciting addition of the year was in January when we extended the append tables experience to allow for more than two tables in one go. This new mode is extremely powerful for advanced scenarios where you want to quickly append many tables in one shot.
In April, we released conditional columns. This update made it extremely easy for you to create new columns in your queries based on values from a different column. This is very useful if you are trying to bucket your data into different categories.
One of the most popular requests in the data preparation area was the Query dependencies view, which we released in September. By using this dialog, you can quickly and easily understand the dependencies across all your queries in your Desktop file. This helps you know what other queries may be affected when you make an update to a given query.
Many of you take advantage of one of our folder-like connectors, such as Folder, SharePoint folder, HDFS, etc. to combine multiple files with the same schema into one table. Because of the popularity, we enhanced the combine binaries experience in November to make it much more intuitive. Now when you use this transformation, it will analyze each input file to determine the file format to use and then let you select a specific object to use from the first file to extract.
Between September and October of this year we released 3 improvements around function authoring and invocation. First in September, we added the Invoke Custom Function operation to the Add Column tab in the ribbon. This new operation makes it seamless for users to invoke a custom function over each row of a table. The dialog gives you the option to pick one of the custom functions in the current file and specify either static values or other columns as input arguments. We followed this up in October with support for table parameters in the Invoke Function experience. You can now use parameters of type “Table” when invoking either a built-in or user-defined function. Lastly, in November, we made it easier to update function definitions without the need to maintain the underlying M code. When a function definition is created based on another query, a link is created between the original query and the newly generated function so that they remain synchronized. When the original query is modified with additional steps, the linked function will be automatically updated for you.
Across the entire Desktop, we made huge leaps forward this past year. And we aren’t planning on slow down. We have many new features planned for the coming year that we are very excited to share with you.
Thank you for all the support and feedback you’ve given throughout 2016. We value your input and as you can see, work hard to integrate your needs into our plans. Please don’t forget to vote for other features that you would like to see in the Power BI Desktop in the future, and we’ll see you again in the new year for the January Desktop release.
Happy New Year!