Power BI Interview Questions & Answers — Part 1

Possible scenario-based interview questions and answers about Power BI.

You have practised and studied a lot before any interview, but still, you are thinking about what type of questions you have to face.

For most of the Power BI-related jobs, interviewers are trying to understand the analytical as well as problem-solving skills of the candidate. To understand better these types of skills of a candidate, most of the time scenario-based questions work better.

In this blog, I am going to start by capturing some possible questions and answers in Power BI. I cannot say that these are obvious questions but from an interviewer’s perspective, I will look for some candidates who have this knowledge.

Let’s start with the questions.

Q1: If you have a dataset, then after importing how will you find that the data has some missing values or outliers?

Whenever you will give the answer, follow the steps.

• After importing the data in Power BI, click on Transform Data and Power Query Editor will be available.
• In Power Query Editor, go to the View tab, and select the checkboxes “Column Quality”, “Column Distribution” and “ Column Profile”.
• Column Quality helps to identify missing values / empty values if any error in the column value
• Column Distribution helps to find the value distribution at a glance for each column.
• Column Profile helps to find detailed statistics about the data including outliers.

Using these features, you can find the statistics about the data set.

Q2. We have two tables Customer and Sales. The customer table has cust_id and cust_name. The sales table has cust_id, order_id, and sales_amount. How will you create a single table?

I will use the “Merge” feature of Power Query Editor to create a single table. Here are some steps that need to be followed.

a) After importing two tables in Power BI, I will go to the Power query editor using the Transform Data.

b) In the Power Query editor, Select Home tab — go to Combine and click on “Merge Queries as New”

c) The Merge dialog will open and select the joining key between two tables. Here it is cust_id.

d) Based on the requirement, I will select the “join kind”. For example, if I select “Left Outer” and the first table is customer, then rows from the first table and matching rows from the sales table will be captured in the new merge table.

Q3. We have huge Student information, but it is distributed in 3 tables. But the good thing is information is in the same pattern, which means the same number of columns. Then how will you create one single student table?

I will use the “Append” feature of Power Query Editor to create a single table. Here are some steps that need to be followed.

• After importing two tables in Power BI, I will go to the Power query editor.
• In the Power Query editor, Select Home tab — go to Combine and click on “Append Queries as New”
• Append dialog will open, click on “Three or more tables”.
• Select all tables and ok to proceed.

Q4. Do you know the purpose of dimension and fact tables?

In a star-schema data model, there are mainly two types of tables, one is dimension and the other is fact.

A dimension table holds the entity attribute label information. For example customer, employee, product etc.

The fact table holds the measure or fact information for example Sales, Profit, Discount, Quantity etc.

Q5. Do you know why we are setting cross-filter direction to both when there is a relationship exists between two tables? It could be one-to-many or one-to-one or many-to-many relationships.

In the power bi project, each model relationship consists of a cross-filter direction. Based on the requirement, I need to set up the direction and filters will generate accordingly.

As per Microsoft Documentation,

Single cross filter direction means “single direction”, and Both means “both directions”. A relationship that filters in both directions is commonly described as bi-directional.

For one-to-many relationships, the cross filter direction is always from the “one” side, and optionally from the “many” side (bi-directional).

For one-to-one relationships, the cross filter direction is always from both tables.

Lastly, for many-to-many relationships, cross filter direction can be from either one of the tables, or from both tables. Notice that when the cardinality type includes a “one” side, that filters will always propagate from that side.

When the cross filter direction is set to Both, another property becomes available.

Q6. The customer wants to display the total sales for a particular category on top of the dashboard. Irrespective of the category selection of any other visual, that visual should not be changed. How will you implement this?

In this scenario, I have to create a separate sales measure for that particular category. Using DAX functions I can create that measure.

For example, CALCULATE(SUM(‘Fact — Sales’[Sales]),’Dim — Product’[Category]=”Technology”)

This measure can be used in a card view on top of the dashboard and irrespective of the category selection of any visual or slicer, that card visual will not be changed.

Q7. If the user needs a customised tooltip, then what will be your approach?

To get a customized tooltip, a new report page can be created and set this report page as Tooltip.

