Enhancing and tuning Excel Power Pivot workbooks for Power BI Q&A
To improve the Power BI Q&A online search experience, you can enhance the workbook (and its associated data model) in Excel or online in Power BI for Office 365.
In Add synonyms to an Excel Power Pivot data model you learned how to work in Excel to enhance the Power Pivot data model by adding synonyms. In this article you’ll learn about other enhancements you can make from within Excel that will improve Q&A search results.
We’re working to improve Q&A, and will continue to update and refine our documentation as we develop the Q&A service and add new features. If you find errors or have other feedback on the documentation, please let us know!
Assign data types
Assigning data types helps Q&A identify what data to retrieve, which visualization to use to display the results, which aggregations to use.
Add missing relationships
Q&A relies on relationships to answer certain types of questions. In our Olympics workbook for example, we have a table for medal winners and a table for host cities. Consider this Q&A question:
the number of medals awarded at the 2000 Sydney Olympics
Part of the answer comes from the Medals table and part from the Hosts table. If there is no relationship between these two tables, Q&A will not be able to answer the question correctly.
So be sure to create a healthy data model with relationships connecting the tables as needed.
Rename tables and columns
Q&A does its best to interpret your table and column names. For example, if it recognizes the individual words in a concatenation, it creates synonyms for both words. So FlagURL auto-generates two synonyms: flag and URL (and also flags and URLs).
But just because Q&A can recognizes the words you use to name your tables and columns, doesn’t mean it can make sense of them. It’s up to you to ensure that the names accurately reflect the content. If my workbook has a table named Hosts that contains the name of the host city (city), and also has a table named Venue that lists the events by venue (stadiumA, stadiumB, etc…) and by venue location (city), and I ask the question:
Number of events by city?
I’m likely to get the count of events by Venue city and not Host city. The Vancouver Olympic venues were spread among 4 different cities (Vancouver, West Vancouver, Redmond, Whistler).
Set Aggregation Rules (Summarize By)
In the Power Pivot Advanced tab, make use of the Summarize By settings.
For numeric column data, Power Pivot adds (sums) the numbers by default. So if you have a column with years (2014, 1985, etc…), it may add the years together instead of counting the occurrences. To ensure that data like this is not aggregated, set Summarize By to Do Not Summarize.
Use the Power Pivot Reporting properties
In the Power Pivot Advanced tab, make use of the Reporting Properties settings.
Assigning a data category helps Q&A identify what data to retrieve, which visualization to use to display the results, which aggregations to use. For example, when a column is categorized as a Continent (or any other category that can be geo-coded), Q&A will be able to display that data as a map visualization.
Some data categories should be added as Custom Categories. Creating a new category won’t change any behavior for that column, but it provides a heads-up that this column is different. For example, integer columns that should not be summed or averaged like Year and Zip Code. To create a Custom Category for Year:
From the Data Category dropdown > More Categories > Custom Category. Type Year in the Custom Categorization field. And then set Summarize By to Do Not Summarize (see Set Aggregation Rules (Summarize By) above).
Default Field Set
Create a default field set for each of your tables. Default field sets identify which columns to return for a given table. These are the columns most-relevant for that table. For example, my Employee table has 9 columns including country, but only 3 columns are identified in the default field set: firstname, lastname, and officelocation. When a Q&A question is asked about this table, the results shown are limited to data in those 3 columns. So the question “show employees in Austria”, will display a table with first name, last name, and office location for all Austria employees.
The columns not in the default field list are still used by Q&A to answer the question –but only those data found in the 3 default field set columns are displayed as results.
Hide tables, columns, fields from Q&A
When a table, column, or field is hidden in the Excel data model, Q&A cannot see those objects. As far as Q&A is concerned, those objects do not exist. Hide any tables, columns or fields that contain data that are not useful to Q&A or that could cause confusion.
Right-click the object to hide and select Hide from Client Tools.
Create “synonyms” on a data level
Power Pivot allows you to add synonyms for table, column, and field names. But what about synonyms for the data in your table? For example, let's say you have an EventTrial table with a Role column. One of the values is Official but people frequently refer to this role as Judge or Ref. A solution for this problem is to create a new table in Power Pivot similar to the Alias table below.
Then create a relationship between EventTrial[Role] and Alias[Role] and hide the column Alias[Role] from client tools. This way, you can ask a question like "show referees" and you will get the right results.