In this article we will discuss about the most asked 55 Power BI interviews question for 2021 with answers for each. These top 55 power interview questions and answers for fresher as well as experienced, will help to crack machine learning engineer job interviews and also you will have greater knowledge in the Power BI technology.
Before getting into the power bi scenario based interview questions, let’s glance about the Power BI.
Power BI is business analytics service technology developed by Microsoft. The main purpose of the Power BI is a collection of apps, software services and connectors work together to turn your unrelated sources of data into visually immersive, coherent and interactive insights. The data may be data may be a collection of cloud-based or Excel spreadsheet or on-premises hybrid data warehouses.
55 Power BI Interview Questions for 2021 with Answers
1). Define the parts of Microsoft self-service business intelligence solution?
Microsoft has two sections for Self-Service BI
2). What do you mean by self-service business intelligence?
Self-Service Business Intelligence (SSBI)
- SSBI is a way to deal with data analytics that empowers business users to filter, segment, and, break down their data, without the in depth technical knowledge in statistical analysis, business intelligence (BI).
- SSBI has made it simpler for end clients to get to their data and make different visuals to better business insights.
- Anyone who has a basic comprehension of the data can create reports to build informative and shareable dashboards.
3). What is Power BI?
It is a cloud-based data and information sharing environment. Once we have created reports utilizing Power Query, Power Pivot and Power View, we can share our insights/inferences with our friends/colleagues. Power BI, which is a part of SharePoint on the web, lets us load Excel workbooks into the cloud and share them with a chosen group of users. Apart from that, our colleagues can interact with our reports to apply filters and slicers to feature data. They are completed by Power BI, a basic way of sharing our analysis and investigation from the Microsoft cloud.
Power BI features permits us to:
- Share presentations and queries with our friends/colleagues.
- Update our Excel file from data or information sources that can be in the cloud.
- Display the output on multiple devices. This incorporates PCs, tablets, & HTML 5-empowered mobile devices that utilize Power BI application.
- Query our data using natural language processing (NLP).
4). What do you know about Power BI Desktop?
Power BI Desktop is a free desktop application which can be installed on a computer. Power BI Desktop works in cohesion with the Power BI service by providing data exploration, shaping, modelling, and making reports with profoundly intuitive visualizations. We can save our work to a file or publish our data and reports to our Power BI site to share with other users.
5). What data sources can Power BI associate with?
The rundown of data sources for Power BI is broad, but it can be classified into the following:
- Files: Data can be imported from Excel, Power BI Desktop files and CSV files.
- Content Packs: It is a assortment of related files that are stored as a group. In Power BI, there are two types of content packs, one from services providers like Google Analytics, or Salesforce and other from those made and shared by other users in our organization.
- Connectors to databases and different datasets such as Azure SQL, Database and SQL Server Analysis Services tabular data, and so on.
6). What are key components in Power BI?
Following are the key components of Power BI:
- Visualizations: Visualization is a visual portrayal of data. For example: Pie Chart, Line Graph, Side by Side Bar Charts, Graphical Presentation of the source data on top of Geographical Map, Tree Map, and so forth.
- Datasets: Dataset is an assortment of data that Power BI uses to create its visualizations.For example: Excel sheets, Oracle or SQL server tables, and so on.
- Reports: Report is an assortment of visualizations that show up together on at least one or more pages.For example: Sales by Country, State, City Report, Logistic Performance report, Profit by Products report, and so forth.
- Dashboards: Dashboard is single layer introduction of multiple visualizations, i.e. we can congregate one or more visualizations into one page layer.For example: Sales dashboard can have pie charts, topographical maps and bar charts.
- Tiles: Tile is a solitary visualization in a report or on a dashboard.For example: Pie Chart in Dashboard or Report.
7). What are the various types of filters in Power BI Reports?
Power BI provides a multitude of options to filter reports, data and visualization. The following are the rundown of Filter types:
- Visual-level Filters: These filters work on just an individual visualization or perception, diminishing the measure of data that the representation can see. Besides, visual-level filters can filter data as well as calculations.
- Page-level Filters: These filters basically work at the report-page level. Various pages in the similar report can have diverse page-level filters.
- Report-level Filters: These filters deal with the entire report, filtering all pages and visualizations included as part of the report.
Power BI visual, as we know, has interactions feature, which makes filtering a report a quick activity. Visual interactions are helpful, yet they come with certain limitations:
- The filter is not spared as part of the report. Whenever we open a report, we can start to play with visual filters yet it is extremely unlikely to store the filter in the saved report.
- The filter is always ostensible. In some cases, we want a filter for the whole report, but we do not want any visual sign of the filter being applied.
8). What are content packs in Power BI?
Content packs for services are pre-built solutions for popular services as a part of the facility BI experience. A subscriber to a service can very fast connect to their account from Power BI to see data through live dashboards and interactive reports that have been already built for them. Microsoft has content packs for popular services like Salesforce.com, Adobe Analytics, Azure Mobile Engagement, and so on. Organizational content packs make way for users, Business Intelligence professionals, and system integrator, the tools to create their own content packs to share purpose-built dashboards, reports & datasets within their organization.
9). What is DAX?
To do basic calculation and data analysis on data in power pivot, we use Data Analysis DAX). It is a formula language that is used to compute calculated columns and calculated fields.
- DAX works on column values.
- DAX cannot modify or insert data.
- We can create calculated columns and measures with DAX, however we can’t calculate rows using DAX.
10). What are the foremost common DAX Functions used?
Below are a number of the foremost commonly used DAX function:
- SUM, MIN, MAX, AVG, COUNTROWS, DISTINCTCOUNT
- IF, AND, OR, SWITCH
- ISBLANK, ISFILTERED, ISCROSSFILTERED
- VALUES, ALL, FILTER, CALCULATE,
- UNION, INTERSECT, EXCEPT, NATURALINNERJOIN, NATURALLEFTEROUTERJOIN,
- VAR (Variables)
- GEOMEAN, MEDIAN, DATEDIFF
11). How is the FILTER function used?
The FILTER function returns a table with a filter condition applied for every of its source table rows. The FILTER function is never utilized in isolation; it’s generally used as a parameter to other functions like CALCULATE.
- FILTER works like an iterator and can negatively impact performance over large source tables.
- Complex filtering logic can be applied like referencing a measure in a filter expression.
o FILTER (MyTable, [SalesMetric] > 500)
12). What’s unique about the CALCULATE and CALCULATE-TABLE functions?
These functions are the sole functions that allow us to switch the filter context of measures or tables.
- By adding to the existing filter context of queries.
- By overriding filter context from queries.
- By removing existing filter context from queries.
- Filter parameters can only operate one column at a time.
- Filter parameters cannot reference a metric.
13). What’s the common table function for grouping data?
- Main groupby function in SSAS.
- The practice that is recommended is to specify table and group by columns but not metrics. We can use ADDCOLUMNS function.
- New group by function for SSAS and Power BI Desktop which is more efficient.
- Specify groups by columns, table, and expressions.
14). What are the various benefits of using Variables in DAX ?
Below are a number of the benefits:
- While declaring & evaluating a variable, the variable is often reused multiple times during a DAX expression, thus avoiding additional queries of the source database.
- Variables can only make DAX expressions more logical to interpret.
- Variables can only be scoped to their measure/query, as they can’t be shared among measures, queries or be defined at the model level.
15). How do we create trailing X month metrics via DAX against a non-standard calendar?
The solution will involve:
- CALCULATE function to regulate (take over) filter context of measures.
- ALL to get rid of existing filters on the date dimension.
- FILTER to spot which rows of the date dimension to use.
Alternatively, CONTAINS could also be used:
16). What are the various Excel BI add-in?
Below are the foremost important BI add-in to Excel:
- Power Query: It helps find , editing and loading external data.
- Power Pivot: It is primarily used for data modelling and data analysis.
- Power View: It’s used to design visual and interactively reports.
- Power Map: It helps us to display insights on 3D Maps.
17). What’s Power Pivot?
Power Pivot is an add-in for Microsoft Excel 2010 that permits you to import many rows of knowledge from multiple data sources into one Excel workbook. It allows us to create relationships between data that is heterogeneous in nature, build calculated columns and measures using formulas, build PivotTables and PivotCharts. We will have to then further analyse the information in order to make timely business decisions without requiring any IT assistance.
18). What’s Power Pivot Data Model?
It is a model that’s made from data types, tables, columns, and table relations. These data tables are primarily constructed for holding data for a business entity.
19). What’s xVelocity in-memory analytics engine utilized in Power Pivot?
The main engine behind power pivot is that the xVelocity in-memory analytics engine. It can handle great deal of knowledge because it stores data in columnar databases, and in memory analytics which ends up in faster processing of knowledge because it loads all data to RAM memory.
20). What are the differences in data modelling between Power BI Desktop and Power Pivot for Excel?
We have a number of the differences enlisted below:
- Power BI Desktop has a process for supporting bi-directional cross filtering relationships, security, calculated tables, and Direct Query options.
- Power Pivot for Excel has one to many relationships, supports only calculated columns, and supports only import mode. Security roles can’t be defined in Power Pivot for Excel.
21). List the components of the Power BI toolkit?
Components of the Power BI toolkit are:
- Power Query: It is a data mash-up and transformation tool.
- Power Pivot: In-memory tabular data modeling tool
- Power View: It is defined as a data visualization tool.
- Power Map: It is defined as a 3D Geo-spatial data visualization tool.
- Power BI Desktop: It is a powerful companion development tool for Power BI.
- Power Q&A: Natural language question and answering engine.
22. How is the FILTER function used in BI?
The FILTER function in BI returns a subset of the table or the expression. The FILTER function is seldom used in isolation; it is mainly used as a parameter to other functions like CALCULATE.
23. Can you tell us how relationships are defined in Power BI Desktop?
The Relationships between the tables are defined in two ways:
- Manually – We define relationships manually using primary and foreign keys
- Automatic – It is an automated feature of Power BI that detects the relationships between tables, and it creates them automatically(Only when Enabled).
24. Define grouping in BI?
Grouping in Power BI is a technique of combining two or more values for further analysis. For example, When we are trying to see products by category report, we find very few records. It will be annoying to see all those underperforming products. In scenarios like this, you can create a group by combining the records and display them as one product.
25. Explain the CALCULATE and CALCULATETABLE functions?
These are the functions that enable us to modify the filter context of tables or measures:
- Add to the existing filter context of queries.
- Override the filter context from queries.
- Remove the existing filter context from queries.
- Filter parameters can operate only on a single column at a time.
- Filter parameters can’t reference the metric.
26. Is it possible to have multiple active relationships between two tables in a Power Pivot data model?
No. We can have multiple inactive relationships, but we can have only one active relationship between the two tables in the Power Pivot data model. The dotted lines define inactive relationships, and the continuous lines define active relationships.
27. Define the term responsive slicers?
With the help of responsive slicers, we can resize them to different shapes and sizes, from horizontal to square, and the values that are present in the slicer rearrange themselves. In Power BI Service and in the Power BI Desktop, we can make horizontal slicers and date or range slicers responsive.
28. Name the common table function for grouping data?
SUMMARIZE() function can be used for grouping the data.
- The recommended practice is to specify the table and group by columns but not the metrics. We can use the ADDCOLUMNS function.
- Main groupby function in SSAS.
- Specifies the group by columns, table, and expressions
- New group by function for SSAS and Power BI Desktop;
- Can we have a table in the model which doesn’t have any relationship with other tables?
Yes. Why you can have disconnected tables is that:
- The table used to define the user with parameter values to be exposed and selected in slicers.
- The table can be used as a placeholder for metrics in the user interface(UI).
30. Explain the term M language?
M is also called Power Query Formula Language. M stands for Data Mashup. M is a functional language.
31. List the benefits of using Variables in DAX?
Benefits of using variables in DAX:
- Improves performance
- Improves readability
- Simplifies debugging
- Reduces complexity
32. Define the CALCULATE column in DAX?
A calculated column is the same as any other column in a table, and we can use it in any part of the report. We also use the calculated column to define a relationship if necessary.
33. What info is required to create a map in Power Map?
The Power BI service and Desktop send Bing the necessary geodata to create the map visualization. This includes the data in the Location, Longitude, and Latitude of the visual’s field well.
34. What is the various Excel BI add-in?
- Power Pivot: It is mainly used for data analysis and modeling.
- Power View: It is mainly used to design visual and interactive reports.
- Power Map: It helps to display the insights on a 3D Map.
- Power Query: It helps to find, edit and load external data.
35. Where data stored in the Power BI?
Data is usually stored in the fact and dimension tables.
- Dimension tables: It is another table in the star schema of the data warehouse. It mainly stores the attributes and dimensions that specify the objects stored in the fact table.
- Fact tables: It is defined as a central table in the star schema of a data warehouse. It mainly stores quantitative information for analysis.
36. List the features of the Power BI dashboard?
The features are:
- It enables us to create different tiles such as web content, images, textbox, and integrate URLs.
- It enables us to set the report layout to mobile view.
- It will enable to drill through the page, bookmarks, and selection pane.
37. Define Power Pivot?
Power Pivot can be defined as an in-memory data modeling component that mainly provides high compressed data storage and extreme fast aggregation and calculation. It is available as part of Excel that is used to create the data model in an Excel workbook. Power Pivot loads data by itself, or it can load the data into Power Query.
38. List the various views available in Power BI Desktop?
We have three different views in Power BI:
- Data View – Here, data shaping is performed using the Query Editor tools.
- Relationship View – Here, the users manage the relationships between datasets
- Report View – Here, the users add visualizations and additional report pages and publish them on the portal.
39. List the steps to go to Data Stories Gallery in Power Bi communities?
- Open PowerBI.com in your browser.
- By hovering on Learn
- Click on the Community
- Scroll down, and you will find the Data Stories Gallery.
- Anyone can submit their Data Story.
40. Define Power Pivot Data Model?
Power Pivot can be defined as an Excel add-in that is used to do powerful data analysis and to create sophisticated data models. The data model you usually see in a workbook in Excel is the similar data model that you see in a Power Pivot window. Any data that you import into Excel is available in the Power Pivot.
41. What are the fundamental concepts of DAX?
Syntax: This is how the formula is written—the elements that comprise it. The Syntax includes functions like SUM. If the syntax is not correct, you will get an error notification.
Functions: They are the formulas that use specific values, also called arguments, in a specific order to do a calculation that is similar to the functions in Excel. The categories of functions are date or time, time intelligence, information, text, parent/child, logical, mathematical, statistical, and others.
Context: We have two types, namely, row context and filter context.
- If a formula has a function that applies filters to identify the single row in a table, we use row context.
- When one or multiple filters are applied in the calculation that determines the result, we use the filter context.
42. Define incremental licensing refresh feature?
The Incremental refresh feature is used for the high-end scalability of data by publishing only on that particular workspace in a Power BI Service where Premium capacity is necessary.
43. Define xVelocity in-memory analytics engine used in Power Pivot?
The in-memory analytics engine that is used in Power Pivot is known as xVelocity, but it is commonly referred to as Vertipaq. Vertipaq can be defined as a powerful engine that analyzes and stores data.
44. Why do we use a custom visual file?
We use a custom visual file if the prepackaged files do not fit the needs of the business. These Custom visual files are created by developers, and we can import them and use them in a similar way that you would do with the pre-packaged files.
45. Steps to analyze Power BI reports data in excel?
- First, open the Power BI Admin portal, then go to tenant settings and select -Analyze.
- Next, check whether the Power BI Administrator has enabled the Excel option or not.
- Then on the upper right corner, click on the settings to download and install the Excel updates.
- Go to the left-hand side navigation pane, then go to workspace, click on the dataset, click on the Eclipses, and select the Analyze in Excel.
- Download the ODC file, save it.
- Provide Power BI user id and password.
46. Define Power Query?
Power Query can be defined as the data connectivity and data preparation technology that allows the end-users to import and reshape data from a wide range of Microsoft products, including Excel, Power BI, Analysis Services, Dataverse.
47. List the most common sources for data in the Get Data menu?
- Power BI datasets
- SQL server
- Analysis services
48. Steps to perform Dynamic filtering in Power BI?
- Once the data is set up, publish a detailed report to the Power BI.
- Publish it to the group workspace.
- Create the filter link
- Create the DAX calculated column.
- Public overview report
49. List the data destinations for Power Queries?
We have two destinations for the output that we get from the power query:
- Load to a table in a worksheet.
- Load to the Excel Data Model.
50. List the commonly used tasks in the Query Editor?
- Connect to data
- Shape and combine data
- Group rows
- Pivot columns
- Create custom columns
- Query formulas
51. Name the programming language used in Power Query?
The programming language used in Power Query is M Language.
52. Define the concept of query folding in Power Query?
Query folding can be defined as the ability of a Power Query query to generate a single query statement in order to retrieve and transform the source data. The Power Query mashup engine achieves query folding whenever possible for the purpose of efficiency.
53. How to Schedule a Power BI Refresh?
- In the web portal, right-click the Power BI report and then select Manage.
- In the left menu, choose the Scheduled refresh tab.
- On the Scheduled refresh page, now select the New scheduled refresh plan.
- On New Scheduled Refresh Plan page, you need to enter a description and a schedule (If you want the data model to be refreshed).
54. Define Tiles?
A tile can be defined as a snapshot of data that is pinned to the dashboard. A tile is created from a report, dataset, dashboard, Excel, the Q&A box, SQL Server Reporting Services (SSRS) reports. Dashboards and dashboard tiles are the feature of the Power BI service, not the Power BI Desktop.
55. List the common Power Query Transforms?
- Changing Data Types
- Filtering Rows
- Choosing/Removing Columns
- Splitting a column into multiple columns
- Adding new Columns, etc.
For those who wanna get into a job in Machine Learning sector, then you must revise these questions and answers properly to crack any level of interview in this year. And We hope your are fulfilled with this top 55 power bi interview question and answers for 2021 and wish you all the best to become a machine learning engineer.