Power BI Interview Questions and Answers

1. Explain your idea of BI.

Ans:

Business Intelligence (BI) uses technologies that are used to analyze data. This analysis of data gives better insights of data to the organization’s managers and executives to improvise their decisions in organizations’ growth.

2. State the components of Power BI.

Ans:

Power BI is comprised of a total of 8 components as follows:

• Power View
• Power Pivot
• Power Query
• Power Map
• Power BI Service
• Data Management Gateway
• Power BI Questions and Answers
• Data Catalog

3. How many parts does Microsoft self-service Business Intelligence Solutions have?

Ans:

Two parts. Namely, Excel BI Toolkit and Power BI. 4. Explain the Parts of Microsoft self-service Business Intelligence Solutions.

• Excel BI Toolkit – It allows us to import data from other data sources to create excellent reports and model data according to the requirements.
• Power BI – This is the online solution tool of the Excel BI toolkit.

4. State the prerequisites for learning Power BI.

Ans:

You should have a web browser and work email address, not ending with .gov, etc. Knowledge of Excel and Power Query will add an advantage.

5. What does the term Power BI Desktop mean?

Ans:

Power BI is a free application that can create great reports by shaping, cleansing, blending, and modeling the data to create interactive visualizations. It also allows you to publish your report to share with other fellow team members.

6. What work are email addresses currently supported?

Ans:

The work email addresses ending with .org and .edu are currently supported.

7. What is Visualization?

Ans:

Visualization is representing the results of the data in pictorial form like graphs, charts, etc.

8. What is the difference between SUM() function and SUMX() function?

Ans:

SUM() only allows you to take the aggregate of the total values of the data and returns a single value, and SUMX() allows you to apply a filter to it.

9. Explain how FILTER DAX is used?

Ans:

It is used to filter column data depending on the functions. Eg,

10. Define Report in Power BI.

Ans:

When data is visualized from a single data set, it is called Report. A report can have multiple pages of visualizations.

11. What are the different connectivity modes in Power BI?

Ans:

There are two types of connectivity modes in Power BI:

• Import
• Direct Query

12. What do you mean by Synchronized Slicers?

Ans:

Synchronized Slicers, as the name suggests, synchronizes slicers over multiple reports.

13. What is the CORR function?

Ans:

CORR is called a Correlation Function. It provides the correlation between two distinct variables between -1 to +1.

14. What are the advantages of Power BI?

Ans:

The following are the advantages of Power BI:

• It contains and makes useful dashboards
• You can publish your reports in a secure way in Power BI
• There is no memory and speed problems so you can access your data quickly
• Power BI gels up with the existing application

15. What are the different data destinations for Power Query?

Ans:

There are two different data destinations we get from power query:

• Load to Excel Data Model
• Load to the table in Worksheet

16. What is the Power BI Service?

Ans:

Power BI service is a cloud-based analytics solution. It helps you to create a dashboard, reports, and many more that can be shared with your internal as well as external team.

17. Where can you change and reshape the data in Power BI?

Ans:

Data editing helps you to change and reshape the data in Power BI.

18. State the data sources that can be connected to Power BI.

Ans:

The following sources can be connected to Power BI:

1. 1. HDInsight Interactive Query
2. 2. Azure Data Explorer
3. 3. Azure SQL Data Warehouse
4. 4. AtScale (Beta)
5. 5. Google BigQuery
6. 6. Azure SQL Database
7. 7. Azure HDInsight Spark and more.

19. Explain the difference between Power BI and Tableau.

Ans:

The following are the differences between Power BI and Tableau:

• Power BI focuses only on modeling and reporting, while Tableau is the best tool according to data visualization.
• Power BI is less expensive, while Tableau is very expensive.
• Power BI is associated with Microsoft Azure, while Tableau uses python Machine learning.
• Power BI has a simple and easy to use interface while Tableau has a customized dashboard.
• Power BI becomes slow while handling massive amounts of data, while Tableau easily handles any amount of data.

20. What do you mean by filters?

Ans:

If we want to sort a particular data in Power BI, the sorting is done by applying filters. We can use a filter to any visualization/page/report/particular fields/values.

21. State different levels of filter.

Ans:

