Most Frequently Asked Interview Questions In Power BI

1. Name the different connectivity modes available in Power BI?

There are several connectivity modes available in Power BI, including:

DirectQuery: Allows users to create a live connection to the data source and query it directly, without the need to import the data into Power BI.
Import: Allows users to import a static snapshot of the data into Power BI, which can then be used to create visualizations and reports.
Live Connection: Allows users to create a live connection to an Analysis Services tabular model or Power BI data model, and query it directly.
Composite model: Allows users to combine data from multiple sources and create a single, unified data model for use in Power BI.
Power BI dataflow: Allows users to clean, transform, and shape data from various sources and then make it available for use in Power BI.
Power Query: Allows users to retrieve and transform data from various sources using the Power Query M Language.
Power Automate: Allows users to automate data refresh, manage data sources and schedule data refresh.
Power BI Paginated Report: Allows users to create a report with a fixed layout, similar to a traditional paginated report.

2. What is grouping, and how would you use it?

Grouping in Power BI is a feature that allows you to organize and aggregate data based on one or more columns. This can be useful for creating more meaningful visualizations and reports by grouping similar data together.

For example, if you have a dataset that contains sales data, you might group the data by product category in order to see the total sales for each category. You could also group the data by region in order to see the total sales for each region.

To use grouping in Power BI, you can follow these steps:

Select the column or columns that you want to group by in the Fields pane.
Drag the selected column or columns to the Rows or Columns section of the visualization.
Right-click on the column header, and select “Group”.
In the Group dialog box, select the column or columns that you want to group by, and set any additional options such as the starting and ending values for the group.
Click OK to apply the grouping.

You can also ungroup data by right-clicking on the column header, selecting “Group” and then selecting “Ungroup” or clear the grouping by right-clicking on the column header, selecting “Group” and then selecting “Clear”.

It’s also worth noting that grouping can be applied to any kind of visualizations like table, matrix, chart, and etc.

3. What is the difference between Merged Queries and Append Queries ?

Both Merged Queries and Append Queries are used to combine data from multiple sources in Power BI, but they are used in different ways and have some key differences.

Merged Queries, also known as Join Queries, is used to combine data from multiple tables based on a common column or key. This is useful when you have multiple tables that have a relationship and you want to combine them into a single table. Merged Queries can be used to join data using different types of joins like Inner, Left, Right and Full Outer join.

Append Queries, on the other hand, is used to combine data from multiple tables by appending the rows from one table to the bottom of another table. This is useful when you have tables that have the same structure and you want to combine them into a single table. Append Queries can be used to append data from one query to another and is useful when you have multiple tables with the same structure and you want to combine them together.

In short, Merged Queries are used to combine data based on a relationship and Append Queries are used to combine data based on structure.

4. What are some common Power Query/Editor Transforms?

Power Query, also known as the Power Query Editor, is a tool in Power BI that allows you to retrieve and transform data from various sources. It provides a wide range of transforms (or functions) that can be used to clean, shape, and manipulate data.

Here are some common transforms that are available in Power Query:

Filtering: Allows you to filter rows based on specific conditions.
Sorting: Allows you to sort rows based on one or more columns.
Grouping: Allows you to group rows based on one or more columns and aggregate the data.
Pivoting: Allows you to transform columns into rows and vice versa.
Splitting: Allows you to split a column into multiple columns based on a delimiter.
Merging: Allows you to merge multiple columns into one.
Replacing: Allows you to replace specific values or patterns in a column.
Removing: Allows you to remove specific columns or rows from the data.
Renaming: Allows you to rename columns or tables.
Unpivoting: Allows you to unpivot a table, which is the opposite of pivoting, and it’s useful when you want to convert columns into rows.
Transpose: Allows you to rotate the data and make rows become columns and vice versa.
Conditional Column: Allows you to create a new column based on conditions you set on other columns.
Custom Column: Allows you to create a new column by applying a custom formula to other columns.

These are just a few examples, Power Query has many other functions that can be used to transform data, depending on the requirement.

5. Which language is used in Power Query?

Power Query uses a functional language called “M” or “Power Query Formula Language” to manipulate and transform data. It’s a simple, yet powerful language that is designed specifically for data integration and transformation tasks.

The language is similar to other functional languages like F#, and it’s similar to SQL in terms of its syntax. It has a wide range of functions that can be used to perform operations such as filtering, sorting, and grouping data. Additionally, it has a user-friendly interface that makes it easy to use, even for those without programming experience.

