By: Scott Murray | Updated: 2020-02-26 | Comments | Related: > Power BI
Problem
What options are available to manually set the Power BI Q & A settings?
Solution
Much of Power BI’s benefit stems from the rich GUI, drag drop, and point and click functionality that is built into Power BI. In many cases, the rich interface can handle required functionality with ease and efficiency. For Q & A, both this tip and this second tip provide extensive coverage on using the Power BI interface to setup and customize the available options and semantics that can be used within Q & A in Power BI. However, there are certainly times when a programmatic approach is needed. As such Power BI provides just that; a method to customize and modify the linguistic setup of the Q & A functionality. Certainly, when dealing with these types of change made "behind the scenes", you could negatively impact a Power BI file; i.e. you could create a situation where the linguistics file is not in the correct format which could render the Power BI File unusable. However, Power BI does have safeguards in place.
Download Power BI and Sample Database
Before we get started with some examples, be sure to download the latest version of Power BI desktop from here. Additionally, we will be using the WideWorldImportersDW database as a basis for our data sources; this database can be downloaded from GitHub. If you need a refresher on bringing data into Power BI please see this tip.
Create Sample Power BI Report
We will setup a simple report from the Wide World Importers database that uses the Fact Sales table, along with the City and Customer Dimension tables.
We will also start with a design grid that includes the Q & A Visual. If you do not have the Q & A visual, be sure you have the latest version of Power BI Desktop.
Power BI Q & A Manage Terms
Within the Q & A visual, several Managed terms were added to the list of terms that create a match between the terms that a report consumer may use and the terms that Power BI understands. In the below example, patron equates to customer and State equates to state province.
As we examine the Power BI linguistics files later in this tip, these terms will be helpful in showing how Power BI handles some of these types of term corrections / term references and definitions.
Export Power BI Q & A Linguistic Schema
The prior screen prints show how to change several of the Q & A settings and properties via the GUI interface in Power BI, but we can also make several changes in the linguistics file that Power BI generates for handling and managing the terms used within Power BI’s Q & A function. The first step to get started would be to export the Linguistic file from your current Power BI file. To complete that process, select Modeling > Linguistics schema and then Export. Power BI will ask where you would to place the file and then you can click save.
Power BI completes the export process and also displays a warning, shown below, if issues exist with the current phrasings within a file. You likely will see these same warnings if you reimport this file.
At this point Power BI has created a file, with a YAML extension; YAML currently stands for YAML Ain’ Markup Language. The YAML file structure is not markup language, but it is somewhat similar to JSON file format with additional connections to XML. You can review more details about YAML here.
The most important aspect of reviewing the YAML configuration is to follow the correct formatting syntax and structure. YAML files can be edited in any text editor program such as NotePad or NotePad++ or in an IDE like Visual Studio Code. As shown below in NotePad++, the YAML structure is very well constructed; for this very small data model almost 2,000 lines of code are auto generated. The overall structure includes, first, the YAML version and then the language. The confidence level is listed next. Thereafter, each entity within the Power BI Model is listed.
Entities
Below the full list of entities is the relationship list. However, these relationships define how fields within your model are related in a descriptive sense as opposed to a database sense. For example, relationship describes how a city is part of a state / province or a customer is part of a buying group.
Thus, for each table in your model, you will have a table entity set generated and then for each column in that table, a column entity is generated. Within each entity, terms are generated that are in essence synonyms for the main header word. Thus, for the first item in the below screen print, customer is main entity. It can be described in many ways including customer, patron, client, consumer, buyer, purchaser, shopper, benefactor, investor, and backer. Within each of these terms, the type of word being used is described … generally these will be nouns. It also provides a weight which is somewhat similar to a confidence interval; thus, the higher the number, the more likely that a term represents the entity listed. Additionally, notice the two addition items in the Entity structure. First, the binding line notes the table and column name (if not a table binding). These bindings match back to your model, so table is used in a loose sense. Additionally, the state item reflects the source of the code. It could use any of the following:
- Generated – These entities were automatically generated by Power BI
- Deleted – Tells Power BI to not automatically generate or regenerate a particular entity or item
- Suggested – Them items are auto generated by Power BI and are based on general synonyms for a particular word
Terms
Reviewing the Terms list under customer, it is noted that patron does not include a type, state, or weight. The word patron is one of the terms that was added and managed though the Q & A setup, managed term window. Thus, it is a "report designer" defined term. If you scroll through the entire list of the entities, it could quickly become quite lengthy and complex.
Relationships
Moving on to the relationships area of the file, shown below, we see a different and distinct structure from the entities. Instead of creating synonyms, this section creates a set of related characteristics between terms that are included in a phrase. These phrases contain several distinct line items including:
- Phrase – Phrase that includes each of the key terms
- Binding – Similar to the entity object, the binding tells which table in the model the phrase belongs to
- State – Again similar to above, you can have state of Generated or Deleted
- Phrasing – This section describes the type of phrase, such as, name, noun, verb, adjective, preposition, and attribute (more on these items in the next paragraph)
- Weight – Again is similar to the weight used in the entity object
- Role – Generally used with dates to describe a date hierarchy
As you review these different phrase types, you will observe that each relates back to its original grammatical definition. Thus, a noun phrase is a person, place, or thing whereas a verb is action and an adjective describes a noun. Further, a preposition combines with a noun or sometimes an adjective to modify that noun. For each of these phrase types, we can specify not only a relationship, but also tell the linguistics schema what a noun or adjective value means. For instance, we may want to define a small city as a one with a population below 100,000 population or a product is new if it has an introduction date within 1 year or less.
Finally, the name and attribute types are specific to the data. Thus, names refer to actual items, such as a specific customer name, city name or a sales region. On the other hand, an attribute would describe the characteristics of something else, such as supplier of widgets or tax rates for counties.
You will quickly notice that these phrase types contain a substantial amount of cross over from one to the next. In the above example, the second element, customer_is_named_customer_name, is the basic example of a name / attribute element. This element tells Power BI that each customer has a customer name. That may seem logical, but Power BI may have trouble if the field was guest name and not customer name. Is the field customer name the same as guest name or does guest name mean something completely different?
Thus, let us cover an example of how we could address guest name dilemma.
customer_is_named_guest_name:
Binding: {Table: Customer}
Phrasings:
- Name:
Subject: customer
Name: customer.guest_name - Attribute: {Subject: customer, Object: customer.guest_name}
Modifying the YAML File
To actually modify the YAML file, Microsoft actually recommends using Visual Studio Code with the YAML Language Support extension. The YAML extension provides visual cues for invalid syntax and also proper tabs \ spacing. As shown below, the "guest name" name / attribute was added to the YAML relationship list. Be certain that your tabs for each line are properly aligned.
At this point we are ready to load this change back into the Power BI pbix file.
Upon completion of the import, the same warnings, which were displayed when the export file was completed, are also posted during the import.
If YAML file contains errors such as incorrect syntax or missing object values, then Power BI will fail the import, as shown below. Upon failing to load, the prior linguistic schema will remain in effect for the file. To address any such errors, it would be best to review similar Power BI generated values to see if a particular required item is missing. In the below example, the Name object was intentionally removed from the new guest name object. To be frank, at this time the documentation is very limited for the exact syntax required.
However, if the syntax and format is correct and the required objects line item(s) are included, the import could complete successfully, but a warning will be displayed about a missing or unknown entity, as illustrated subsequently.
The above example uses the name attribute relationship object. An alternate example would be to use the adjective phrase type to describe what is meant by a small city. Thus, we could utilize following lines to tell Power BI that a small city is one that has a recorded population count of less than 1000.
city_is_small:
Binding: {Table: City}
Conditions:
- Target: city.latest_recorded_population
Operator: LessThan
Value: 1000
Phrasings:
- Adjective:
Subject: city
Adjectives: [small]
Again, after saving the YAML file, we must import it back into Power BI, which I have done. Now we can ask Power BI Q & A to generate a list of small cities as illustrated below.
Quickly converting this visual to the table visual and then adding in the latest recorded population field; we can see all the cities that have a population of less than 1,000.
You may be wondering why go to all this trouble to modify the YAML file when you can just change the setting in Power BI Desktop. The problem with changing this setting is that the linguistic schema is related to that particular file only. If you have multiple/many files using these same linguistic schemas, it can be inefficient and time consuming to adjust the schema in multiple places. However, using the linguistic schema files, the schema can be change in one step. Then, once it is correct, you can apply it to multiple pbix files quite easily. Furthermore, the changes could be made to one file, then exported to a YAML file, and then subsequently imported into all the pbix files relatively quickly. These options all provide a method to scale up quickly.
In this tip we reviewed the process of using the export linguistics schema option to export a YAML schema-based file for manual editing of the Q & A settings. Adjustments can be made to both the schema entities and the schema relationships which are used by Power BI to comprehend the questions used by the Q & A functionality in Power BI.
Next Steps
Learn more about Power BI in this 3 hour training course.
About the author
Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2020-02-26