The following are the different levels of filters:

• Report-level filter
• Page-level filter
• Visualization-level filter

22. Explain the process of dynamic filtering.

Ans:

The following are the steps of dynamic filtering:

1. 1. Set all the data of a report ready to be published
2. 2. Publish it into group workspace
3. 3. Create a filter link
4. 4. Create a DAX calculated column
5. 5. Public overview report

23. Explain the uniqueness of CALCULATE and CALCULATETABLE functions.

Ans:

Only these two functions can modify or filter the table. These functions can be added to the existing filter context of queries, override the filter context of queries and remove existing filter context from queries.

24. What is xvelocity in memory?

Ans:

It helps in loading large sets of data in Power BI and visualize it immediately without any delays.

25. Can we have multiple custom relationships in Power BI?

Ans:

No, we cannot have multiple custom relationships in Power BI.

26. What is the difference between Values() and Distinct() in DAX?

Ans:

Both are used to do the same function of calculating values; the only difference is DISTINCT () can calculate null values as well.

27. What is embed code?

Ans:

The reports in Power BI can be published on the web, and this will generate a link. This link can be shared with others and is called an embedded code.

28. By what means  can you compare the target and actual value in Power BI?

Ans:

Gauge charts can be used to compare target and actual value in Power BI.

29. Is it possible to create multiple dynamic connections between two tables?

Ans:

No, it is not possible to make more than one dynamic connection between two tables.

30. What is Collect Function?

Ans:

The main job of the collect function is to exclude null values. It has the property that it does not aggregate spatial values, and one cannot transfer the data to some other format.

31. Explain Power Query in Power BI.

Ans:

Power Query is used to perform ETL (Extract, transform and load), cleansing, and sharing the data with the use of intuitive interfaces without coding. It imports the data through various data sources and helps to join and append them

32. State the data destinations for Power Query.

Ans:

There are two data destinations for power query, namely, load to a table in worksheet and load to excel data model.

33. Do you know some common Editor Transforms/Power Query?

Ans:

Some common Power Query/Editor Transforms are changing data types, adding new columns, splitting a column into multiple columns, choosing/removing columns, grouping, filtering rows, etc.

34. Which language is used in Power Query?

Ans:

M-Code is the new programming language used in Power Query. It is easy to use and is similar to other languages, and it is case-sensitive.

35. What does the query editor do?

Ans:

Query editor performs functions such as connecting to information, shaping and joining information, pivot segments, group columns, query equations, and create custom segments.

36. What does the ceiling function do?

Ans:

Ceiling function has the same function as the round up function in excel. For, eg. If the output is 4.7, then the ceiling function gives output 5.

37. What type of context style is allowed in Power BI DAX?

Ans:

Filter and row style context style.

38.Why is Top N not accessible by or for report level filter and page-level filter?

Ans:

Top N is not accessible for page-level filter and report level filter as they both are associated with different visuals.

39. What are the three Edit Interactions options of a visual tile in Power BI Desktop? 

Ans:

The 3 edit interaction options are Filter, Highlight, and None.

Filter: It completely filters a visual/tile based on the filter selection of another visual/tile.

Highlight: It highlights only the related elements on the visual/tile, gray out the non-related items.

40. What are many-to-many relationships and how can they be addressed in Power BI ? 

Ans:

Many to Many relationships involve a bridge or junction table reflecting the combinations of two dimensions (e.g. doctors and patients). Either all possible combinations or those combinations that have occurred.

• Bi-Directional Cross Filtering relationships can be used in PBIX.
• CROSSFILTER function can be used in Power Pivot for Excel.
• DAX can be used per metric to check and optionally modify the filter context.

41. How to apply information security in Power BI reports? 

Ans:

A DAX look can be valuable on a table sifting its lines at question time. Dynamic security includes the utilization of USERNAME works in security job definition. Typically a table is made in the model that relates clients to explicit measurements and a job.

42. Explain about Joins? 

Ans:

We have Six types of Joins in Power BI which we use it in Power Query Editor:

Inner Join, Left Outer Join, Right Outer Join, Full Outer Join, Left Anti and Right Anti.