To set the page as Tooltip, I will select the “Format page” section and under that expand “Canvas settings”. Now I will select the “Tooltip” under the “Type” section.

This customized tooltip page can have images, visuals etc.

Q8. Have you used Bookmarks? Any example?

Yes, I used the bookmark feature in my project.

Some examples are following

1. Using Selection and Bookmark features, on the same page change the text of a button and implement navigation.

For example, you can check the below link for your reference.

Q9. When we saved a bookmark, what are all the properties saved?

When we save a bookmark the following properties are saved

1. Current page
2. Filters
3. Slicers
4. visual selection
5. drill location
6. sort order
7. visibility of an object
8. spotlight or focus mode

Q10. I have one power bi file which has 4 pages. On the first page, there is one slicer. I want to include this slicer on other pages. Also, if the user selects any value from the slicer on the page, then it should be reflected on other pages too. How will you implement this?

To implement this, there are some options.

1. using “Sync Slicer”
2. using report-level filters means “Filters on all pages”

Q11. What do we mean by data granularity?

Data granularity means, how much detail-level information I have in the data set.

For example, I have sales-related data. In the data set, if I find that it has daily-level sales data. That means it has daily-level data granularity.

Q12. In our dataset, there is one timestamp column. I want to display the date and time separately. What will be the possible approach?

There are two possible approaches. I can create separate date and time columns.

1. Using Power Query Editor, I can create a duplicate of that column. Then change the data type of one column to date and the other one to time.
2. Using DAX DATE and TIME functions, I can create calculated columns to fulfil these requirements.

Now based on the situation or requirements, I will use the suitable solution.

Q13. Do you know what are the key differences between the power query editor and DAX?

There are many differences between power query and DAX.

Some key differences are

1. Power Query is used for data profiling which means, data ingestion, data transformation and cleaning purposes. DAX is used for data analysis purposes which means applying aggregation, filtering, calculation, and relationship building.
2. Power Query is using M language whereas DAX is a formula expression language or functional language.

Q14. Suppose you have created one dashboard in Power BI Service for some users who have viewer access to the particular workspace. Now users want daily updates about that dashboard. What will be the convenient solution?

I can suggest subscribing to the dashboard. Because Power BI will send email notifications to all subscribers as well as subscribers can choose the frequency of the notification, for example daily, weekly, hourly or a particular time after refreshing the data.

Q15. Suppose you have one visual that displays the number of orders over the months. A user wants to display the order amount as a tooltip. What are the possible options you have?

There are options which I can apply in this scenario.

a) add the order amount to the Tooltips section of the visual. This is as simple as that.

b) add the order amount field to the visual filters section. Visual filters are usually applied to a single visual. This can be a very interesting option in this scenario.

Q16. To identify the outliers, which visual you will choose?

I will use a scatter plot to identify the outliers of the dataset.

Q17. After identifying the outliers of a dataset, what could be the possible solutions to deal with that?

After finding the outliers, I will open the power query editor and select the particular column which has outlier information.

Due to outliers, the mean value of that particular field will be affected. So I have to find whether other column values are important or not. If those the useful information, then it is better to replace the outlier value with the median value of that particular column. Otherwise, I can delete the row if other columns don’t have useful information.

Q18. While importing the data in Power BI, what could be the most possible solution to improve the performance?

To improve performance, it is always better to use a database environment for calculating the complex formula.

Q19. Your dataset has some impurities, such as extra spaces and special characters. Then what will be the possible option to remove these?

To remove this kind of impurities, I will use the power query editor. In the power query editor, I will select the particular column and right-click, and then there will be the “Transform” option. Under this option, there are many sub-options like Trim, and Clean. I will use these options to remove the impurities of the data.

Q20. Do you know why we are using row-level security in Power BI?

To implement the data level security, Row Level Security (RLS ) are used. That means, one user can access the permissible or restricted rows.

Videos

Conclusion

In this blog, we get some idea about Power BI possible scenario-based real-time questions and answers.

If you have any questions related to this project, please feel free to post your comments.

Please like, comment and subscribe to my YouTube channel. :-) Keep Learning.