Power BI Interview Questions and Answers

What is power bi?

Power bi is data visualization tool. It convert data from different data sources to interactive dashboard and reports.

What are building blocks in power bi?

The following are the building blocks in power bi:

• Visualization: Visualization is visual or graphically represent the data.
  Eg. Pie chart, Bar chart
• Datasets: Datasets is collection of data.
  Eg. Excel sheet, SQL Tables.
• Reports: Reports is collection of visuals appears on one or more pages.
  Eg. Sales by country, profit by product reports.
• Dashboards: Dashboard is single representation of multiple visuals.
  Eg. Sales Dashboard we have pie chart, bar chart, line graph.
• Tiles: Tiles is single visual in report or dashboard.
  Eg. Pie chart in report.

What are the types of filters in power bi?

• Visual level filters: This type filter works only individual visualization to reduce amount of data.
• Page level filters: This type filter works at report page level. In reports one or more page are available so we apply different filters on different pages.
• Report level filters: This type filter works on entire report. Filtering all pages and all visualization included in report.

What is DAX?

Dax means Data Analysis Expression it is used for creating some basic formulas or expression.

What is Power Query?

Power query is self- service ETL tool and allow user to pull data from different data sources.

What are the common DAX function?

• Sum, Min, Max, Avg.
• If, And, Or
• Union, Median, DateDiff

Power BI Architecture:

SQL Questions & Answers:

Question: Select top 3 salary records from each department.

Query:

; with T as (
select EmployeeName,DepartmentId,Salary,
DENSE_RANK() over (partition by DepartmentId order by Salary desc) as toprank
from Employee )

select * from T
where T.toprank <=3

Output:

Question: How to replace Gender Male to Female and Female to Male in sql server.

Query:

UPDATE Customer SET Gender = 
CASE Gender WHEN 'male' THEN 'female' WHEN 'female' THEN 'male' ELSE Gender END 

Output:

Question: Difference Between Union and Union all

• Union: The union command combines result set of two or more Tables and retrive the distinct value.
• Union All: The union all command combines result set of two or more Tables and retrive the all value.

Question: What is stored Procedure and Advantages of stored Procedure.

Stored procedure is prepared sql code that you can save and code is reused again and again.

Advantages of Stored Procedure:

It is Faster, Pre-compiled, reusable

It reduce network traffic.

It can handle complex operations.

Security is high.

Question: Difference between Sub Query and Correlated Query

Sub Query: In sub query the inner query executes only one time. The inner query executes first and send the output to the outer query and inner query is not depended on outer query.

Correlated Query: In correlated query Outer query executes first. The inner query executes so many times as no of rows results of outer query. The inner query depended on outer query.

Question: Difference between Primary key, Unique Key and Foreign key.

Primary key	Foreign key	Unique key
Primary key uniquely identify record in the table	Foreign key is field in the table that is primary key of another table	Unique key uniquely identify record in the table
Primary key can't accept null values	Foreign key accept multiple null values	Unique key accept only one null value
We can apply only one primary key in a table	We can apply more than one foreign key in a table	We can apply more than one unique key in a table
Primary key creates clustered index by default	Foreign key can not creates index by default	Unique key creates non clustered index by default

Question: How to delete duplicate records from table.

Query:

;WITH TempEmp (Name,duplicateRecCount)
AS
(
SELECT Name,ROW_NUMBER() OVER(PARTITION by Name, Gender ORDER BY Name) 
AS duplicateRecCount
FROM Customer
)
--Now Delete Duplicate Rows
DELETE FROM TempEmp
WHERE duplicateRecCount > 1 

Output:

Question: Joins in SQL Server

SQL joins are used to combine data from two or more tables based on a common field between them.

Types of Join:

• Inner join: Inner join returns only those records that matches in both table.
• Left join: Left join returns all records from left side table and from right side table returns only matched records.
• Right join: Right join returns all records from right side table and from left side table returns only matched records.
• Full outer join: Full outer join is combination of left join and right join. It returns all records from both table either matched or not matched.



• Cross join: Cross join means Cartesian product of both tables.



• Self join: Self join is used to join database table to itself.

Question: Parameters in stored procedure

In parameter: This types of parameters are used to send values to stored procedure.

Out Parameter: This types of parameter are used to get values from stored procedure.

In Out parameter: This types of parameters are used to send values and get values from stored procedure.

Order only MSBI self-study learning video materials which are available with customized package costing 999 INR/15$. Click this link here to see details and order it.