1. 1. Inner Join: If we want only the matching records from both the tables then will use Inner join/Simple join.
2. 2. Left Outer Join: If we want all the records from left table and only matching records from right table then will use left Outer join/left join.
3. 3. Right Outer Join: If we want to display all the records from the right table and only matching records from the left table then will Right Outer join/Right Join.
4. 4. Full Outer Join: If we want display all the records from both the tables then will use Full Outer Join.
5. 5. Left Anti: Displays only the mismatched records from Left Table
6. 6. Right Anti: Displays only the mismatched records from the Right Table.

43. How do you bring data from SQL Server to Power BI? 

Ans:

Step 1: Click on Get Data in Power BI Desktop

Step 2: Select SQL Server from the list

Step 3: Fill the Details of the Server and Database

Step 4: Click on Import or Direct Query based on the Requirement.

44. What is a substance pack, and for what reason would you utilize one? 

Ans:

A substance pack is an instant, predefined gathering of representations and reports utilizing your picked administration (for instance, Sales power). You’d utilize one when you need to get fully operational rapidly, rather than making a report without any preparation.

45. Why should General Formatting be applied to data in Power BI? 

Ans:

By formatting data, users can help Power BI categorize and identify data, making it much easier to work with.

Enter a custom formula to create a new column, including calculations using values from the other columns To create a new column, click on Add Custom Column In the new column name box, type the name of the column and add the formula to the custom column formula box; for example, [ShipDate] – [OrderDate] Select a column from the available columns list and click on Insert or double-click to add it to the custom column formula text box. Then, click on OK The new column is appended to the table, and the formula is visible in the Formula Bar.

The above code is the formula to create a custom column, which calculates the days from when an order was placed to when it got shipped.

46. What are the steps to go to the Data Stories Gallery in Power Bi communities?

Ans:

Steps to go to Data Stories Gallery:

• First, open PowerBI.com in a favorite browser.
• By hovering on Learn
• Click on Community
• Scroll down a  little, and you will find the Data Stories Gallery.
• Anyone can submit her or his Data Story as well.

47. If you click on a single data-point in one of the multiple visuals in a report, does it make any difference?

Ans:

Yes, it makes a difference. When we click on a single data-point, the data gets selected and copied to the dashboard. The copied data can be pasted then anywhere according to the requirement of the users.

48. How can you compare Target and Actual Value from a Power BI report?

Ans:

You need to use a Gauge chart to compare two different measures.

49. Is on-premise access possible in Power BI? 

Ans:

No, it is not possible to access Power BI services on private or internal service cloud. However, by using Power BI desktop, you can connect with the on-premises data sources.

50. What is the Power BI Publisher for Excel? 

Ans:

You can use Power BI publisher for Excel to pin ranges, pivot tables and charts to Power BI.

• The user can manage the tiles – refresh them, remove them, in Excel.
• Pinned items must be removed from the dashboard in the service (removing in Excel only deletes the connection).
• The Power BI Publisher for Excel can also be used to connect from Excel to datasets that are hosted in the Power BI Service.
• An Excel pivot table is generated with a connection (ODC file) to the data in Azure.

The Publisher installs all necessary drivers on the local machine to establish connectivity .

51. Which field composes can be added to the Field well of a Slicer visual? 

Ans:

Computing segments should be made with a DAX articulation.

52. What context style is allowed by Power BI DAX? 

Ans:

Power BI DAX content style is both Row and Filter.

53. How can you download excel files which have millions of records from a Power BI report? 

Ans:

To download large extracts of data, we can host the excel file to a share point, which will generate a .xlsx link, which can be embedded to “Text” or “Button”. On clicking on the Text link or Button. The excel file gets downloaded directly which is hosted in share point or some other base location.

54. Why might you have a table in the model without any relationships to other tables? 

Ans:

There are mainly 2 reasons why we would have tables without relations in our model:

• A disconnected table might be used to present the user with parameter values to be exposed and selected in slicers (e.g. growth assumption.)
• • DAX metrics could retrieve this selection and use it with other calculations/metrics.
• A disconnected table may also be used as a placeholder for metrics in the user interface.
• • It may not contain any rows of data and its columns could be hidden but all metrics are visible.

55. What is the Embed Code? 

