This month’s update is filled with major updates across the whole product. In the data preparation area, we’ve introduced query diagnostics, which allow you to see what queries are being run behind the scenes. On the reporting side, we are taking a huge step forward in terms of real-time reporting, with our new automatic page refresh feature. This feature lets you trigger refreshes for your DirectQuery-based report at regular intervals. Q&A has also been completely revamped this month, with a new Q&A visual, tooling, and improved user experiences.
Here’s the complete list of October updates:
- The new Q&A visual
-
- Improved drop-down control
- Red/Blue underlines
- Improved visual results
-
- Integration with office/Bing thesaurus
- Support for measure tables & better handling of table names and ambiguity
- Row label and synonyms support inside Modelling pane
-
- Review questions
- Teach Q&A
- Review all changes made
- Sagra Emigo connector generally available
- Azure Cost Management connector updated
- New Workplace Analytics connector
For a summary of the major updates, you can watch the following video:
Reporting
Automatic page refresh for DirectQuery (preview)
When monitoring critical events with Power BI, it’s important that the data is reflected in Power BI as soon as the data source gets updated. For example, in the manufacturing industry, it’s critical to know when a machine is malfunctioning. To enable this, we are releasing automatic page refresh, which allows you to set the refresh interval of visuals in your report when using a DirectQuery source. For very fast updating sources, this near real-time experience will allow your consumers to monitor time-critical reports and ensure that they are always viewing the latest.
To use automatic page refresh, make sure you have it turned on in the preview Options dialog.
After that, you’ll see a Page refresh card in the formatting pane for the page when connected to a DirectQuery source. In that card, you’ll set the duration of your refresh.
In Desktop, you can set up this refresh as frequent as once every second, assuming your DQ source can handle that. In the service, the minimum refresh time is 30 minutes for a regular workspace, so once you publish the report, the duration will be overwritten if it’s less than 30 min. If your workspace is part of a premium capacity, your duration can go as low as the minimum set by the capacity admin and will only work if the admin has enabled automatic page refresh in the capacity.
To learn more about the automatic page refresh preview you can read our documentation
Watch the following video to learn more about automatic page refresh:
Analytics
The new Q&A Visual
This month we are bringing NLG (natural language generation) even more to the forefront of reporting in Power BI with a new Q&A visual. This is a full visual experience that allows you to give the Q&A experience to your end-users. While similar to using a button to trigger Q&A, this visual can improve discoverability for your report consumers, as the experience isn’t hidden behind an extra button click. The new Q&A Visual can interact, cross filter, support bookmarks and be themed just like any other visual. To add the visual to the report, just click on the Q&A icon from the visualization pane.
This will add the Q&A visual to the report page with an input box for you to type your questions and a few suggestions to get started with. Clicking any of the suggestions will automatically populate the input box with that question and show you the result.
Otherwise, if you have your own question to ask, type it out and see Q&A automatically give you the result.
As a report author, you also have the ability to use this visual as an authoring tool and convert the Q&A visual into the specific visual you’re seeing if you’d like to stick with that one visual. Just click the icon next to the input box.
Near the top of the visual result, you’ll also see an info icon that has the restatement as a tooltip. If you ever want to confirm what the Q&A result is showing you, you can hover over this icon to see the restatement.
The state the Q&A visual is in when you save your report is what your report consumers will see as well. So if you have the report with a Q&A in the box, that will be the default view for your end users. You can also use bookmarks to save different Q&A questions for your end users. The Q&A visual will also automatically pick up your applied theme and cross-highlight automatically works between visuals on the report page and the Q&A visual.
You also have complete formatting control over the question input box through the question field card in the formatting pane.
The suggestions’ appearance can also be formatted.
If you’re using the Ask a Question button in the ribbon or double-click to trigger the Q&A experience, those entry points will now create Q&A visuals.
Improved user experience for Q&A
We’ve also updated the user experience for the Q&A question input box. You’ll see blue underlines on terms that we understand from your data model. Red underlines are used for terms that are ambiguous or we don’t understand at all. There is also a new dropdown experience when selecting a specific auto-suggestion as you type that is similar to what you get in web search engines.
We’ve also done a lot of work to improve the visuals that are generated from Q&A. Q&A should be even better at suggesting the best visual following visualization best practices for the specific questions you ask.
Natural Language improvements for Q&A
Integration with Office/Bing thesaurus
We’ve done a lot of work to also improve core engine inside Q&A by allowing it to automatically understand common synonyms by leveraging Office/Bing data to understand terms which are not mentioned in your data model.
Support for measure tables and better handling of table names and ambiguity
If you have used Q&A before, you would know that Q&A does not work very well when dealing with disconnected tables. This update brings the biggest improvement to Q&A working out of the box automatically by providing automatic handling of disconnected/measure tables and figuring out which measures are related with which dimensions. You no longer need to make a specific data model just for Q&A which we hope will save you countless hours and pain.
Row label and synonyms support inside Modelling pane
This update also brings support for Row labels (formerly known as default labels which were available inside Excel) which allows you to define the row identifier for a table. To define the row label, you can go to the modeling tab and click a table name to see the option. Also, if you wish to define synonyms which are to be used by Q&A you can use the modelling pane to quickly define all the synonyms for all columns inside your data mode.
Q&A Tooling (Preview)
This month we’re also introducing tooling you can use to see how people are using the Q&A visual and improve the results they’re getting. You can reach this tooling by click on the gear icon.
Review questions
The first section of the tooling allows you to see the questions consumers are asking in your reports. You start out by picking which dataset is used in this report.
From there, you’ll see a list of questions that have been asked in the Power BI service from that data. You can filter the list to see either all questions or just the ones where the consumer has asked for you to fix the result to give a better answer. Any questions where Q&A didn’t understand the terms will have a pencil next to them, which will take you to the next section, Teach Q&A, to fix it.
Teach Q&A
The second section in the tooling allows you to teach Q&A how to respond to questions that are ambiguous. You can start by typing in a question such as “Show my best manufacturers” and clicking Submit.
Then the terms that Q&A didn’t understand, in this case “best”, are identified, and you’re given an experience to teach Q&A what those terms mean.
You can then type in your own custom definition, such as good manufacturers have a high sales amount, see the potential result on the right, and save the definition once you are happy.
You can do this for as many terms and questions as needed.
Manage terms
The last section allows you to manage the terms you’ve added to Q&A through the Teach Q&A section. You’ll see each term, its definition, when it was created, and if you no longer want the term an option to delete it.
Support for SSAS and Azure AS including RLS
Finally, we now support SQL Server Analysis Services and Azure Analysis services with Q&A allowing you to connect to your data without having to import the data first inside Power BI. In order to use this feature, you must first enable the preview feature switch in options and settings -> options -> preview features.
There are a few limitations that we’ll be addressing over the coming months. You can find the details in the documentation.
If you haven’t already, start using Q&A and provide a truly self-serve experience for your end-users. As always, we would love to hear from your feedback and what you think about the new Q&A experience.
Watch the following video to learn more about the new Q&A visual and tooling:
Visualizations
PowerApps visual is now included by default
Last month, we announced that the PowerApps visual was generally available and certified, and to continue along that vein, this month we’re announcing the visual is now included by default. Instead of needing to add the visual from the marketplace, you’ll find it automatically included with the list of other built-in visuals within the visualization pane. This visual is a key integration with the rest of the Power Platform and enables key scenarios such as writeback and taking immediate data-driven actions on your insights.
New xViz visuals
There are three new visuals as part of the xViz suite. The xViz suite is a set of custom visuals that aim to meet your end-to-end BI needs. You can use all the visuals within the suite for free with their branding and some data row restrictions or you can pay one price to unlock all the visuals. You can learn more about pricing here.
Hierarchical Variance Table
The Hierarchical variance table lets you compare to measures within a hierarchical table layout and see the variance, % variance, and how that contributes to the whole. If you include a date field, you can also see a sparkline within the table.
You can use the number formatting card to apply custom formatting to the measures.
The same card also has a section called semantic formatting that lets you change the color and format based on if the value is negative or positive.
Right-clicking on the row headers will allow you to do things like rearrange the order of the hierarchy, hide or show totals, change the sort order, expand to a specific level of the hierarchy, or show and hide different column headers.
Other formatting options include:
- Text and background colors for alternating rows
- Renaming headers
- Hiding empty rows
- Adding a search bar (only for the Power BI service)
Find this visual on AppSource.
Linear Gauge
The second visual this month is the Linear Gauge, which allows you to compare a value to a target goal.
In the conditional formatting card of the formatting pane, you can set the band thresholds and colors.
You can also customize the marker shape, size and offset from the bottom for both the actual and target in the gauge options card.
Other formatting options include:
- Setting minimum and maximum values
- Axis formatting, including text size, colors, and label density
- Data label formatting
Tag Cloud
The last visual for this month is the tag cloud. This word cloud visual allows you to get instant insights of the more prevalent terms in your data, such as product descriptions, tweets, and feedback comments.
Some key features and options include:
- Split phrases into individual words
- Remove special characters
- Exclude words
- Format the word size, color, angles, and number of orientations
You can also control the color and size of the words based on different metrics.
You can find this visual on AppSource.
Watch the following video to learn more about these new visuals:
Data connectivity
Sagra Emigo connector generally available
Emigo supports sales organizations and field teams management. It automates all daily activities of field professionals. Inventory data, orders, answers from surveys or all sorts of other digital forms are available for managers and other teams within organization, through the Biqsens Power BI app as well as through Emigo BI Connector for Power BI Desktop.
Azure Cost Management update
Azure Cost Management enhances exploration and analysis of your Azure cost data. With it, you can make powerful and flexible reports to understand your consumption patterns.
What’s new:
- Supports OAuth 2.0 for authentication
- Supports both Microsoft Customer Agreement and Enterprise Agreements (EA)
- Includes enhancements to RI recommendations and RI usage data and the addition of amortized cost
- Performance improvements
New Workplace Analytics connector
Microsoft Workplace Analytics uses data from everyday work in Office 365 to identify collaboration patterns that impact productivity, workforce effectiveness, and employee engagement. Help your customers speed transformation using new insights about the way their people and teams work.
Data Preparation
Query diagnostics
Query Diagnostics is a powerful new feature that will allow you to determine what the Mashup Engine is doing during authoring time. While we’ll be expanding on this feature in the future, including allowing you to use it during full refreshes, at this time it allows you to understand what sort of queries you are emitting, what slowdowns you might run into during authoring refresh, and what kind of background events are happening.
To enable it, enable ‘Query Diagnostics’ under ‘Preview Features’ in Options.
To start diagnostics, in the Power Query Editor click on the ‘Tools’ pane in the Ribbon and select ‘Start Diagnostics’. To finish and aggregate the traces, press ‘Stop Diagnostics’. Whenever you start diagnostics, Power Query will begin tracing any evaluations that you cause.
The evaluation that most users think of is when you press refresh, or when you retrieve data for the first time, but there are many actions that can cause evaluations depending on the connector. For example, with the SQL connector, when you retrieve a list of values to filter, that would kick off an evaluation as well—but it doesn’t associate with a user query, and that’s represented in the diagnostics. Other system-generated queries might include Navigator or “Get Data” experience.
With this first version of the feature, we present two different diagnostic views—a summarized and a detailed view.
The basic view is aimed to give you an immediate insight into where time is being spent in your query. The detailed view is much deeper, line by line, and will generally only be needed for serious debugging by power users.
Some capabilities, like the “Data Source Query” column, are currently available only on certain connectors. We will be working to extend the breadth of this coverage in the future.
You can find more information about Query Diagnostics, including all diagnostics information fields exposed by this feature and their semantics in this documentation article.
Data profiling enhancements
This month we’re introducing a couple of significant enhancements to our Data Profiling capabilities within the Power Query Editor, including:
Multiple Grouping options for the Column Profile pane value distribution visual, specific by column type, in addition to the existing “By Value” criteria:
- Text: By Text Length (number of characters).
- Number: By Sign (positive/negative) and Parity (even/odd).
- Date/DateTime: By Year, Month, Day, Week of Year, Day of Week, AM/PM Time and Hour within a day.
- And more for other data types (Logical True/False), etc.
Filter options: Not only can you now leverage several type-specific grouping criteria within the Colum Profiles distribution pane but also we’ve enabled filtering from within the callouts for each of the values in the distribution chart when the grouping criteria is applied. For example, you can easily exclude all values that fall in a given Month right from the Data Profiles pane for a Date/DateTime column.
Watch the following video to learn more about our data preparation updates:
Template Apps
Project Web App
This month’s template app aimed at helping project, portfolio and resource managers who work in Project Web App visualize their work. The report model was built using the Project Web App data connector. It can be connected to your own Project Web App data using your own credentials. This template app is comprised of 13 visually rich report pages to support the PWA management needs.
Below is one of the many Portfolio Management report pages.
And here’s an example of a Resource Management report page.
The reports enable users to do a deep-dive analysis on project management, resource management and portfolio management views. Overall, this report provides a great out-of-box experience for managers using PWA to take their data to the next level.
- This template includes 6 report pages offering analysis on portfolios of projects including views of a Portfolio Dashboard, Portfolio Timeline, Portfolio Costs, Portfolio Milestones, and Portfolio Issues and Risks.
- It also includes 5 report pages with analysis on resourcing across projects including views of a Resource Overview, Resource Availability, Resource Assignments, specific Resource Details, and a predictive analysis Resource Demand Forecast.
- For individual projects, there are 2 report pages with detailed project information for single projects including a dive on Project Status and Project Issues and Risks.
You can install the app right in the Power BI service and set its parameters to your PWA site URL, and the language your site is in. Once you install it, the report is now yours, so you can customize and share as much as you want.
To learn more about this template app, check out this template’s documentation.
Download this app from AppSource.
Microsoft partners can create their own template apps to publish in the marketplace. If you’re interested in this, be sure to check out our documentation to get started and latest features blog post.
Watch the following video to learn more about Project Web App Template:
Other
New file format: .pbids
This month we are releasing a new file format: PBIDS, to streamline the “Get Data” experience for report creators in your organization. It’s recommended that admins create these files for commonly used connections to help the getting started experience for new report authors.
When an author opens a .PBIDS file, Power BI Desktop will launch and prompt for authentication to connect to the data source specified in the file. Then the navigation dialog will pop-up and the user will need to select the tables to load into the model. They may also need to select the database(s) if one was not specified in the file. From there the user can start building visualizations or revisit “Recent sources” to load a new set of tables into the model.
For the admin to create this file, they will need to specify in the file the required inputs for a single connection and they can specify the mode of the connection. The “mode” can be either “DirectQuery” or “Import”. If “mode” is missing/null, the user that opens the file in Desktop will be prompted to select DirectQuery or Import.
Here is an example of a PBIDS file for a SQL Server connection:
{
“version”: “0.1”,
“connections”: [
{
“details”: {
“protocol”: “tds”,
“address”: {
“server”: “<<server-name>>”,
“database”: “<<db-name (Optional)>>”
}
},
“options”: {},
“mode”: “DirectQuery”
}
]
}
And here’s an example for a SharePoint list. Please note that the URL must point to the SharePoint site itself and not to a list within the site. The user will get a navigator which allows them to select one or more lists from that site which will each become a table in the model.
{
“version”: “0.1”,
“connections”: [
{
“details”: {
“protocol”: “sharepoint-list”,
“address”: {
“url”: “<Sharepoint Site URL>”
}
}
}
]
}
More examples can be found in our documentation page.
Watch the following video to learn more about this new file format:
Performance improvements for modeling operations
We have made a performance improvement in the Analysis Services engine to speed up modeling operations (such as adding measures or calculated columns, creating relationships, etc.) when the model satisfies the following conditions:
- Contains a lot of calculations
- Uses any of the UseRelationship or CrossFilter functions
- Uses any of the Today, Now, Random, etc. volatile functions
While the actual improvement you’ll see will be highly dependent on the model, we have seen 20x performance improvement for some customers when doing things like opening the file and adding a measure.
That’s all for this month! We hope that you enjoy these updates for the month. Please continue sending us your feedback and 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 Microsoft layout from PowerBI.Tips.