Power BI Q&A: Language Modeling in Excel (adding synonyms to a Power Pivot data model)

To improve the Power BI Q&A online search experience, add synonyms to your data model in Power Pivot for Excel. Your users don’t know the names of the objects in your data model. Add synonyms for those tables, columns and fields so that when your users type natural language queries in Q&A, those queries are understood and the query results are relevant. Synonyms can be added to any Excel 2013 workbook that has a data model. Learn how to create an Excel workbook with a Data Model.

 Tip    Much more language modeling (including adding synonyms) is available in Power BI for Office 365 (cloud modeling).

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!

Requirements

  • Office 365 Professional Plus or the Click-to-Run version of Office 2013 Professional Plus.
  • This feature isn’t available in Office on a Windows RT PC.
  • Edit permissions for the workbook. If you don’t have access, contact your IT Administrator or Data Modeler.
  • The Power Pivot add-in must be enabled in Excel.
  • Office 365 subscription.
  • Signed in using an organizational account.

Open the Synonyms pane in Power Pivot

A set of synonyms are added automatically, the first time you open the Synonyms pane in your Power Pivot workbook. In the Synonyms pane, you can edit or remove synonyms and add new ones.

  1. Open the workbook that contains the data model you’d like to edit.
  2. Open Power Pivot by selecting the Power Pivot tab and clicking Manage.
  3. Open the Advanced tab and select Synonyms.

Power Pivot Language Control

Power Pivot switches to Diagram View with the Synonyms pane on the right. The Synonyms pane lists the table, fields and columns for the selected table. Below each table, field, and column are its synonyms. To see the synonyms for another table, select that table from the diagram on the left.

Power Pivot Synonym Pane

Excel auto-generates synonyms for you

Excel auto-generates one synonym for each table, column, and field name. This is referred to as the Primary Synonym and appears bolded.

  • If the name is recognized by Excel, that name is used as the synonym. For example, the Primary Synonym for “Cumulative Medal Count” is “cumulative medal count”, and “Description” is “description”.
  • If the name is a concatenation of words recognized by Excel, the name will be split into those individual words. For example, the Primary Synonym for “HostID” is “host id”, and “Country/Region” is “country region”.
  • If the name is not recognized by Excel, it is used as-is. For example, the Primary Synonym for “NOC” is “noc”, and “ccrx123” is “ccrx123”.

Understanding the Synonyms pane

Which data model objects can have synonyms?

Not every object from the data model will appear in the Synonyms pane. Calculated fields can have synonyms, but hierarchies cannot. If you find this confusing, you can turn off the display of objects for which synonyms cannot be created. To do this, in the Display area at the top of the Diagram, deselect one or more objects.

Power Pivot Layout Display Settings

 Note    Turning off the display of layout objects does not impact the display of these objects anywhere else.

Why are some things bolded and some things dimmed?

  • In Diagram view, tables that are entirely dimmed are hidden from client tools. Within tables, dimmed columns and fields are also hidden from the client tools.

In the Synonyms pane, these hidden columns and fields, as well as all of their synonyms, display dimmed.

 Note    Excel will auto-generate synonyms for hidden objects. However, when Q&A performs a search, it does not search columns, tables, or fields that are hidden.

  • In the Synonyms pane, you will notice that some are bolded and some are not. The bolded synonyms are the ones auto-generated by Power Pivot and are called Primary Synonyms. The lighter synonyms are those manually created. The auto-generated synonym can be deleted and replaced.

In the Power Pivot window, below:

  • olympic year is bolded, is auto-generated by Excel, and is a Primary Synonym. Primary Synonyms are the ones displayed by Q&A to your users in the restatement of their query.
  • event year and edition year are manually-added synonyms. They will be recognized by Q&A, but will not appear in restatements.
  • in Diagram view, the display of hierarchies is turned off.
  • the MedalClasses table is hidden from client tools.
  • the Medals table’s Medal ID and Year columns are hidden from client tools.

Synonym Pane Explained

Add new synonyms

To add new synonyms, click in the field below the table, column, or field name and type the synonyms separated by a comma and space. Synonyms can be a single word or a phrase. Synonyms can be copied and pasted to other columns.

add synonym in Power Pivot

When you are done adding synonyms to your data model, save your workbook. To make this workbook available to Power BI Q&A users, you’ll need to publish it to a Power BI site.

Delete synonyms

Highlight and click backspace to delete a synonym. If the Primary Synonym is deleted and not replaced, it will be re-generated the next time the table is selected.

Tips for creating synonyms

  1. nouns should be singular – chair not chairs
  2. you can use compound terms like net sales or event gender
  3. synonyms can contain hyphens and other punctuation
  4. remember to separate synonyms with a comma and a space

For more advanced tips for editing your data model to provide the best experience to your users, see Enhancing and Tuning Power BI Q&A Natural Language Models in Power Pivot.

See Also

After you’ve added synonyms to your Power Pivot model:

Having trouble adding synonyms to Power Pivot for Excel:

How do the Power Pivot for Excel synonyms and modeling fit into the Q&A big picture:

Videos – coming soon!

 
 
Applies to:
Excel 2013