Ans:

Power BI online contains an option for publishing on the web that will generate a link address for the Power BI report. You can share these links to others, which is called embed code.

56. How do you hide and unhide a specific report in Power BI? 

Ans:

We have to go to the selection Pane in the menu bar and hide/unhide the report and the action can be passed to bookmark.

57. How is data security implemented in Power BI ? 

Ans:

Power BI can apply Row Level Security roles to models.

• A DAX expression is applied on a table filtering its rows at query time.
• Dynamic security involves the use of USERNAME functions in security role definitions.
• Typically a table is created in the model that relates users to specific dimensions and a role.

58. Name the datasets that can be used with streaming data tiles to create dashboards. 

Ans:

Hybrid datasets and Streaming datasets can be used with streaming data tiles to create dashboards.

59. What is the query collapsing? 

Ans:

The process of converting the steps in the power query editor to SQL and executing it by the source database is called query collapsing.

60. How can we create a dashboard? 

Ans:

First upload a dataset and then upload the same, create multiple reports and infuse everything together as an appealing dashboard.

61. What kind of data can you store in Power BI? 

Ans:

In Power BI, you can store mainly two types of data.

1. Fact Tables:

The central table in a star schema of a data warehouse is a fact table that stores quantitative information for analysis, which is not normalized in most cases.

2. Dimension Tables:

It is a table in the star schema which helps you to store attributes and dimensions which describe objects that are stored in a fact table.

62. What are Tiles in Power BI? 

Ans:

Tile is an important feature of power BI services and can be said as a picture of the data which can be pinned to the dashboard.

63. How to handle Many to Many relationships in Power BI? 

Ans:

You can use the Cross Filtering option in Power BI to address the Many to Many relationships.

64. What are the methods to hide and unhide a specific report in Power BI? 

Ans:

To hide and unhide specific reports, you have to go to the selection Pane in the menu bar, and press hides/unhide toggle button to bookmark.

65. How to represent different levels of hierarchy of data in one single visualization? 

Ans:

Step 1: Create a hierarchy by grouping the required data from the data tab and pull that into the Axis Tab and Enable drill down option by clicking on the down arrow on the chart. 

Step 2: Pull all the hierarchical columns separately into the axis pane in the order of their hierarchy and enable the drill down action by clicking on the down arrow on the chart. Note: Both Step 1 and Step 2 will serve the same purpose.

66. Name some commonly used tasks in the Query Editor? 

Ans:

• Connect to data
• Shape and combine data
• Group rows
• Pivot columns
• Create custom columns
• Query formulas

67. How to perform joins in Power bi? 

Ans:

Joins will be performed in Model Tab in Power BI Desktop.

68. Give examples of data sources which will refresh in real-time. 

Ans:

PowerBI currently supports different types of streaming datasets. You can directly stream data using a push URL to PowerBI.com service. PowerBI also supports streaming data sources from PubNub, which is an Infrastructure as a Service provider. The third option is Azure Stream Analytics which is built on top of Azure Service Bus and Event Hub. You can select the streaming data to go-to PowerBI as your destination. Here is a sample report with streaming data.

69. Does the gateway have to be installed on the same machine as its data source? 

Ans:

No, the gateway connects to its data source by using the provided connection information. In this sense, think of the gateway as a client application. It just needs to connect to the server that has the provided name.

70. What is the difference between Power BI and Power BI Pro? 

Ans:

Power BI: It provides different features for the users to get started with data search in a unique way. Power BI Pro: It holds some extra features added to the Power BI features like higher storage capacity, data refresh scheduling and interactive live data sources.

71. Explain the term Custom Visuals. 

Ans:

Graphs or visuals which are not included in Power BI desktop are imported for better visualization.

72. Define the term ‘M language.” 

Ans:

It is a programming language used in Power Query. It’s a functional, case-sensitive language which is similar to other programming languages and easy to use.

73. What are the different types of refreshing data for our published reports? 

Ans:

There are four main types of refresh in Power BI. Package refresh, model or data refresh, tile refresh and visual container refresh.

1. Package refresh