The M language is also used in Power BI dataflow, Power Automate, and Excel. Knowing the M language can be useful when you work with different tools and platforms that use the same language.

Overall, the Power Query formula language (M) is a powerful tool that allows you to easily retrieve and transform data from various sources, making it a valuable asset for data analysis and visualization.

6. What is Query duplicates vs Reference in Power Query ?

In Power Query, a query is a set of steps used to transform and clean data. A “Query Duplicate” refers to a query that is an exact copy of another query in the same workbook. A “Reference” is when one query references another query as an input source. In this case, the reference query is not a duplicate of the original query, but rather it uses the output of the original query as input. This allows for the data to be transformed in multiple stages, with each stage being a separate query.

7. What are the different ROW Transformations available in Power Query ?

There are many different row transformation options available in Power Query, including:

Filtering: Allows you to select specific rows from a table based on certain conditions.
Sorting: Allows you to order the rows of a table by one or more columns.
Grouping: Allows you to group rows of a table together based on one or more columns.
Pivoting: Allows you to rotate rows into columns and columns into rows.
Unpivoting: Allows you to reverse the transformation of a pivot and move columns into rows
Merging: Allows you to combine two or more tables into one by matching values in specified columns.
Splitting: Allows you to divide a column into multiple columns based on a delimiter or a fixed width.
Extracting: Allows you to extract specific parts of a column’s values using patterns or delimiters.
Replacing: Allows you to replace specific parts of a column’s values using patterns or delimiters.
Removing Columns: Allows you to remove one or more columns from the table
Adding Column: Allows you to add one or more columns to the table
Aggregating: Allows you to perform aggregation operations such as sum, count, min, max, average etc on columns
Transpose: Allows you to swap rows and columns of the table
Custom: Allows you to perform custom and complex transformations using M-language.

These are just a few examples, Power Query offers more options and combination of these options to clean, transform and shape data.

8. What are the different COLUMN Transformations available in Power Query ?

There are many different column transformation options available in Power Query, including:

Renaming: Allows you to change the name of a column.
Reordering: Allows you to change the order of the columns within a table.
Splitting: Allows you to divide a column into multiple columns based on a delimiter or a fixed width.
Extracting: Allows you to extract specific parts of a column’s values using patterns or delimiters.
Replacing: Allows you to replace specific parts of a column’s values using patterns or delimiters.
Removing Columns: Allows you to remove one or more columns from the table
Adding Column: Allows you to add one or more columns to the table
Aggregating: Allows you to perform aggregation operations such as sum, count, min, max, average etc on columns
Transpose: Allows you to swap rows and columns of the table
Custom: Allows you to perform custom and complex transformations using M-language.
Changing Data Type: Allows you to change the data type of a column.
Replacing Errors: Allows you to replace the errors in a column with a custom value or null.
Filling Up or Down: Allows you to fill up or down the missing values in a column
Removing Duplicates: Allows you to remove the duplicate values in a column
Grouping: Allows you to group rows of a table together based on one or more columns.

Again, these are just a few examples, Power Query offers many more options and combination of these options for column transformations.

9. What is Data Source Settings in Power BI ?

In Power BI, the Data Source Settings refers to the configuration options that are used to connect to, query and refresh data from various data sources. It allows you to specify the connection details and credentials for different types of data sources like files, databases, and web services.

When you import data into Power BI, you can use the Data Source Settings to specify how the data should be imported, and how the connection to the data source should be established. This includes options such as the file path, server name, database name, and authentication method.

You can also use the Data Source Settings to manage and refresh the data in your report. This includes options such as scheduling data refresh, setting up refresh intervals, and configuring row-level security.

Additionally, you can use the Data Source Settings to specify the data source credentials, and manage the data privacy levels of your data sources. This is particularly useful when working with sensitive data, and you need to ensure that the data is protected and secure.

Overall, the Data Source Settings in Power BI provides a centralized location to manage the data sources and ensure that the data is up to date and accurate in your reports and dashboards.

10. How can we define Hierarchies in Power BI Desktop ?

In Power BI Desktop, a hierarchy is a way to organize data fields into multiple levels of grouping, which can be used to navigate and filter data in a more meaningful way.

To define a hierarchy, you can follow these steps:

