Power BI’s Q&A is often the quickest way to get answers from your data in Power BI. It furthermore makes use of natural language to provide rich insights from the questions asked from your datasets. Q&A uses all the different visualizations available in Power BI to display the result of the question being asked. It is also a self learning tool that is guided through the question process as Q&A will indicate if the question makes sense or if there is some ambiguity in your question.
In this blog post, we will provide a detailed overview of the Q&A functionality in order to use it in a meaningful way.
Getting started with Q&A
There is a number of ways to launch Q&A. The quickest way is to simply double click with your right mouse on a Power BI page, this will open the Q&A functionality. Otherwise select Q&A from the locations as shown in the below image.
When opening Q&A we see that there is already some suggestions of questions that it wants to answer. This is a great way to get going and to get a feel for interacting with natural language in Q&A.
Let’s use one of these suggested questions to get going. We will start off by selecting total revenue by sales year. Great, Q&A created a line chart that displays the total revenue by year and the chart is sorted by the year. Something to note is that this is just a suggested visual, this is not yet converted into a visual that you can keep in a Power BI report. In order to do that, select the little image to the right of the Q&A question box as highlighted in the image below.
The result will be a standard line chart.
This was just a quick example to give you an idea of how Q&A works, we will now get into the details of Q&A.
Cleaning up your data model
Before delving deeper into Power BI’s Q&A functionality it is necessary to stress the importance of cleaning up your data model. This will help to guide Q&A to answer relevant questions. We will give a very brief overview. However, if you are still new to Power BI or want to refresh best practice, we have a series on Data Preparation in our blog posts. If you would like to get an extensive overview on data preparation, start with the first of our series of seven blog post on data preparation. We also have a blog post on Data Relationship which is also important to setup correctly in order for Q&A to answer questions form data across multiple tables.
First, let’s start with your data fields. We will use our fields list as shown below to verify our data fields.
To the left of the field name we see a little image that indicates a specific field type for the specific field in our table. Next to our dates we have a calendar image and next to our revenue and year field we see the summation type. That is all correct, but note that there is no field type next to Latitude and Longitude.
Lets update Longitude to the field type Longitude. In the tool bar select column tools. Here we need to change the data type from text to decimal number and the data category to longitude. This is one way of doing it. Another way to do this is to change the data type and data category directly in Power Query editor. It is best practice to check all your input data before starting to build any reporting in Power BI. In the image below we see that we have changed both our longitude and latitude fields as the little image next to the text suggests.
We are now ready to start exploring ways of asking questions in Power BI after a thorough check is completed of our entire data model.
Ways to ask Questions in Q&A
The Q&A feature has a rich search feature as it uses natural language. This means that you can guide Q&A to a large extend to the type of answers that you would like. It is important to understand the way that you can ask questions in Q&A. If you just ask a very broad question, you might just get a very obvious result and the value add is not what you would expect from such a powerful and talked up feature. You can also run into a lot of ambiguity if you have little understanding of how Q&A works. Let’s delve deeper into asking questions in Q&A.
Exploring your data set
Q&A allows you to have a quick overview of your datasets or the ability to display your data set in a table. We will start of by counting the number of rows in our Sales table. Simply type ‘count the number of rows in sales‘. We see that we have 66 rows. We can also count the number of empty rows and wee that there is no empty rows. There are so many checks that you can do in this manner. Let’s explore just one more aspect. We would like to see our data in a table. Simply type sales and your sales table will be displayed.
Filtering in Q&A
Let’s start of with a very basic question. We would like to know what the total revenue where in 2019. We simply type total revenue in 2019 and get a simple card showing total revenue for 2019.
Next we would like to know the total revenue between 2018 and 2020. We type this in the Q&A, but notice something important. Three different date fields exists in our table. Our sales year, order date and delivery date. Now if we just type date, Q&A doesn’t know which of these dates we are referring to, it will either default to one of those dates or we have the option to choose which date field to use. Q&A give us a drop down list from which to choose a specific date field. We will choose order date.
Specifying a visualization type
In Power BI’s Q&A we are able to specify a visualization type, such as a bar chart, line chart, pie chart etc. Let’s also display this data in a stacked bar chart. Simply type stacked bar chart at the end of your question and there we go we almost have our desired chart.
We only see the total revenue for the three years, but would like to see the revenue by year. Let’s add to our question ‘by order year’ and brilliant now we have our revenue per year.
Now another question can be what is the breakdown of revenue per product by year. Here we simply add ‘and product’. This is just amazing we have our desired chart by simply typing our question and natural language takes care of the rest. We hope that this gives you a better idea of the capability of natural language. We can really go into a lot of depth from Power BI’s Q&A in order to get to a detailed chart of our data.
Aggregations in Q&A
Q&A is also able to handle aggregations very well. Let’s explore a few simple examples. We would like to display in a line chart showing the average revenue per order in a certain year.
Next we will see what the maximum revenue were per order for a certain year.
We won’t go into more detail, but it is clear that you can really ask any sort of aggregation that you would like in Q&A.
In the supply chain world a product is often referred to as a SKU short for stock keeping unit. Especially in the warehousing world Product A might be referred to as SKU 1 rather than product A. Let’s see what the Q&A feature do when someone from our warehouse wants to see revenue by SKU.
We see that Q&A does not know what a SKU is, but shows it clearly by underlining the word with a double red line. This means that Q&A will show us the total revenue, but not per SKU. Now the great thing about Q&A is that you can teach it to identify these different terms that is used within the business.
Let’s head over to setting and select “Teach Q&A”. We will type the same question,’revenue by SKU in stacked bar chart’ and hit submit. This then shows the call out from Q&A that asks us what SKU refers to. Something to note is that the preview of our result only contains the single bar chart as Power BI’s Q&A cannot identify what SKU means.
Here we simply type in Product. See how Q&A has update the chart as it learns that SKU refers to Revenue. Great, a very useful feature in Q&A for any business. Let’s hit the save button.
Q&A have added this term to it’s library under manage terms. Let’s head over to manage terms just to verify that our term was saved.
The term SKU can now under the field synonyms tab as well. The field synonym tab is where you edit and store all the synonyms the fields in dataset. You can add synonyms and also remove synonyms that you don’t want to be recognized in the Q&A function.
This is also a great place if you have large datasets and want to simplify your Q&A feature to exclude some tables or fields. Again a very useful function in order to simplify Q&A.
In the beginning of the post we saw how Power BI’s Q&A have suggested questions. This is just some default questions that Q&A included. We can also set the questions that Q&A suggests simply start typing your question and once you are satisfied with the result hit the add button. We see all the suggested questions populated at the bottom left of the screen.
You can reorder the questions by simply dragging and dropping the question tabs and you can remove questions as well.
That is it for today’s blog post. A bit of a lengthy one, but as you can see it is very useful to understand some of the logic behind the Power BI’s Q&A functionality. Have fun with this feature and see how it can step change the way your business answer questions.