This synchronizes your Power BI Desktop, or Excel, file between the Power BI service and OneDrive, or SharePoint Online. However, this does not pull data from the original data source. The dataset in Power BI will only be updated with what is in the file within OneDrive, or SharePoint Online.

2. Model/data refresh

It refers to refreshing the dataset, within the Power BI service, with data from the original data source. This is done by either using scheduled refresh, or refresh now. This requires a gateway for on-premises data sources.

3. Tile refresh

Tile refresh updates the cache for tile visuals, on the dashboard, once data changes. This happens about every fifteen minutes. You can also force a tile refresh by selecting the ellipsis (…) in the upper right of a dashboard and selecting Refresh dashboard tiles.

4. Visual container refresh

Refreshing the visual container updates the cached report visuals, within a report, once the data changes.

74. What is Advanced Editor in Power BI? 

Ans:

Advanced Editor can be used to see the query that Power BI runs against data sources to import data.

The query is written in M-code, the Power Query Formula Language To view the query code from Power BI Desktop, choose Edit Queries from the Home tab From either the Home or the View tab, click on Advanced Editor to work on the query. The Advanced Editor window opens, displaying the code for the currently selected query When we make transformations to our data in Query Editor, the steps are saved to the Applied Steps in the Query Settings These steps are also applied to the code in the Advanced Editor

75. How can you perform Dynamic filtering in Power BI? 

Ans:

Dynamic Filtering in Power BI is done using the following steps:

1. 1. Once all the data is set up, publish a detailed report to Power BI.
2. 2. Publish it to the group workspace.
3. 3. Create a filter link Create a DAX calculated column. Public overview report

76. What happens by clicking on a single information point in one of the various visuals in a report? 

Ans:

When you click on single information point data gets selected and copied to the clipboard. Moreover, the copied data can be pasted in any place according to your requirement.

77. What is a Power BI Designer? 

Ans:

It is a stand alone application where we can make Power BI reports and then upload it to Powerbi.com, it does not require Excel. Actually, it is a combination of Power Query, Power Pivot, and Power View.

78. What information is required to create a map in Power Map? 

Ans:

Power Map can display visualizations which are geographical in nature. That’s why some kind of location data is needed, for example, city, state, country or latitude and longitude.

79. Can you join two unique information sources in the same Power BI dashboard? 

Ans:

Yes, we can join two unique information sources in the same Power Bi dashboard. We can also merge comparable reports in your Power Bi account into a single dashboard.

80. What are the different types of refresh in Power BI? 

Ans:

Package Refresh Model/data Refresh Tile Refresh Visual Container Refresh

81. What is Bidirectional Cross-filtering in Power BI? 

Ans:

Bidirectional cross-filtering in Power BI Desktop allows data modelers to determine how they want filters to flow for data using relationships between tables. With bidirectional cross-filtering, the filter context is propagated to a second related table on the other side of a table relationship. This can help data modelers solve the many-to-many problem without writing complicated DAX formulas. Thus, bidirectional cross-filtering simplifies the job for data modelers.

82. How can you analyze Power BI reports data in excel? 

Ans:

You need to follow the below-given steps:

• Open Power BI Admin portal, go to tenant settings and select -> Analyze.
• Check that Power BI Administrator has enabled Excel option or not.
• Then at the upper right corner, click settings to download and install Excel updates.
• Next, go to the left-hand side navigation pane, go to the workspace, click on the dataset, click on Eclipses (three dots) and select Analyze in Excel.
• download ODC file, save, and double click on it.
• After that needs to provide power BI user id and password.

83. How to toggle between two options and make it interact with the rest of the visualizations in report? 

Ans:

Toggling Action can be achieved in Power BI reports, when in case there are two more dimensions. This can be showcased using an “Enlighten Slicer” in Power BI which can be imported from Marketplace.

84. What is Power BI Q&A? 

Ans:

Power BI Q&A is a natural language tool which helps in querying your data and get the results you need from it. You do this by typing into a dialog box on your Dashboard, which the engine instantaneously generates an answer similar to Power View. Q&A interprets your questions and shows you a restated query of what it is looking at from your data. Q&A was developed by Server and Tools, Microsoft Research and the Bing teams to give you a complete feeling of truly exploring your data.

85. Explain the process of dynamic filtering? 