Select the Fields pane in Power BI Desktop, and find the fields that you want to include in your hierarchy.
Drag and drop the fields on top of each other to create a hierarchical structure. The order in which the fields are arranged will determine the level of the hierarchy.
Right-click on the field you want to be the top-level of the hierarchy and select “New hierarchy”.
Give the hierarchy a name, and it will be created.
You can also Edit the hierarchy, by right-clicking on the hierarchy, and select “Edit hierarchy” you can change the order of the fields in the hierarchy, add or remove fields, and change the name of the hierarchy.
Once the hierarchy is defined, you can use it in visualizations to group and filter data by the levels of the hierarchy.
You can also create multiple hierarchies for different purposes, and use them in different visualizations.

Additionally, you can also create Date hierarchies, which are special hierarchies that are used to group and filter date fields, such as year, quarter, month, and day. Date hierarchies are automatically created when you load a date field in Power BI, but you can also create them manually.

Hierarchies are a powerful feature of Power BI that can help you organize and present data in a more meaningful and intuitive way.

11. How to handle Many to Many relationships in BI?

Handling many-to-many relationships in BI can be a bit more complex than handling one-to-many or many-to-one relationships. A many-to-many relationship occurs when multiple records from one table can be linked to multiple records in another table, and vice versa.

There are a few ways to handle many-to-many relationships in BI:

One approach is to create a bridge table, also known as a junction table, that connects the two tables. This table contains the unique IDs of the records from the two tables and can be used to link the records together.
Another approach is to use Power BI’s built-in relationship handling features. You can establish a many-to-many relationship between tables by creating a relationship between the primary key of one table and a composite key (a combination of two or more columns) of another table.
A third approach is to use DAX (Data Analysis Expressions) formulas to create calculated columns or tables that aggregate the data from the many-to-many relationship. This can be useful for creating calculated columns or tables that can be used in visualizations or for filtering data.
Another approach is to use a combination of the above methods. For example, creating a bridge table for connecting two tables and using DAX formulas for data aggregation.

It’s important to understand the structure and the nature of your data and the business requirements before making a choice on the approach to handle many-to-many relationships. It’s also important to test and validate the chosen approach with sample data before implementing it in production.

12. What are the different Cardinality types available in BI ?
13 What is cross-filter direction ?
14 Explain how relationships are defined in Power BI Desktop?
15 Can you have more than one active relationship between two tables in a Power Pivot data model?
16 How we use STAR SCHEMA in our project in data model ?
17 How we implement SNOW FLAKE SCHEMA in our project in data model ?
18 What is the difference between CALCULATED COLUMNS & MEASURES ?
19 What is the CALCULATE function in DAX?
20 What are the different TEXT Functions available in DAX in Power BI?
21 What are the different Logical Functions available in DAX in Power BI?
22 What is the difference b/w IF Function and Nested IF Function in Power BI?
23 Difference between SUM and SUMX Function in DAX?
24 Difference between COUNT & COUNTX Function in DAX?
25 What is SUBSTITUE Function in DAX?
26 What is BLANK Function in DAX?
27 What is FILTER Function in DAX?
28 What is the difference b/w ALL and ALLSELECTED Function in DAX?
29 What is TOTALYTD Function available in DAX?
30 What is SAMEPERIODLASTYEAR Function available in DAX?
31 What is ALL, ALLSELECTED and ALLEXCEPT functions in Power BI ?
32 What is CALCULATETABLE function in Power BI ?
33 What is CROSSFILTER function in Power BI ?
34 What is RELATED, RELATEDTABLE and RELATEDFILTER function in Power BI ?
35 What is USERRELATIONSHIP function in Power BI ?
36 What is the difference between CALENDER and CALENDERAUTO function in DAX in Power BI ?
37 What is PARALLELPERIOD function in DAX ?
38 What is special or unique about the CALCULATE and CALCULATETABLE functions?
39 What are the differences between visual-level filters, page-level filters, and report-level filters?
40 What is Bookmark?
41 Why use selection pane in Power BI?
42 State the main difference between Filter and Slicer.
43 What are the three Edit Interactions options of a visual tile in BI Desktop?
44 How does the Schedule Refresh feature work?
45 What is on-premise gateway?
46 Explain the term incremental refresh?
47 What is Scheduled Refresh ?
48 What is ROW LEVEL SECUTIRY ? Types of RLS?
49 What is Static Row Level Security ?
50 What is Dynamic Row Level Security ?
51 What is What If Parameter in Power BI ?