Ans:

The following are the steps of dynamic filtering:

Set all the data of a report ready to be published Publish it into group workspace Create a filter link Create a DAX calculated column Public overview report

86. What is the latency for running queries from the gateway to a data source? What is the best architecture? 

Ans:

We recommend that you have the gateway as close to the data source as possible to avoid network latency. If you install the gateway on the actual data source, the introduced latency is minimized.

87. Name two types of connectivity modes in Power BI? 

Ans:

Two types of connectivity modes used in Power BI are:

• Import
• Direct Query

88. Explain the term responsive slicers. 

Ans:

On a report page, you can easily resize a responsive slicer to various sizes and shapes, and the data contained in it should be rearranged according to it. In case if the visual becomes too small to be useful, an icon representing the visual ability will take its place, thus saving the space on the report page.

89. Is the Power BI tool available for free? 

Ans:

You can access Power BI for free. However, if you wish to use all the features of Power BI, then you can use its pro subscription account. The subscription account offers an enhanced version of several features that are available with Power Bi free account.

90. What is grouping? How can you use it? 

Ans:

Power BI Desktop allows you to group the data into small chunks. For grouping, you should use Ctrl + click to select multiple elements in the visual. Right-click one of those elements which appear in the groups window.

91. What is a data management gateway and Power BI personal gateway? 

Ans:

Gateway acts as a bridge between on-premises data sources and Azure cloud services.

Personal Gateway:

• Import Only, Power BI Service Only, No central monitoring/managing.
• Can only be used by one person (personal); can’t allow others to use this gateway.

On-Premises Gateway:

• Import and Direct Query supported.
• Multiple users of the gateway for developing content.
• Central monitoring and control.

92. What is the only prerequisite for connecting to a database in Azure SQL Database? 

Ans:

The only prerequisite for before connecting to a database is that the user needs to configure firewall settings to allow remote connections.

93.Explain the term incremental refresh? 

Ans:

Increment refresh is a newly added data so that there is no need to truncate or load the entire data.

94. What is the Power Pivot Data Model? 

Ans:

It is a model that contains data writes, tables, sections, and table relations. These data tables help you to develop the holding data for a business substance.

95. What are the differences between a Power BI Dataset, a Report, and a Dashboard?

Ans:

Dataset: The source used to create reports and visuals/tiles.

• A data model (local to PBIX or XLSX) or model in an Analysis Services Server
• Data could be inside a model (imported) or a Direct Query connection to a source.

Report: An individual Power BI Desktop file (PBIX) containing one or more report pages.

• Built for deep, interactive analysis experience for a given dataset (filters, formatting).
• Each Report is connected to at least one dataset
• Each page contains one or more visuals or tiles.

Dashboard: a collection of visuals or tiles from different reports and, optionally, a pinned.

• Built to aggregate primary visuals and metrics from multiple datasets.

96. Why is TOP N not accessible for the Page and Report Level Filter? 

Ans:

ToP N is not accessible for the Page and Report Level Filters because their channels are associated with different visuals.

97. Is Power BI available on-premises? 

Ans:

No, Power BI is not available as a private, internal cloud service. However, with Power BI and Power BI Desktop, you can securely connect to your own on-premises data sources. With the On-premises Data Gateway, you can connect live to your on-premises SQL Server Analysis Services, and other data sources. You can also schedule refresh with a centralized gateway. If a gateway is not available, you can refresh data from on-premises data sources using the Power BI Gateway – Personal.

98. Name 4 types of roles-based usage of Power BI? 

Ans:

Microsoft has categories Power BI users as below:

1. 1. Consumers (also called End-Users) of Power BI who get insights of the data in the form of reports and used for decision making.
2. 2. Designers who Collect, Wangle, Explore, Transform, Model, and Evaluate Data and then design reports, publish reports to Power BI Web Service and create dashboards.
3. 3. Administrators who are responsible for User governance using Office 365 (like Provisioning of Power BI Access to workspaces Licenses) and Administering User Security (like assigning Power BI admin roles and assigning Premium capacity roles).
4. 4. Developers who helps Power BI consumers and designers to build integrating / embedding custom visuals in Power BI Reports and building streaming datasets.